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 ofw1
) - 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.