Sign In
Create Account
Follow Us on Twitter
Subscribe on Youtube
I came across this comment in the MySQL Manaul
Quote
More about performance:
Fulltext search in MySQL isn't slow really. It's slower than normal index selects, but that's not noticable.
I'm playing tables that each contains ca 4million records, about 6GB of text that needs to be indexed. The problem lies in the optimization of the queries, that use the MATCH() and AGAINST() functions. So far as I found out MySQL can use in a query only one index. The optimizer looks for the index that will possibly give the smallest amount of rows, then goes trough all of these, and removes those records, that fall out because of the other WHERE statements.
When you enter a fulltext search, the server _has_ to use that index. Any other statments could be applied only to the rows that were returned by the search. If you have lots of records, most of the searches will return lots of results. The rest of your query will be executed like if you were executing it against a table that contains the results, and no indexes - obviously that will be processed sequentially.
Same thing applies when you use the SQL_CALC_FOUND_ROWS, or LIMIT with high offset values: first the server has to load all the results, then the limit could be applied.
If you _have_ to use the fulltext, these are some hints how you could get quite good results:
- Try to avoid any group, where, order and any statements for what it's necessary to get all the results. I know, usually this is impossible.
- If you need to show the results on a web page, try to find other methods to get the total number of results than using SQL_CALC_FOUND_ROWS. Better if you don't tell the user at all how many results there are ;)
- If indexing text that is in other language than english, before doing anything create a stopword file for your language! (That could reduce index size about 30%)
But most important: think a lot, before you decide to use fulltext search!
Fulltext search in MySQL isn't slow really. It's slower than normal index selects, but that's not noticable.
I'm playing tables that each contains ca 4million records, about 6GB of text that needs to be indexed. The problem lies in the optimization of the queries, that use the MATCH() and AGAINST() functions. So far as I found out MySQL can use in a query only one index. The optimizer looks for the index that will possibly give the smallest amount of rows, then goes trough all of these, and removes those records, that fall out because of the other WHERE statements.
When you enter a fulltext search, the server _has_ to use that index. Any other statments could be applied only to the rows that were returned by the search. If you have lots of records, most of the searches will return lots of results. The rest of your query will be executed like if you were executing it against a table that contains the results, and no indexes - obviously that will be processed sequentially.
Same thing applies when you use the SQL_CALC_FOUND_ROWS, or LIMIT with high offset values: first the server has to load all the results, then the limit could be applied.
If you _have_ to use the fulltext, these are some hints how you could get quite good results:
- Try to avoid any group, where, order and any statements for what it's necessary to get all the results. I know, usually this is impossible.
- If you need to show the results on a web page, try to find other methods to get the total number of results than using SQL_CALC_FOUND_ROWS. Better if you don't tell the user at all how many results there are ;)
- If indexing text that is in other language than english, before doing anything create a stopword file for your language! (That could reduce index size about 30%)
But most important: think a lot, before you decide to use fulltext search!
The bit that caught my eye was this ...
Same thing applies when you use the SQL_CALC_FOUND_ROWS, or LIMIT with high offset values: first the server has to load all the results, then the limit could be applied.
So, that means that when I am on page 15, with 10 records per page - the Server has to get 160 records, via a fulltext search and discard 150 of them ?
Yikes @ fulltext search across 5 columns each time they page up or down.
Would I not be better off building an array of the ID's then slicing the array, as you would use LIMIT within MySQL and doing a
SELECT * from table WHERE ID = $ID
ID being it's usual unique indexed key that we all use.
From my array of ID's rather than send the server off to get a load of records, just to discard them each time ?
Now, I've not even got LIMIT to work in this query - but it has been suggested that I should use it ..
My query currently looks like this ...
$query= "SELECT * FROM $table WHERE MATCH $fulltext AGAINST '".mysql_real_escape_string($test) ORDER BY Master_Group,Sub_Group,Description,Dimensions"'";
$table = table to be searched.
$fulltext is the list of columns to be searched using the index made by invoking a FULLTEXT index on the columns I want to check.
$test is what I'm looking for.
I am proposing to do this :
$rs=mysql_query($query) or die(mysql_error());
// populate $result
$count = 0;
while ($row = mysql_fetch_array($rs)) {
$result[$count] = $row['ID'};
$count = $count + 1; // Total Number of rows returned
}
So, this way I would take the 'hit' on the SELECT once - build my Array of the ID numbers, then wander off and slice it in the same way you would slice using LIMIT. to get the individual records.
I haven't even got LIMIT to work with the search query I'm using - Should I battle on, or is my method acceptable ?
Your thoughts on the matter would be gratefully received.
Thanks, Phill.
Back to top
