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:chriswhite@stripped]
Sent: Wednesday, May 03, 2006 3:27 PM
To: mysql@stripped
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/mysql?unsub=1
