…need to use something like Trick “Unionizing Order by” described in presentation. Q:…column MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL…work very similar to other comparison and same tricks may apply, for example you…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… columns. The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see why it is the case. MySQL… changed the table to make columns i1 and i2 highly correlated. In fact they are now the same: mysql [localhost] {msandbox} (test) > update… columns. In this case the combined index is useless and MySQL has an option of doing full table scan or doing the Union…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… view of a UNION DISTINCT of those two tables: mysql> create view…mysql> select * from all_indexes\G … *************************** 33. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: rental INDEX_NAME: rental_date NON_UNIQUE: 0 COLUMN…on disk are still the same old size. If you use…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 15 Comments
…UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION…columns with cardinality of 100, having about 40.000.000 of rows select * from test.abc where i=5 union…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…”key_len” remains the same. This however looks like a bug in the MySQL 5.0.18… to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT…MySQL is able to execute it much faster, delivering us expected performance. You can also use this approach when first key column…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…which contain WHERE clauses with columns that had single-column indexes on them, MySQL could sometimes make use … indexed_colB = Y” might use the index merge union algorithm, which would *simultaneously* (this is important, … trying to do multiple things at the same time ends up taking longer – if we …
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…was assigned 12 cores for this test. The same VM was used as the previous baseline test….: $ cat shards.ini [default] user=mysql db=ontime password= port=5029 column=date_id mapper=hash gearman=127…or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are …
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…I also created couple of other tables with same data just with really low cardinality with …column index is faster for Q3 in case of low cardinality even though MySQL can’t use index well. You’re right MySQL… watchout a MySQL may decide not to do Index merge (either intersection or union) but instead …
Post: Merge Tables Gotcha
May 9, 2007 By 4 Comments
… – if you have same key values in underlying base tables some of the queries may give you wrong results: mysql> create table… key on the given column instead: mysql> create table tx (id int unsigned not null, key(id)) type=merge union(t1,t2); Query… key to the key in MySQL manual Here is what it says: Note that the a column is indexed as a PRIMARY…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…UNION workaround I already wrote about. So what if you have application which can perform search on many different columns…if we stick to simple MySQL using multiple indexes on most selective columns would be good idea… few members from the country, ie Slovenia – same kind of search might need to scan…
