Wednesday, October 24, 2007

Axel Schwenke helped us. Thanks!

Axel Schwenke, a Support Engineer at MySQL AB has replied my question posted at comp.databases.mysql in this way:

1. an index on a MyISAM table does not refer to the PK, but to the
physical address (or row number) of the row.

2. how do you *know* MySQL is reading from the MYD file?

3. use EXPLAIN to see how your query will be executed.
If it shows "using index" then no datafile reads will be done.

This is what I have replied to Axel Schwenke:

1.- What is the PK?

1b.- The query is a count of registers, so it doesn´t matter what kind of pointer is the index using, isn't it?

2.- I know MySQL is reading the MYD file because I reboot the server and then I force a reading of MYD file in order to be cached by the filesystem. Then I measure the query reply speed. Secondly I do the same process (including reboot) but not caching MYD file. The timings are extremely diffrent in each case. In the first case query responds in 0.1 seconds. In the second case query takes up to 5.0 seconds. So I conclude: MySQL is reading MYD file. Take into account that the MYD file is 100Mb in size.

3.- The query should use the full-text index. Look at the EXPLAIN result: (key = title)

mysql> EXPLAIN SELECT COUNT(*) FROM articles WHERE MATCH (title,body) AGAINST ('keyword');
| table | type | possible_keys | key | key_len | ref | rows | Extra |
| articles | fulltext | title | title | 0 | | 1 | where used |
1 row in set (0.02 sec)

Thanks a lot for your help. Any further hint will also make me very thankful.

No comments: