…) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56 from var55 left join var56 on var55.variable…_SWITCH VARIABLE_VALUE: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from slave…, all_indexes.index_name as index_name from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where… / ( 1024 * 1024 ), 2) as index_size_mb from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where…
Comment: Why MySQL could be slow with large tables ?
April 12, 2007 By 141 Comments
…JOIN tblanswersets ASets USING (answersetid) INNER JOIN tblanswersetsanswers_x ASAX USING (answersetid) INNER JOIN tblanswers A USING (answerid) LEFT JOIN (tblevalanswerresults e1 INNER JOIN…, A.answername, A.answervalue HAVING Q.questioncatid = 1 UNION /**The following query is just for the totals,…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
….length+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D’, 0; The above is the simple…. DELETE a FROM TreePaths AS a JOIN TreePaths AS d ON a.descendant = d.descendant LEFT JOIN TreePaths AS x ON x.ancestor….descendant, supertree.length+subtree.length+1 FROM TreePaths AS supertree JOIN TreePaths AS subtree WHERE subtree.ancestor = ‘D’ AND supertree.descendant…
