I was doing some optimizing with devel and noticed that on every page load a faceted search is creating a temp table, even though the faceted search is not being used. The main concern is the time to load the page, the query takes anywhere between 7 to 10+ ms. I checked the issue queue and did not find a similar issue. BTW, views is being used for the output (Faceted Search View).

Here is the query:

CREATE TEMPORARY TABLE temp_faceted_search_results_1 (nid int unsigned NOT NULL, PRIMARY KEY (nid)) Engine=HEAP SELECT n.nid AS nid, 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), MAX(c.last_comment_timestamp)) - 1255842957) * 6.43e-8) + 2 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 1.0581450716893E-5)) AS score FROM node AS n LEFT JOIN node_comment_statistics AS c ON n.nid = c.nid WHERE ((n.status = 1) AND (n.type IN ('product'))) AND n.nid IN (SELECT node.nid AS nid FROM node node LEFT JOIN content_type_product node_data_field_product_image ON node.vid = node_data_field_product_image.vid LEFT JOIN uc_products uc_products ON node.vid = uc_products.vid WHERE (node.type IN ('product')) AND (node.status <> 0) ) GROUP BY n.nid ASC ORDER BY score DESC

Comments

David Lesieur’s picture

David Lesieur commented
Status: Active » Closed (works as designed)

This is not a bug. The temporary table is used as a base for facet queries, which allows each facet query to be much simpler. This avoids repeatedly querying all nodes for each facet.

You'll get this same behavior whether a view is used or not.

You'll certainly want to cache your pages to avoid the same query being performed on each page request.

elvis2’s picture

elvis2 commented

But what should a temp table even be created if a search is not performed?

mpaler’s picture

mpaler commented
Status: Closed (works as designed) » Active

Going bold here and setting this back to active because I think the issue/question still remains. Why is a temporary table created for every page load even if it's not a faceted search page? I other words, on pages where there's nothing related to faceted search (eg our about us page) devel is reporting a faceted search temp table is being created...

Thanks,
mike

orangeweb’s picture

orangeweb commented

subscribing.

How can we fix the temp table recreation on non faceted search pages?

Thanks

Eddie

willhowlett’s picture

willhowlett commented

subscribing

derekahmedzai’s picture

derekahmedzai commented

If you use Context to display the faceted search blocks rather than Blocks admin, you should find that they will only be executed on the relevant pages.

Drupal's default behaviour seems to be to execute all blocks in any live regions, even if they are not actually going to be displayed on the page.

yurtboy’s picture

yurtboy commented

what if we had a if statement as it is earlier in the code
see file faceted_search.inc line 1182 or so. words_scores is not the ideal variable to check for content could use ($this->_filters).
Either way just to not run this unless needed seems simple enough?

    // Perform the search results query and store results in a temporary table.
    //
    // This is MySQL-specific. db_query_temporary() is not used because of the
    // need to specify the primary key. The index provides a huge performance
    // improvement.
    //
    // See http://drupal.org/node/109513 regarding the use of HEAP engine.
    //
    //WHAT IF we add this if statement? Seems to work on our live site?
    if(!empty($words_scores)){
    db_query('CREATE TEMPORARY TABLE '. $this->_results_table .' (nid int unsigned NOT NULL, PRIMARY KEY (nid)) Engine=HEAP '. $query->query(), $query->args(), $this->_results_table);
    $this->_results_count = db_result(db_query('SELECT COUNT(*) FROM '. $this->_results_table));
    $this->_ready = TRUE;
    }