Thank you, Chris But the table is indexed on the field you are referring to and the other one the query, which is evident from this:
> KEY `prdadadx` (`price_data_date`), > KEY `prdatidx` (`price_data_ticker`) And this: > ll TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS.* -rw-rw---- 1 mysql mysql 610809664 May 1 13:32 TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS.MYD -rw-rw---- 1 mysql mysql 223084544 May 1 13:34 TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS.MYI <<<< huge index file -rw-rw---- 1 mysql mysql 8902 May 1 09:00 TICKER_HISTORY_PRICE_DATA_STAGING_NO_KEYS.frm Any other ideas, please? Mikhail Berman -----Original Message----- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 03, 2006 3:27 PM To: mysql@lists.mysql.com Subject: Re: Q2. Is there anything could be done to speed up this query 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
