I'm using MySQL/PHP to create a system to search about 9 million records, spread over a couple tables (the biggest has 5 million records). The script counts the total number of results, then displays the first ten. Most of the time it works great, but when the search is too vague and a lot of rows come back, it can take three or four minutes.
Most of that time is spent "sending data," which I assume means the server is searching through the table for non-sequential records. I've tried using syntax like SELECT count(1) FROM table WHERE MATCH(fields) AGAINST(term) > score, but that still takes forever.
Is there a way to make MySQL do this faster? Can MySQL just count the matches it finds in the key file without having to pull those records from the table? Any suggestions?
Brad Heath
Most of that time is spent "sending data," which I assume means the server is searching through the table for non-sequential records. I've tried using syntax like SELECT count(1) FROM table WHERE MATCH(fields) AGAINST(term) > score, but that still takes forever.
Is there a way to make MySQL do this faster? Can MySQL just count the matches it finds in the key file without having to pull those records from the table? Any suggestions?
Brad Heath