Complex Fulltext Query Issues

[phadeguy]

Hey All,

I am having a very difficult problem with complex queries using fulltext indexes in MySQL 4.0.18. I am creating a super searh function for my web site:

http://www.animemusicvideos.org/search/supersearch.php

The function takes inputs for different criteria, generates a query, executes it, and returns the results.

I was using LIKE to do the search, but I switched to fulltext indexes in mid January. Search performance increased dramatically. Most queries are now measured in hundredths of seconds. I was very pleased.

But starting about 2 weeks ago, something bad started happening. Certain simple inputs would cause queries to run for 2000+ seconds bringing the dual Opteron w/8G RAM server to a grinding hault.

Although most queries are very fast, it appears that under certain conditions, queries will take 10+ seconds to run. I am unable to determine if the server lockup is due a single runaway query that takes a long time or due to several of these queries attempting to run at the same time and causing a deadlock situation for table read locks.

Here is an example query:

SELECT DISTINCT members.user_id, members.username, videos.vid_id, videos.title, videos_stats.hits, videos_stats.star_avg, videos_stats.star_count, videos_stats.star_avg_bay, videos.date_premiered, videos.local, videos.local_hidden,
MATCH (anime.anime) AGAINST ('+Dragonball* >Z*'IN BOOLEAN MODE) + MATCH (artists.artist) AGAINST ('+Linkin* +Park*' IN BOOLEAN MODE) AS compscore
FROM videos, members, videos_stats, video_anime, anime, video_music, songs, artists
LEFT JOIN video_downloads ON video_downloads.vid_id = videos.vid_id
WHERE videos.user_id = members.user_id AND videos.is_deleted = 'FALSE' AND videos.vid_id = videos_stats.video_id AND videos.date_available <= now( ) AND video_anime.vid_id = videos.vid_id AND video_anime.anime_id = anime.anime_id AND
MATCH (anime.anime) AGAINST ('+Dragonball* >Z*' IN BOOLEAN MODE) AND video_music.vid_id = videos.vid_id AND video_music.song_id = songs.song_id AND songs.artist_id = artists.artist_id AND MATCH (artists.artist) AGAINST ('+Linkin* +Park*' IN BOOLEAN MODE) AND (video_downloads.vid_id IS NOT NULL OR (videos.local = 'TRUE' AND videos.local_hidden = 'FALSE'))
GROUP BY videos.vid_id
ORDER BY compscore DESC

Here is the EXPLAIN for the query:

+-----------------+----------+------------------------------------------------------------------+-----------+---------+------------------------------+------+----------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------------+----------+------------------------------------------------------------------+-----------+---------+------------------------------+------+----------------------------------------------+
| anime | fulltext | PRIMARY,anime | anime | 0 | | 1 | Using where; Using temporary; Using filesort |
| artists | fulltext | PRIMARY,artist | artist | 0 | | 1 | Using where |
| songs | ref | PRIMARY,artist_id | artist_id | 8 | artists.artist_id | 3 | |
| video_music | ref | vs_id,vid_id,song_id | song_id | 8 | songs.song_id | 3 | |
| videos | eq_ref | PRIMARY,member_id,local_hidden,is_deleted,local_2,date_available | PRIMARY | 8 | video_music.vid_id | 1 | Using where |
| video_anime | eq_ref | va_id,vid_id,anime_id | va_id | 16 | videos.vid_id,anime.anime_id | 1 | Using index |
| videos_stats | eq_ref | PRIMARY | PRIMARY | 8 | videos.vid_id | 1 | |
| members | eq_ref | PRIMARY | PRIMARY | 4 | videos.user_id | 1 | Using where |
| video_downloads | ref | vid_id | vid_id | 8 | videos.vid_id | 1 | Using where; Using index |
+-----------------+----------+------------------------------------------------------------------+-----------+---------+------------------------------+------+----------------------------------------------+

It appears that the key_len for the fulltext index is 0 and that the optimizer expects only 1 row to be the result for the fulltext portion. This is definitely not the case since there will be many results for the fulltext portion of the query. The “anime” fulltext lookup will produce 144 results and the “artist” fulltext lookup will produce 76 results.

It may be that the optimizer has determined a wrong path for executing the query, resulting in a several million row result/join instead of the quick answer it expects. The resulting unindexed join would take a long time to execute, thus causing delay in the page loading. However, I am not sure that the amount of time to execute alone the would be in the 10-20 second range or actually be the 2000+ second range.

Is the explain correct in its determination of what needs to be done? What can I do to help find/force the optimum join path? Are the multiple wait read locks to blame? If so, how do I fix those? Do you need more information before an answer can be determined?

Thank you in advance for your help and have a great day!!

Kris.