CREATE TEMPORARY TABLE on every page load...

elvis2 - October 29, 2009 - 07:36
Project:Faceted Search
Version:6.x-1.x-dev
Component:Views integration
Category:bug report
Priority:normal
Assigned:Unassigned
Status:by design
Description

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

#1

David Lesieur - November 2, 2009 - 00:07
Status:active» by design

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.

 
 

Drupal is a registered trademark of Dries Buytaert.