Help:Temporary tables

Temporary tables are used to hold temporary computed results during a query execution and while kept in memory those will be deleted after a query execution has finished.

Example

Below shows an example for when temporary tables (marked with t.) are created and used by a query.

{{#ask: 
 [[Category:City]] OR  [[Located in::Germany]] 
 |format=debug
}}

Debug output SQLStore

ASK Query

[[Category:City]] OR [[Located in::Germany]]

SQL Query

SELECT DISTINCT
  t0.smw_id AS id,
  t0.smw_title AS t,
  t0.smw_namespace AS ns,
  t0.smw_iw AS iw,
  t0.smw_subobject AS so,
  t0.smw_sortkey AS sortkey, t0.smw_sort
FROM
  `smw_object_ids` AS t0
INNER JOIN
  `t1` AS t1 ON t0.smw_id=t1.id
WHERE
  t0.smw_iw!=':smw'
  AND t0.smw_iw!=':smw-delete'
  AND t0.smw_iw!=':smw-redi'
ORDER BY
  t0.smw_sort ASC
LIMIT
  55
OFFSET
  0

SQL Explain

ID	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	PRIMARY				43		Using temporary; Using filesort
1	SIMPLE	t0	eq_ref	PRIMARY, smw_id, smw_iw, smw_iw_2	PRIMARY	4	DB9907220070920.t1.id	1		Using where

Auxilliary Tables

• Temporary table t1
    INSERT IGNORE INTO `t1` SELECT DISTINCT t2.s_id FROM `smw_fpt_inst` AS t2 INNER JOIN `t3` AS t3 ON t2.o_id=t3.id
    INSERT IGNORE INTO `t1` SELECT DISTINCT t4.s_id FROM `smw_di_wikipage` AS t4 INNER JOIN `t5` AS t5 ON t4.p_id=t5.id WHERE t4.o_id='779'
• Temporary table t3
    Recursively computed hierarchy for element(s) ('148509').
    SELECT s_id FROM `smw_fpt_subc` WHERE o_id='148509' LIMIT 1
• Temporary table t5
    Recursively computed hierarchy for element(s) ('148244').
    SELECT s_id FROM `smw_fpt_subp` WHERE o_id='148244' LIMIT 1

Query Metrics

Query-Size:3
Query-Depth:1

Errors and Warnings

None

Disable temporary tables

You can disable specific query features that rely on temporary tables (necessary when computing intermediary results) by adding the following to "LocalSettings.php":

$smwgQSubcategoryDepth = 0;
$smwgQSubpropertyDepth = 0;
$smwgQFeatures         = SMW_ANY_QUERY & ~SMW_DISJUNCTION_QUERY;
$smwgQConceptFeatures  = SMW_ANY_QUERY & ~SMW_DISJUNCTION_QUERY & ~SMW_CONCEPT_QUERY;

See also

• $smwgQSubcategoryDepth
• $smwgQSubpropertyDepth
• $smwgQFeatures
• $smwgQConceptFeatures
• 1114: The table 't6' is full (localhost) issue