…a range clause on the previous key part. So if you have INDEX(A,B) and have a where clause … problem – using IN list instead of range or UNION which however require rather serious application changes and … 3681 Extra: Using where 1 row in set (0.01 sec) mysql> select sum(events) from info where d IN(‘…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…’ve benchmarked simple queries using where clause which covers multiple columns: Q1 SELECT sum(length(val)) FROM T WHERE i=2 AND j… of low cardinality even though MySQL can’t use index well. You’re right MySQL can’t and MySQL does not – Full table… also worth to watchout a MySQL may decide not to do Index merge (either intersection or union) but instead do full table…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… not just for new connections as in MySQL. This is very helpful for measurement as… 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_…SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’\G This is the …by the query, by using a LIMIT clause or by filtering based on the option…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what if …to watch for, if you do not have full where clause resolved by index is how many rows you …second table which requires filesort: mysql> explain select test.i from test, test t where test.k=5 and …
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
….ancestor, ‘D’, t.length+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D’, 0; The above is… TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’) AND ancestor NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update in FROM clause.” We can…
Comment: MySQL VIEW as performance troublemaker
November 20, 2007 By 57 Comments
… a UNION select) stored in separate tables, then use a where clause when selecting from the view, is that a case where MySQL would be…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…clause in CREATE TABLE to get different auto_increment values ? I guess not: mysql… INSERT_METHOD=LAST UNION=(`a1`,`a2`) | …mysql> delete from a2 where i>2; Query OK, 9 rows affected (0.00 sec) mysql…
