Re: scanning 2 rows slow index fast 26GB MyISAM

Thu, 23 Dec 2004 12:05:02 -0800 

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]

Reply via email to