List:General Discussion« Previous MessageNext Message »
From:Mikhail Berman Date:May 3 2006 7:16pm
Subject:Q2. Is there anything could be done to speed up this query
View as plain text  
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
 

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