List:General Discussion« Previous MessageNext Message »
From:Chris White Date:May 3 2006 7:26pm
Subject:Re: Q2. Is there anything could be done to speed up this query
View as plain text  
On Wednesday 03 May 2006 12:16 pm, Mikhail Berman wrote:
> I have a table:
>
> CREATE TABLE `TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS` (
>   `price_data_ticker` char(8) NOT NULL default '',
>   `price_data_date` date NOT NULL default '0000-00-00',
>   `price_data_open` float default NULL,
>   `price_data_high` float default NULL,
>   `price_data_low` float default NULL,
>   `price_data_close` float default NULL,
>   `price_data_volume` float default NULL,
>   KEY `prdadadx` (`price_data_date`),
>   KEY `prdatidx` (`price_data_ticker`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

<snip>

> mysql> explain select count(price_data_date), price_data_date from
> TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS group by price_data_ticker,
> price_data_date having count(price_data_date) > 1;

> | id | select_type | table                                     | type |
>
> possible_keys | key  | key_len | ref  | rows     | Extra
>
> +----+-------------+-------------------------------------------+------+-
> --------------+------+---------+------+----------+----------------------
> -----------+
>
> |  1 | SIMPLE      | TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS | ALL  |
>
> NULL          | NULL |    NULL | NULL | 19087802 | Using temporary;
> Using filesort |
> +----+-------------+-------------------------------------------+------+-
> --------------+------+---------+------+----------+----------------------
> -----------+

Well, one problem is that nothing is being indexed.  I think your best bet is 
that if you're using that as a high volume query, to look at indexing other 
fields (possibly price_data_date as it seems to be the main hit for your 
search).  However, this is really all going to depend on how the database is 
interacted with as well.  If this is the only query on this table, or the 
only major query, then I'd say look at indexing price_data_date per what I'm 
seeing in  your query.

> This table is intentionally designed without the primary keys, so we can
> catch and display duplicates.
>
> Regards,
>
>
> Mikhail Berman

-- 
Chris White
PHP Programmer / DB Monkey
Interfuel
Thread
Q2. Is there anything could be done to speed up this queryMikhail Berman3 May
  • Re: Q2. Is there anything could be done to speed up this queryChris White3 May
RE: Q2. Is there anything could be done to speed up this queryMikhail Berman3 May
  • Re: Q2. Is there anything could be done to speed up this queryDan Nelson3 May
RE: Q2. Is there anything could be done to speed up this queryMikhail Berman4 May