It seems that (under my conditions) the most expensive queries are under the load_categories() method. In my case, adding an index to the temporary table has cut times almost in half overall in some cases; for example:
http://enlinea.mty.itesm.mx/faceted_search/select/
normally takes in average about 11.4 seconds to render; with an index it takes about 6.531 seconds in average.

The (ugly) patch I put in is for MySQL, inside faceted_search.inc:

783d781
<     
786,789d783
<     // Add index for speed
<     $ok=db_query("alter ignore table temp_faceted_search_results add unique index (nid);");
<     // drupal_set_message("load_categories(): Added index with result ".$ok);
<

Here is an example dump from my mysql slow query log:

# Time: 080123 10:52:14
# User@Host: XXXX[XXXX] @ XXXXX []
# Query_time: 2  Lock_time: 0  Rows_sent: 6  Rows_examined: 142563
SELECT n.nid AS nid, COUNT(DISTINCT(n.nid)) AS count, term_data.tid AS term_data_tid, term_data.name AS term_data_name FROM node AS n INNER JOIN temp_faceted_search_results AS results ON n.nid = results.nid INNER JOIN taxonomy_facets_term_node AS taxonomy_facets_term_node ON n.nid = taxonomy_facets_term_node.nid INNER JOIN term_data AS term_data ON taxonomy_facets_term_node.tid = term_data.tid INNER JOIN term_hierarchy AS term_hierarchy ON taxonomy_facets_term_node.tid = term_hierarchy.tid WHERE ((term_data.vid = 4) AND (term_hierarchy.parent = 0) AND (n.type NOT IN ('story'))) GROUP BY term_data_tid ASC ORDER BY count DESC, term_data.weight ASC, term_data.name ASC LIMIT 0, 6;

I have about 28,000 different terms in 7 vocabularies. One has 16,300 terms, the other 10,955 and the rest... well... the rest =) All vocabularies are freetagging.

Comments

janusman’s picture

janusman commented
Title: Allow indexes for faster load_categories() » Add table indexes for faster load_categories()
janusman’s picture

janusman commented

Just noticed that creating the temporary tables with ENGINE = MEMORY (or HEAP) furthers speeds things up, no need for the Index nor any change in the module.

This will be included natively in Drupal 6, for now I applied the patch mentioned in http://drupal.org/node/109513 and, yes, it works =) I guess you can either point to that patch as an option until you install Drupal 6...

inforeto’s picture

inforeto commented

subscribing

janusman’s picture

janusman commented

I just downloaded the dev version, and yes, I am still having this problem.

Culprit example queries go like this:

Slow queries en enlinea.mty...

1) Setup query #1: 327.4 ms (no problem)

CREATE TEMPORARY TABLE temp_faceted_search_nids_1 Engine=MEMORY SELECT n.nid AS nid, 1 AS relevance, COUNT(*) AS matches FROM node AS n WHERE ((n.status = 1)) GROUP BY nid ASC HAVING ((COUNT(*) >= 0))

2) Setup query #2: 0.462 ms (no problem)

CREATE TEMPORARY TABLE temp_faceted_search_results_1 Engine=MEMORY SELECT n.nid AS nid, ((5 * (1 * i.relevance)) + (5 * POW(2, (GREATEST(n.created, n.changed, IFNULL(c.last_comment_timestamp, 0)) - 1203079220) * 6.43e-8)) + (5 * (2.0 - 2.0 / (1.0 + IFNULL(c.comment_count, 0) * 1.000000))) + (5 * (2.0 - 2.0 / (1.0 + IFNULL(nc.totalcount, 0) * 0.000850)))) / 20 AS score FROM node AS n INNER JOIN temp_faceted_search_nids_1 AS i ON n.nid = i.nid LEFT JOIN node_comment_statistics AS c ON n.nid = c.nid LEFT JOIN node_counter AS nc ON n.nid = nc.nid INNER JOIN term_node AS term_node_26607 ON n.nid = term_node_26607.nid INNER JOIN term_node AS term_node_26486 ON n.nid = term_node_26486.nid WHERE ((n.type IN ('bibitem','page')) AND (term_node_26607.tid = 26607) AND (term_node_26486.tid = 26486)) ORDER BY score DESC

3) Final query: 44901 ms! Query bad!

SELECT n.nid AS nid, COUNT(DISTINCT(n.nid)) AS count, term_data.tid AS term_data_tid, term_data.name AS term_data_name FROM node AS n INNER JOIN temp_faceted_search_results_1 AS results ON n.nid = results.nid INNER JOIN term_node AS term_node ON n.nid = term_node.nid INNER JOIN term_data AS term_data ON term_node.tid = term_data.tid WHERE ((term_data.vid = 9) AND (n.type IN ('bibitem','page'))) GROUP BY term_data_tid ASC ORDER BY count DESC, term_data.weight ASC, term_data.name ASC LIMIT 0, 6

If I create an index between #2 and #3, however, query #3 goes from 44 seconds to 0.641 seconds:

CREATE INDEX results_nid ON temp_faceted_search_results_1 (nid);

Index creation on HEAP tables are relatively painless, so if MySQL temp tables are created with ENGINE=HEAP (default on Drupal 6) I would move for index creation to also be included in the faceted_search module.

david lesieur’s picture

david lesieur commented
Version: 5.x-0.11 » 6.x-1.x-dev

I'd be very open to a clean patch for fixing this. ;-)

david lesieur’s picture

david lesieur commented

I've tested this in my environment, but strangely not seeing any speed improvements. Might be a matter of MySQL configuration...

This is with the latest -dev version, which has improved queries (the 1st temporary table has been eliminated).

load_categories() remains a serious bottleneck.

Helmut Neubauer’s picture

Helmut Neubauer commented

- subscribe -
I've tested the additional index and the HEAP engine for temporary tables. Former I needed 15-20 seconds for a query, now I need 5 seconds. It's too slow but better. I'm using 16000 nodes with almost 20 vocabularies and 300 terms. I'm very interested to hear news about the improvement of this module :)

inforeto’s picture

inforeto commented

subscribing

alimc29’s picture

alimc29 commented

What file creates these TEMPORARY TABLES? I'd like to add that INDEX to execute right after the TEMPORARY TABLEs are created, but I dont' know what file to add the code for creating the index to.

Thanks

david lesieur’s picture

david lesieur commented
Version: 6.x-1.x-dev » 5.x-1.x-dev
Status: Active » Fixed

Committed. I did more tests and the index indeed makes a huge difference. Creating the table with the HEAP engine also helps a bit, although not as much as the index (in my environment at least).

Thanks everyone!

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.