Why SELECT is so slow

Posted on

Question :

I have the following table

CREATE TABLE words (
  id bigint(20) NOT NULL,
  `text` int(11) NOT NULL,
  w1 int(11) NOT NULL, 
  w2 int(11) NOT NULL, 
  w3 int(11) NOT NULL, 
  w4 int(11) NOT NULL, 
  w5 int(11) NOT NULL,
  w6 int(11) NOT NULL, 
  w7 int(11) NOT NULL, 
  w8 int(11) NOT NULL, 
  w9 int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY w1 (w1),
  KEY w2 (w2),
  KEY w3 (w3),
  KEY w4 (w4)
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

that contains 2,124,377,011 rows.

Why this simple query

SELECT w1 FROM words WHERE w2=3112

takes 50 sec on Intel Xeon E3-1230 v2 at 3.30 Ghz Server with 7200 RPM HDD?

Is this normal or am I missing something?

UPDATE: It returns 60243 rows, the execution plain is

+----+-------------+-------+------+---------------+------+---------+-------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------+
|  1 | SIMPLE      | words | ref  | w2            | w2   | 4       | const | 31132 |       |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------+

Answer :

  • It is slow because the table is big, and it performs lots of IO when reading sparse data. The index on w2 would be used to tell which rows will be read from the base table, which is huge. And since the rows in that table are not in order- physically that are fragmented, there will be probably one disk access per row (This is to read the value of w1)
  • To make this query faster, create a composite index on (w2, w1). This way, the data is only read from the index file, which is much smaller in size, and the required data is adjacent.
  • Note please that if it is not a frequent query, it is an overhead to have this index.

Leave a Reply

Your email address will not be published. Required fields are marked *