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]