… performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my… do not have duplicate rows in result set matching both conditions and so result will be same as query with “i… to tell you this shameful fact: mysql> explain (select * from test.abc where i=5) union all (select * from test.abc where…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… in which conditions it would do the index merge: mysql [localhost] {msandbox} (test)… i1,i2 | 4,4 | NULL | 203803 | Using union(i1,i2); Using where | +—-+————-+———+————-+—————-+——-+———+——+——–+———————————+ 1 row in set…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… used as the underlying storage for the virtual machines. Baseline: The MySQL command line client was used… one join, and many of the filter conditions are placed on attributes in the dimension …the FROM clause, UNION or UNION ALL clauses. If none of those features are used, then parallelism can’…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… and available = ‘divorced’”. I’ve used Russia as target for investigation and here is results: both conditions – 58, divorced – 623, 18th – 3000… may be consider using (City, Age). Whatever you select to use can be used as prefix for other indexes cuz conditions on these fields….mysqlperformanceblog.com/2006/08/10/using-union-to-implement-loose-index-scan-to-mysql/#comment-1695 2. http://dev.mysql.com/doc/refman/5…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… like: SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT N,M Make sure it uses index It is very … standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what if… be developed, but if we stick to simple MySQL using multiple indexes on most selective columns would be …
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 16, 2007 By 22 Comments
… on “greater” and “lower” characters ! Yes I often use this too, in MySQL and in Postgres, it is just one of the… slow. It has various conditions like GPS coordinates, date, item type, etc, and the all-time favourite, union from a huge archive… tens of milliseconds. Not bad I may say. Use this trick if you need ! It works very very well with MySQL too.
Comment: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…peter In your summary, “In case you’re using OR between columns – single column indexes are required for index … be used for such queries.” Are you saying that if index (a) were not created, MySQL could not perform an index merge (union) with (a,b) and (b) during an OR operation? Would this condition require the…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 16, 2007 By 22 Comments
Yes I often use this too, in MySQL and in Postgres, it is just one of the tricks … slow. It has various conditions like GPS coordinates, date, item type, etc, and the all-time favourite, union from a huge archive… and store the results in a temporary table. – since i use ORDER BY + LIMIT, I can then compute the max() or…
