Wednesday, October 24, 2007

Index full-text queries are not fast

We are going to flush the filesystem cache by rebooting the server and ask for a series of index only full-text queries. We suppose they have to be very fast (around 0.01 seconds each one) because it won't be necessary that MySQL reads MYD file. It will read only certain chunks of the MYI file. Now the queries are this kind:

SELECT id FROM articles WHERE MATCH (title,body) AGAINST ('keyword');

It is evident we are not asking for any information stored in the MYD file. Just the id that is supposed to be found in the MYI file.

65 rows in set (1.68 sec)
165 rows in set (3.08 sec)
256 rows in set (3.57 sec)
27 rows in set (0.57 sec)
5 rows in set (0.12 sec)
Empty set (0.00 sec)
28 rows in set (0.54 sec)
236 rows in set (3.36 sec)
30 rows in set (0.57 sec)
75 rows in set (1.32 sec)

Oh, oh! What is happening here? This benchmarks make a nonsense of our theory. I have no explanation. Maybe index is not actually stored in MYI file, but another kind of pointer. Let's do another test. Lets query only the sum of registers. This way id won't be asked, just the number of index references found in the MYI file:

SELECT COUNT(*) FROM articles WHERE MATCH (title,body) AGAINST ('keyword');

And the results are:

1 row in set (3.09 sec)
1 row in set (5.72 sec)
1 row in set (4.04 sec)
1 row in set (0.55 sec)
1 row in set (0.13 sec)
1 row in set (0.00 sec)
1 row in set (0.62 sec)
1 row in set (4.18 sec)
1 row in set (0.58 sec)
1 row in set (1.33 sec)


Bad results again. So the problem is that the real internals in this MySQL queries are different from what we thought. I am lost. Any ideas?

No comments: