Re: How to avoid slow reading MYD file?
1.- What is the PK? Primary Key?
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.
|