Dear List, 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 |
That holds: mysql> select count(*) from TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS; +----------+ | count(*) | +----------+ | 19087802 | +----------+ 1 row in set (0.00 sec) I am looking to see if there is something I can do to speed up this query: 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; My explain returns: 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 | +----+-------------+-------------------------------------------+------+- --------------+------+---------+------+----------+---------------------- -----------+ This table is intentionally designed without the primary keys, so we can catch and display duplicates. Regards, Mikhail Berman