similar tables, similar queries, one query fast, one slow -- why???

theonomo · #1 (**permalink**) 04-08-04, 11:01

I am running two very similar queries on two very similar tables, but one query is running about three times as slow as the other query. The funny thing is, the slow query is on the smaller table. All the indexes are new and up to date. myisamchk has been run to optimize the tables and indexes.

Can someone please explain why one query would be slower than another, nearly identical query.

Here are the specifics:

table one:

Code:

CREATE TABLE one (
  cui1 VARCHAR(8) NOT NULL DEFAULT '',
  cui2 VARCHAR(8) DEFAULT NULL,
  soc VARCHAR(7) NOT NULL DEFAULT '',
  cot CHAR(3) DEFAULT NULL,
  cof INT(5) DEFAULT NULL,
  coa TINYTEXT,
  cui2name VARCHAR(255) DEFAULT NULL,
  KEY one_index (cui1,cui2,cof,cot)
) TYPE=MyISAM;

query one:

Code:

SELECT cui2, cui2name, cof
FROM one
WHERE cui1 = 'C0002170' AND cui2 IS NOT NULL
AND cof > 2 AND cot <> 'LQ' AND cot <> 'LQB'
ORDER BY cui2;

explain select for this query on table one:

Code:

+------+------+--------------+-----------+---------+--------+--------+-------------+
|table  | type  | possible_keys | key           | key_len | ref       | rows    | Extra           |
+------+------+--------------+-----------+---------+--------+--------+-------------+
|one    | range| one_index      | one_index  |   17      | [NULL]  | 1023    | Using where  |
+------+------+--------------+-----------+---------+--------+--------+-------------+

Now here is the info for the second table and the second query.

Code:

CREATE TABLE two (
  cui1 VARCHAR(8) NOT NULL DEFAULT '',
  cui2 VARCHAR(8) NOT NULL DEFAULT'',
  cof INT(8) DEFAULT NULL,
  new_cof INT(8) default NULL,
  cui1name VARCHAR(255) DEFAULT NULL,
  cui2name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (cui1,cui2),
  KEY two_index (cui1,cof)
) TYPE=MyISAM;

Here is the query on table two:

Code:

SELECT cui2, cui2name, cof
FROM two
WHERE cui1 = 'C0002170' AND cof > 2
ORDER BY cof DESC;

And here is the explain select for the query on table two:

Code:

+-------+-------+--------------------+-----------+---------+--------+------+-------------+
| table  | type   | possible_keys          | key          | key_len  | ref       | rows  | Extra          |
+-------+-------+--------------------+-----------+---------+--------+------+-------------+
| two    | range  | PRIMARY,two_index | two_index  | 13        | [NULL]  |  241  | Using where  |
+-------+-------+--------------------+-----------+---------+--------+------+-------------+

Table one has 13.9 million rows and the query returns in 0.13 seconds. Table two has 1.3 million rows and the query takes three times as long (0.39 seconds). I really need to get the query on the second table down to 0.1 seconds. Why in the world is it taking so long if the similar query on the similar (but bigger) table is taking much less time?

Thanks.

theonomo · #2 (**permalink**) 04-08-04, 14:12

I'm surprised that you MySQL gurus weren't able to get this one. Well, I ended up doing the hard thinking work myself, and here is the answer:

The distribution of the rows in the two tables was different. In the larger table the rows were already ordered in a way that was conducive to the query. In the smaller table the rows were scattered here and there.

Once I sorted the tables based on the criteria in my queries I got similar speed out of both queries.

The command I used to sort the tables was:

Code:

myisamchk --sort-index --sort-records=1 /path/to/table/tablename.MYI

In which "1" is the number of the index I wanted to sort the table according to.

This trick resulted in a significant improvement in query performance. Now both queries run in under 0.1 seconds.

Internet Services

Web Development

Digital Marketing

Consumer Tech