lotek wrote:Hi Rich
Are there any news/updates about your performance optimizing stuff? Is it possible to see how you integrated sphinx (or available as extension?). Just curious.
bye
Marcel
Hi Marcel
Well I downloaded and compiled Sphinx v0.9.9
http://sphinxsearch.com/downloads/release/ and I also installed the sphinx client PECL extension
http://pecl.php.net/package/sphinx. Then it was a case of looking at what search options were to be made available to the site user -- as my site was an online book store the search options were
Author name
Title
Multiple ISBN numbers
Publisher
Categories
Then it is a question of defining the SQL queries to obtain the required results and putting them in the sphinx configuration file so it can build the indexes based on the queries. Below is an example:-
source getProductsByPublisher
{
type = mysql
sql_host = xxxxx
sql_user = xxxxx
sql_pass = xxxxx
sql_db = xxxxx
sql_port = 3306 # optional, default is 3306
sql_sock = /tmp/mysql.sock
sql_query = \
SELECT p.product_id, m.name \
FROM product AS p \
INNER JOIN manufacturer AS m ON (m.manufacturer_id = p.manufacturer_id)
sql_query_info = \
SELECT p.product_id, m.name \
FROM product AS p \
INNER JOIN manufacturer AS m ON (m.manufacturer_id = p.manufacturer_id) \
WHERE p.product_id = $id
}
In my case as the product data is loaded by a batch process I had to rebuild the sphinx indexes after each load process using the sphinx tool as follows --> <installdir>/bin/indexer --all. Also the sphinx listener daemon (searchd) needs to be running which passes data between Opencart and the sphinx engine however it must be shutdown when the re-indexing operation is running.
Next the Opencart product model code needs to be altered to use Sphinx rather than querying the MySQL database directly -- below is an example:-
$sphinx = new SphinxClient; // Instantiate PECL client
$sphinx->setServer('hostname',9999); // Hostname and port number that searchd is listening on
$mode = (strpos($keyword,'"') !== false ? SPH_MATCH_PHRASE : SPH_MATCH_ANY); // See if a phrase is being searched for
$sphinx->setMatchMode($mode);
$sphinx->setMaxQueryTime(100);
$sphinx->setLimits(0,2000,0,0);
// Get results from sphinx
$results = $sphinx->query($keyword,'getProductsByTitle');
$results['matches'] will contain an array of product ID values of all the matching products so when the Opencart query is issued the query's 'WHERE' clause should have added 'AND product.product_id IN (999,999,999) where 999 is the imploded list of IDs returned from Sphinx. There is also a command line tool (bin/search) supplied with Sphinx to test search queries.
In my case the products table is around 8.5 million rows now and sphinx performs really well so it is definitely worth using if you have a large product table.
Hope the above is of interest!
Cheers
Rich
I was about to ask - are you Amazon, rich06? : ) I guess you won't be typing all the products in via the admin panel.
Too right! I feel like a yokel chewing a straw, but this has really piqued my curiosity. 12 million products..
