Hi
I now have over 700,000 products from over 100 different merchants. Now the search facility is unusable - far too slow. Some queries take over a minute.
Any ideas?
To test things i put in about 1,000,000 records, and things did slow down a lot, but queries only took about 15 seconds, so i guess some of your problem is sql server speed, the rest being the number of records to search through
Trouble is, you can not complain too much with the standard version of PT as this was provided free of charge as a starting point to show what magic parser can do.
however once the pro version is released as a stand alone product, that is the point that performance issues can be raised
Hi Steve,
Can you drop me an email with the URL so I can take a look... reply to your Magic Parser reg code email will work...
Thanks,
David.
Thanks for the testing.
15 seconds for a few queries is still a concern. And 1 million records in a table is no big deal for mysql, as long as you keep your indexes fresh and used.
About the pro version, David clearly stated that the only support would be via forums and that no official support whatsoever can be expected.
I totally agree with him, as long as it keeps the pricing lower, and the timeline shorter :)
I have the same issue with the search function on www.shopsort.co.uk with only 350,000 records.
It's the nature of the query that is slowing it down I think - LIKE '%text%' means that the server is having to scan the entire index.
I'll do some experiments with a full text index on the product name field and see if that helps - i'll release it into the free version so that you can try it out. I'll get onto this thisafternoon.
Hi,
Thanks for everyone's response. I don't have a url to look at yet as I am still building it, I am testing searches in phpMyAdmin first.
I think that it is partly server resources. Plus, I think that there was a problem with the index. I am in the process of moving to a new host, and after setting up the database on the new server it is much faster. Querying over 700,000 records is now taking under 1 second.
cheers
Steve
Hi Eddie,
I have just had a look at your store.
Thought you might like to know that the product page formatting is a bit out when viewed in firefox V1.0.7. some of the info overlaps and doesn't look so good.
Simon
Hi all
I have had issues to with search speed, while writing this i have 585710 products listed and the search speed is not great, when i had over 1 million products it was very slow. I recently upgraded buy24-7.net to a virtual dedicated server but to be honest it hasn't made a big difference, any suggestions? A big well done to dave, the script is coming together very well! Keep up the good work!
phil
Hi
My new host has a newer version of MySQL than the old, I don't know if this is the reason but the difference is staggering. For one particular search on the old database - over 17 seconds, on the new - under 1 second! Even quicker if you do a full text search on the title field.
Steve
Hi Steve,
Have you created a full text index on `name` rather than `search_name` and then made appropriate changes to the SQL in search.php? (using HAVING)...
That's basically what I'm about to try here; glad to hear it makes an improvement; but I need to check up on what MySQL versions support full text and perhaps have a fallback if necessary.
OK - For Shopsort.co.uk I am running MySQL version 4.1.14-standard
And it is very slow.
Hi Eddie,
If you don't mind; i'd like you to try a couple of tests to help find out what is causing the speed problem on your server.
Firstly, I think from your posts in other threads that you have SSH access to your host now... Could you see if you are allowed to run the "top" command, which will create a screen-full of information. If you can, could you let us know the contents of the first line of output which will look something like this:
18:13:49 up 37 days, 21:56, 2 users, load average: 0.11, 0.05, 0.01
(it's the load average i'm interested in) Secondly, could you copy and paste the PHP below into a script called dbtest.php (or something like that) into the top level directory of your Price Tapestry installation and then copy & paste the output from your site into the thread. This will help get a feel for what's going on:
<?php
require("includes/common.php");
require("includes/header.php");
function timerBegin()
{
global $timeBegin;
list($usec, $sec) = explode(" ", microtime());
$timeBegin = ((float)$usec + (float)$sec);
}
function timerEnd()
{
global $timeBegin;
list($usec, $sec) = explode(" ", microtime());
$timeEnd = ((float)$usec + (float)$sec);
return ($timeEnd - $timeBegin);
}
$sql = "SELECT COUNT(*) AS product_count FROM `".$config_databaseTablePrefix."products`";
database_querySelect($sql,$rows);
$product_count = $rows[0]["product_count"];
$sql = "SELECT COUNT(*) AS merchant_count FROM `".$config_databaseTablePrefix."feeds`";
database_querySelect($sql,$rows);
$merchant_count = $rows[0]["merchant_count"];
$dbVersion = mysql_get_server_info();
print "<p>MySQL Server Version: ".$dbVersion."</p>";
print "<p>Merchants: ".$merchant_count."</p>";
print "<p>Products: ".$product_count."</p>";
print "<p>Testing SELECT without full index scan:<br />";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE search_name = 'FOOBAR'";
timerBegin();
database_querySelect($sql,$rows);
print timerEnd()."</p>";
print "<p>Testing SELECT with full index scan:<br />";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%FOOBAR%'";
timerBegin();
database_querySelect($sql,$rows);
print timerEnd()."</p>";
require("includes/footer.php");
?>
Here's the output from http://www.webpricecheck.co.uk/dbtest.php
MySQL Server Version: 3.23.58
Merchants: 6
Products: 19473
Testing SELECT without full index scan:
0.0013978481292725
Testing SELECT with full index scan:
0.15667390823364for what its worth here is what i get
MySQL Server Version: 3.23.56
Merchants: 68
Products: 144706
Testing SELECT without full index scan:
0.0010738372802734
Testing SELECT with full index scan:
7.9201030731201That's helpful - showing that on our servers at least there is a comparable linear relationship between product count and full index scan time (approx. 5 seconds / 100,000 records); so I would anticipate a result of around 15 seconds for the second query on Eddie's server, assuming that it is running to "par"...
Full text index is supported as of MySQL version 3.23.23, so if it makes things a lot faster (which it certainly should do!) then i'll make that version of MySQL the minimum required for Price Tapestry and incorporate full text indexing into the "free" distribution. Bargain eh? ;) Will post back once it's tested and in and working...
top up one day - load average - 1.01, 0.78, 0.71
MySQL Server Version: 4.1.14-standard
Merchants: 55
Products: 382480
Testing SELECT without full index scan:
0.076632976531982
Testing SELECT with full index scan:
15.711628913879
That's an acceptable load average, Eddie; certainly not high enough to be responsible for the speed problem (which was bang on my estimation anyway! :) FYI, the recommended maximum load average is 9 * No. processors; so assuming you're on a single processor server there's plenty of room for maneuvre.
Anyway, it does look like it's down the index type - a full text index is required so i'll get it checked in tomorrow and you'll be able to give it a go.
I just ran the test on my site after running the full text index,
results were;
MySQL Server Version: 4.1.13-standard
Merchants: 76
Products: 211746
Testing SELECT on FULL TEXT index:
0.00059700012207031
is this good? Have noticed search time is very fast now compared to before
Phil / buy24-7.net
That's excellent, Phil. Well under a second.
I have almost 600000 products and want to improve search time.
Because it tooks 12-15 seconds to get the search results.
I created dbtest.php with the above cod but I get no results.
I get only a blank page.
What I am doing wrong?
Hi Kempo,
The code above is based on an older version of Price Tapestry where the included files needed to be called separately. In the latest version only require("includes/common.php") is required. I've modified the code above to reflect this, so if you use this version it should work.
Regarding speed; if you could perhaps email me your search.php so that I can see what you are currently using as I have often helped users with tweaks regarding particular searches which may have had an impact...
Cheers,
David.
Hi,
do you have an SQL statement for example?
Generaly speaking, use EXPLAIN in phpMyAdmin to benchmark your request. You must look for indexes NOT being used. And maybe, you can create indexes where they miss.
Also, shared hosting companies put a limit on database access and resources.
I.