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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
