Help:Temporary tables

From semantic-mediawiki.org
Jump to: navigation, search

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 by SQLStore

Ask query

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

SQL Query

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

SQL Explain

IDselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEt0ALLPRIMARY43Using temporary; Using filesort
1SIMPLEt6eq_refPRIMARY,smw_id,smw_iwPRIMARY4DB0231020151216.t0.id1Using where

Auxilliary Tables Used

  • Temporary table t0
      INSERT IGNORE INTO `t0` SELECT DISTINCT t1.s_id FROM `smw_fpt_inst` AS t1 INNER JOIN `t2` AS t2 ON t1.o_id=t2.id
      INSERT IGNORE INTO `t0` SELECT DISTINCT t3.s_id FROM `smw_di_wikipage` AS t3 INNER JOIN `t4` AS t4 ON t3.p_id=t4.id WHERE t3.o_id='779'
  • Temporary table t2
      Recursively computed hierarchy for element(s) ('490').
  • Temporary table t4
      Recursively computed hierarchy for element(s) ('86').

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