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

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