… UNION DISTINCT of those two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION… update our droppable_indexes view to use that information: mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes as select…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… RAID array was used as the underlying storage for the virtual machines. Baseline: The MySQL command line client was used to execute the… the following are in used in the query: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are used, then parallelism can’t be added…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… WHERE indexed_colA = X OR indexed_colB = Y” might use the index merge union algorithm, which would *simultaneously* (this is important, as…: Using intersect(user_type,status,parent_id); Using where; Using index; Using filesort At first glance, this might not look too bad. MySQL is using three…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… in the overview section. So say if you were using the vanilla MySQL server, you would see an entry like this in… 2.5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_pp… that you can use to gather more data about the underlying tables involved and the query execution plan used by MySQL. The end…
Comment: Possible optimization for sort_merge and UNION ORDER BY LIMIT
October 5, 2007 By 10 Comments
[...] I was comparing 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 benchmarks, and he was [...]
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… over a union of all the already joined and aggregated data from all the nodes. A single temporary table is used to…. This allows BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed set processing is… of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
When examining MySQL configuration, we quite often want to know how various buffer sizes are used. This matters because some buffers (sort… << 30; # 1GB select * from (select 1 union select 1) as x1 join (select 1 union select 1) as x2 join…. That should… bad happens, you might be seeing this bug: http://bugs.mysql.com/55002
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… be converted to IN instead of using BETWEEN [1]. As all boolean operators are ‘AND’, mysql can use either indexes built on multiple… don’t have such compound index then mysql most probably use index on “available” column only using ref access and after that filter….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 100 Comments
… from performance standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what if you have… solutions can be developed, but if we stick to simple MySQL using multiple indexes on most selective columns would be good idea… it is possible to use index to do ORDER BY with JOIN MySQL still will not be able to use it as Optimizer…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you… does not work for subselects, inline views, parts of the UNION. This is also common missunderstanding. Works on packet level – This… is good idea to use query cache ? Third party application – You can’t change how it works with MySQL to add caching…
