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');

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/

BankHacker <webmaster@bankhacker.com> wrote:

> 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/

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 . Any further hint will also make me very
thankful.

BankHacker wrote:

> 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

BankHacker <webmaster@bankhacker.com> wrote:

> 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 . Any further hint will also make me very
> thankful.

I'm afraid I wasn't too ful. Maybe you find some better answer in
our forums.


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/

But I am not loading the full table in cache, just only the
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.

The MySQL version I am using is 3.23.58. This might be the cause of
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.

I have just install MySQL 5.0.22 and the EXPLAIN commands throws the
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.