Topic: mysql fulltext issue
Hey, maybe someone have a clue about this:
I have performance issues with fulltext search in mysql.
If I use FORCE INDEX (the_fulltext_index) a specific query takes 0.7s to execute (only 6000 rows and no large columns), If I instead use IGNORE INDEX (the_fulltext_index) the query takes still 0.7s to execute. That probably means something is not right? Explain for the query says the fulltext index is used on the first query (force) but not on the other (ignore) ... but the results are the same. I have NO clue of what to do now... 
This is the explain WITH force index on the query:
mysql> explain SELECT a.adid, a.catid, a.areaid, a.subject, a.price, a.mileage, a.year, a.timestamp, a.type, a.images, i.filename AS first_image, i.folder FROM ads AS a FORCE INDEX (cs_idx) LEFT JOIN ads_images AS i ON i.adid = a.adid WHERE MATCH (subject, content) AGAINST ('+helt +ny +ouppackat +kamera +med +garanti' IN BOOLEAN MODE) AND a.visible = '1' AND type = 's' GROUP BY a.adid ORDER BY a.adid DESC LIMIT 0, 50;
+-------+----------+---------------+--------+---------+------+------+----------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+----------+---------------+--------+---------+------+------+----------------------------------------------+
| a | fulltext | cs_idx | cs_idx | 0 | | 1 | Using where; Using temporary; Using filesort |
| i | ALL | adid_idx | NULL | NULL | NULL | 1 | |
+-------+----------+---------------+--------+---------+------+------+----------------------------------------------+
2 rows in set (0.00 sec)And this is how the index looks on the table:
mysql> show index from ads;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| ads | 0 | PRIMARY | 1 | adid | A | 6000 | NULL | NULL | | BTREE | |
| ads | 1 | areaid_idx | 1 | areaid | A | 4 | NULL | NULL | YES | BTREE | |
| ads | 1 | catid_idx | 1 | catid | A | 2 | NULL | NULL | YES | BTREE | |
| ads | 1 | type_idx | 1 | type | A | 0 | NULL | NULL | YES | BTREE | |
| ads | 1 | cs_idx | 1 | content | A | 6000 | NULL | NULL | YES | FULLTEXT | |
| ads | 1 | cs_idx | 2 | subject | A | 6000 | NULL | NULL | YES | FULLTEXT | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)if I dont use "+" in the boolean search and search for only "helt ny ouppackat kamera med garanti" (the table have this frase in every row of the 6000 btw) the query takes about 4 seconds.
Could anyone please suggest anything to do? Im happy if I could even recieve only clues, then I could search further for a solution. Thanks...
/C