Hi all.
I have a question I was wondering if someone might be able to help with:
I have a small table containing a full text index on a title and
description fields. Everything was going great and the full text index
was performing well against 250000 items (So farm, but set to rise). IE:
mysql> explain SELECT ID FROM RESOURCE WHERE MATCH(TITLE) AGAINST
('fred' in Boolean mode);
+----+-------------+----------+----------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+----------+----------+---------------+---------------+---------+------+------+-------------+
| 1 | SIMPLE | RESOURCE | fulltext | res_title_idx | res_title_idx
| 0 | | 1 | Using where |
+----+-------------+----------+----------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)
Great!
My main table joins to a subject headings table using a more traditional
string match. For example, I would like to be able to look for all
resources where title or subject heading is fred. I noticed that when I
add in this secondary OR clause, performance really drops off (Up to 3
seconds). I simplified the query and got things down to the following
example which combines a fulltext match with a lookup on primary key:
(Here's the PK just for completeness)
mysql> explain SELECT ID FROM RESOURCE WHERE ID = 1;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+--------------------------+
| 1 | SIMPLE | RESOURCE | const | PRIMARY | PRIMARY |
8 | const | 1 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+--------------------------+
mysql> explain SELECT ID FROM RESOURCE WHERE MATCH(TITLE) AGAINST
('fred' in Boolean mode) OR ID = 1;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | RESOURCE | ALL | PRIMARY | NULL | NULL |
NULL | 0 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Do I read this correctly in that type=ALL indicates a full table scan? I
had hoped that the query plan would be a merge of the keys located by
the fulltext query and the keys located by the primary key lookup of
ID=1. If I rewrite this query using a UNION, things start to work well
again, but alas my SQL is auto generated, and it's hard to tune things
like that.
Anyone have any feelings about this.. is it behaving correctly and my
expectations or wrong or might it indicate a minor feature?
Kindest Regards and Many thanks for your time!
Ian.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
