Hello, I currently have a table with around 10 gigabytes of information. Sphinx speeds are fine, data retrieval speeds are fine however doing a sphinx query and then selecting the information from mysql using a join is definately my bottleneck:
SELECT urls.title,doctime,urls.size, weight, urls.data, urls.url
FROM urls
INNER JOIN
codecrawler_sphinx cs ON ( urls.id=cs.id )
WHERE urls.title != “” AND search=‘query=example query;mode=all;limit=1000’ ORDER BY weight DESC
Selecting this data from sphinx takes ~0.4 seconds. However for the first execution of this query and the join of the 1000 rows takes the query time to over 12 seconds at times! The table structure for “urls” table is innodb (i have tried myisam and data retrival times is still excessive).
Please dont recommend me to use say 20 rows from Sphinx as i need the 1000 to do weight adding/subtracting to enhance results. The php to change the results is minimul without any accelerator used.
Would a query alike:
SELECT urls.title,doctime,urls.size, weight, urls.data, urls.url
FROM urls WHERE id IN(23232,3232435,434324,…x1000
be quicker? This would also create another loop or two to add the data to the already grabbed sphinx data. However i cant see how a query alike the above would be any quicker than a query with a join from sphinx.
I guess there is some way with spliting tables and getting data cross tables, but im unsure how this is done…
Anyway, any help GREATLY appreciated
Will