CREATE TEMPORARY TABLE on every page load...
| Project: | Faceted Search |
| Version: | 6.x-1.x-dev |
| Component: | Views integration |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | by design |
Jump to:
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
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.