Hi Donny,
Remember that the Date field is also indexed: KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`);
Whether it is a SELECT COUNT(*) or a SELECT locationid, it still must evaluate the Date field, in both cases it should and does (according to explain) use the key: myKey w/o a table-scan required.
The select as you suggest w/o the date key is the same speed only it returns thousands of results, this query would make sense why it is slow to retrieve data. However, my previous query which only returns 2 results should be fast and not slow.
You are right that SELECT COUNT(*) is a different query. My point is the only difference is a SELECT locationid must retrieve the results as locationid is not in the index. It should not however take 5 mins to return 2 rows from the table.
Bryan
----- Original Message ----- From: "Donny Simonton" <[EMAIL PROTECTED]>
To: "'Bryan Heitman'" <[EMAIL PROTECTED]>; "'Sergio Salvi'" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Thursday, December 23, 2004 2:49 PM
Subject: RE: scanning 2 rows slow index fast 26GB MyISAM
Bryan,
Select count(*) is basically a different query then select locationid or any
of your fields. I have tables with way more than a billion rows of
information, I have some in innodb and some in myisam, and neither of them
when heavily loaded will take as long as yours is taking.
I recommend that you try this:
Run and Explain: select locationid from matrix where accountid = 11 and wordid = 72 and position = 'Body';
Who many results do you get?
Then run and explain:
select locationid from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date > now() - interval 10 day;
How many results to you get on this query?
I am betting the problem is that you only have results in the past 10 days
and nothing before that with accounted =11, worded =72 and position = Body.
Which would then do a scan on the date, since it doesn't have anything
before that. Just a theory.
Donny
-----Original Message----- From: Bryan Heitman [mailto:[EMAIL PROTECTED] Sent: Thursday, December 23, 2004 2:02 PM To: Sergio Salvi Cc: mysql@lists.mysql.com Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
Hi Sergio,
All of your suggestions deal with key optimization, I do not believe I have a key issue here. Remember that select count(*), an index-only query returns in .06 seconds which is very quick. The real question, is why does it take 5 mins to retrieve the row data for these 2 rows that the index retrieved so quickly. Why the delay and why the heavy read activity on the MYD file.
That to me does not make a lot of sense on the time it takes, does MyISAM not handle large MYD files w/ a billion rows that well where I should split my data across many tables instead? I have certainly not ran across this issue before, but this is the first time I have a table with a billion rows.
mysql> show index from matrix; +--------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+------+------------+---------+ | matrix | 1 | myKey | 1 | AccountID | A | NULL | NULL | NULL | | BTREE | | | matrix | 1 | myKey | 2 | WordID | A | NULL | NULL | NULL | | BTREE | | | matrix | 1 | myKey | 3 | Position | A | NULL | NULL | NULL | | BTREE | | | matrix | 1 | myKey | 4 | date | A | NULL | NULL | NULL | | BTREE | | | matrix | 1 | myKey2 | 1 | LocationID | A | NULL | NULL | NULL | | BTREE | | +--------+------------+----------+--------------+-------------+----------- +-------------+----------+--------+------+------------+---------+ 5 rows in set (0.00 sec)
----- Original Message ----- From: "Sergio Salvi" <[EMAIL PROTECTED]> To: "Bryan Heitman" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> Sent: Thursday, December 23, 2004 12:01 PM Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
>
> On Thu, 23 Dec 2004, Bryan Heitman wrote:
>
>> My mistake! Here you go:
>
> Ok, no prob :)
>
>>
>> CREATE TABLE `matrix` (
>> `WordID` int(11) unsigned NOT NULL default '0',
>> `LocationID` int(11) unsigned NOT NULL default '0',
>> `Position` enum('Body','From','Subject','To','Mailbox','File') NOT
NULL
>> default 'Body',
>> `times` int(11) unsigned NOT NULL default '0',
>> `MyOrder` int(11) unsigned NOT NULL default '0',
>> `AccountID` int(11) unsigned NOT NULL default '0',
>> `date` timestamp(19) NOT NULL,
>> KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`),
>> KEY `myKey2` (`LocationID`)
>> ) TYPE=MyISAM MAX_ROWS=1000000000 AVG_ROW_LENGTH=300 DATA
>> DIRECTORY='/home/imap/fuse3.disk2/SQL/search/'
>>
>
> Oops, I forgot to ask you to send the output of "show index from
matrix".
> But your index "myKey" looks goods, you could try changing the order of
> the fields in your key. Try creating a index with your fields ordered > by
> the "Cardinality" value from the "show index from matrix" output
> (in asceding order).
>
> Also, what happens if you don't specify the "date" value in your query?
> Check the time it takes and the explain output.
>
> Another thing I would suggest is to create (or replace) your index,
> trying all (or almost all) of the possible combinations regarding the
> order of the keys in your index. It helped me in some situations, and
> sometimes it's better for me to keep two indices with the same keys but
> different order, because of my different selects.
>
> Hope that helps!
>
> []s,
> Sergio
>
>
>
-- 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]
