How to avoid slow reading MYD file?
BankHacker
doing an index query like this:
SELECT COUNT(*) FROM articles WHERE MATCH (title,body) AGAINST
('keyword');
This is a table with just 2 fields and it has been indexed with full-
text option this way:
CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL
PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) );
I was though MYD file was read only when querying for data not stored
in MYI file, but that idea seems to be wrong.
Any explanation or hint? Thanks in advance.
P.S.: You may get further details in http://mysql-full-text.blogspot.com/
Axel Schwenke
> I am trying to find out why MySQL has to read MYD data file when just
> doing an index query like this:
>
> SELECT COUNT(*) FROM articles WHERE MATCH (title,body) AGAINST
> ('keyword');
...
> P.S.: You may get further details in http://mysql-full-text.blogspot.com/
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.
XL
--
Axel Schwenke, Support Engineer, MySQL AB
Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/
BankHacker
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.
Brian Wakem
> 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.
Forcing a filesystem cache of the table is probably loading the index into
memory too. In the second case you are not, so the extra time is probably
spent reading the index from disk, not the data file.
--
Brian Wakem
Axel Schwenke
> 1.- What is the PK? Primary Key?
yep
> 1b.- The query is a count of registers, so it doesn't matter what kind
> of pointer is the index using, isn't it?
For SELECT COUNT(*) not. But for SELECT id it does.
> 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.
Ähem. This is not conclusive at all. EXPLAIN is.
> 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)
This looks a little strange. The index is used, but key_len == 0 and
there is "where used" where I expect "using index". Maybe your index
is broken? Or something charset related?
> Thanks a lot for your help. Any further hint will also make me very
> thankful.
I'm afraid I wasn't too helpful. Maybe you find some better answer in
our forums.
BankHacker
corresponding MYD file as follows:
# cat /var/lib/mysql/full_text_investigations/*.MYD > /dev/null
The increment in cache size obtained through the "free" command
matches with the MYD file size.
Thence, we can be sure that the query needs to read the MYD file, in
order to explain the penalty in the bechmarks when not caching the
file.
Thanks.
BankHacker
the "where used" message instead of the "using index" you expected.
I am going to do these tests under last MySQL version, and then I will
report the results.
Thanks again.
BankHacker
same result:
mysql> EXPLAIN SELECT COUNT(*) FROM articles WHERE MATCH (title,body)
AGAINST ('pantanos');
+----+-------------+----------+----------+---------------+-------
+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+----------+----------+---------------+-------
+---------+------+------+-------------+
| 1 | SIMPLE | articles | fulltext | title | title |
0 | NULL | 1 | Using where |
+----+-------------+----------+----------+---------------+-------
+---------+------+------+-------------+
Thanks.