Hi there,
I am trying to limit the number of results returned as some queries are very slow. They will return sometimes more than 70,000 results. This is obviously an overkill and I have to find the way to limit the number of results and speed up the response time. I tried to use suggestions from this board but it did not work out for me. I would like to be able to limit the number of results to around 500-1000. I do not use price, category or merchant sorting in this installation.
Many Thanks.
Bob
David,
Thanks for the reply. I tried that but I am not seeing any significant difference. In any case I can't use that solution as it returns only 10 results. Is it possible to ask MySQL to match and return only the first 500-1000 records instead of going through all the records and returning 70,000 results? Can I use LIMIT or some other command like WHERE in the script in order to achieve this?
Many Thanks.
Bob
Hi Bob,
As the count SQL does not make any difference then it is purely down to the index performance, and by definition the entire index must be checked in order to return any results - but this should be fast for queries > 3 characters long (are the test queries you are using > 3 characters?). Also, have you looked through the recommendations in the following thread:
http://www.pricetapestry.com/node/732
Hope this helps...
Cheers,
David.
David,
Test queries are for > 3 characters long. I am having around 3 million products which might be cauising this but I also have some pretty powerful hardware: 2GB RAM, 2 X 10k HDD, 512MB Raid conroller card, RAID 0 set up, Opetron 64 dual core 2.4Ghz. So I am surprised to be honest. I thought I would not see issues like these with this kind of hardware. If this is due to hardware at all. I did optimize mysql.cnf. I upped that sort_buffer now a bit higher and I got a faster response a bit but I wonder if that is due to cashing. I have to fix this. I cannot have 5-10 seconds response time for some queries with this hardware. I am planning to get some more RAM but before I spend some more money on hardware I have to know for sure what is causing this.
Many Thanks.
Bob
Hello Bob,
I've just done some searching on MySQL/FullText with very large tables, and from what I can gather "several seconds" is the norm when it comes to so many rows (in the millions). So on a first look, I would say that it is a MySQL limitation that has been reached and not hardware (within reason anyway).
I'll keep on looking into it to see what I can suggest - for example there are alternative full text engines that are reportedly much faster, if this might be an option for you (I have heard good reports about Lucene for example), and another option maybe the Sphinx engine...
http://shodan.ru/projects/sphinx/
Cheers,
David.
David,
Thanks a lot for your support. I am now going to look into those alternatives and possibly adopt one of them.
Many Thanks.
Bob
Hi there,
I have looked into those faster alternatives as you suggested David.
Hi Bob,
That sounds encouraging.
Removing all price min/max from the main (> 3 characters) search SQL should leave you with something like this:
$sql = "SELECT * , MATCH name AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($parts[0])."') GROUP BY name";...which should be OK, - although you will have to play around with the display code that relies on the fields no longer in the result array (minPrice, maxPrice etc.). If that still gives an error, then assuming a database error, add code to print the last MySQL error message which should describe what was wrong with the query...
<?php
print mysql_error();
?>
Cheers,
David.
David,
I am having no problems with this modification. So far. I am now looking into whether I should change the variable-length columns into fixed ones. I could them preload indexes into cache. Is there any particular reason why they are variable in this installation?
Many Thanks.
Bob
Hi Bob,
Fixed width should work ok; they're variable so that you don't have 255 bytes taken up for every value that would normally be more like 6 or maybe 7 characters - but I wanted to give reasonable flexibility for larger values...
Cheers,
David.
David,
Thanks for the reply. Will I have to re-import everything again if I change the format to fixed?
Many Thanks.
Bob
Hi Bob,
You shouldn't have to - MySQL will convert all existing records to the new field sizes. Personally, however, I would prefer to TRUNCATE a large table before changing the structure just to avoid massive fragmentation on disk...
Cheers,
David.
Hi
Just thought you might want to take alook at www.toolsearchit.co.uk I am using the sphinx full text search engine on this site it is still in testing stages and does seem to work well i hace create sphinx index with about 70000 products and index name description merchant etc. have a look test out so comments here would be good. the only thing the coder has forgot to do with the sphinx search is on the old search.php it passed merchant search like this merchant:some merchant but sphinx comes back empty but if you search for just the merchant name it comes back all good i will get the coder to fix this.
any thoughts would be good.
all the best
Darren
I know this is an older thread but I needed a way to limit the number of results too (NOT FOR PERFORMANCE REASONS). Some searches return 10,000+ results in my case and at 10 results per page you end up with 1000 pages of pagination (navigation). Nobody is realistically going to ever look at all 1000 pages BUT search engines will be forced to pick and choose which they index and which they don't.
Since I want page 1 indexed with at most 2 other pages I made the following change in navigation.php
I replaced
$totalPages = ceil($navigation["resultCount"] / $config_resultsPerPage);
with
$totalPages = 30;
And at 10 results per page that leaves 3 pages of navigation per product search max. Perfect for my needs, I've linked items together via related products and other links instead.
If you think of a way to limit the $sql import to just the first 30 results per search I would love to hear how, there's no need to search for 10,000 items when I only need the first 30.
Gem
Hi Gem,
It would be impractical to limit the import procedure to just 30 results per search, so the approach you've used should be fine - and since the search SQL includes a SORT clause the database has to perform the entire query internally anyway, so neither is it actually possible to limit the query even if you only want to limit the results displayed!
Cheers,
David.
Hi Bob,
The actual search results SQL is already limited by the max results per page; however it might be the result count SQL that is being slow. To find out if this is the case, look for the following code in search.php (beginning at line 91 in the distribution):
database_querySelect($sqlResultCount,$rows);$resultCount = $rows[0]["resultcount"];
If you comment the above code out, and replace it with the following:
$resultCount = $config_resultsPerPage;...then this will give 1 page of results every time, and will at least confirm that it is the result count SQL that is causing the slow-down...
Cheers,
David.