MySQL Bugs: #11531: Strange slowness for indexed search

Bug #11531	Strange slowness for indexed search
Submitted:	23 Jun 2005 15:29	Modified:	18 Aug 2006 13:52
Reporter:	Name Withheld	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.1.12	OS:	MacOS (Mac)
Assigned to:		CPU Architecture:	Any

Description:
According to 'EXPLAIN SELECT...', this query is supposed to use an index.  On Windows XP it executes quickly; but on the Mac it executes much more slowly, unless I drop the index altogether.

The query is:

    SELECT id FROM terms WHERE list_id = 1;

The table schema is:

    CREATE TABLE `terms` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `list_id` smallint(5) unsigned NOT NULL default '0',
      `term` text NOT NULL,
      PRIMARY KEY  (`id`),
      KEY `list_id` (`list_id`,`term`(19))
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

The query executes immediately on MySQL 4.1.11 on Windows XP, but takes four seconds to execute using MySQL 4.1.12 on MacOS 10.3.9.  (Both machines have 512Mb RAM, same MySQL settings, etc.)

On both platforms, "EXPLAIN SELECT ..." claims that a two-byte key will be used against the 'list_id' index.  And if I drop that index, the Mac query becomes just as fast as the Windows query.
 
If I add 250,000 rows to this table, the Windows query is still very quick, whereas the Mac query takes over a minute.  Again, if I drop the index, the Mac query becomes just as fast as the Windows query.

How to repeat:
Using MySQL 4.1.12 for Mac 10.2, open the standard 'mysql' client, and run the following query against the attached database ('test1.zip'):

    SELECT id FROM terms WHERE list_id = 1;

Notice how slow it is.  Then run:

    DROP INDEX list_id ON terms;

and execute the first query again.  This time it's much faster.

Now try the same thing on Windows XP.  It's never slow.

most of the 'test4' database

Attachment: test4_rest.zip (application/zip, text), 143.72 KiB.

the 'terms.MYI' file for the 'test4' database

Attachment: test4_index.zip (application/zip, text), 168.53 KiB.

Sorry; my 'test1' database was larger than your 200Kb upload limit.  So I've attached a smaller database, 'test4', with only 12,500 rows of data, spread across two zipfiles.

After further testing, it seems like the Mac slowness may be intermittent. If you can't reproduce it, try calling "FLUSH TABLES", then repeat the test.

Thank you for the report.

I have following result using your test data and last 4.1.22 BK sources:
Mac with index: 0.24 sec
Mac without index: 0.11 sec
Windows with index: 0.14 sec
Windows without index: 0.11 sec

It looks like problem has solved. Could you please check this using your full loaded table and if not reopen the bug?

As you requested, I've tested this with 4.1.21 and my full loaded table.  The problem is gone.