…: 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… ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE user_type=2 AND… output from EXPLAIN “appears to look good” doesn’t mean that it actually is good. Whenever you see index_merge_intersection, it…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…Using intersect(i2,i1); Using where | +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ 1 row in set (0.00 sec) Hm… Optimizer decides to use…to use combined index: mysql [localhost] {msandbox} (test) > explain select …
Comment: Multi Column indexes vs Index Merge
May 10, 2012 By 31 Comments
… when previously it was using a single index. I would greatly appreciate your answer. First explain —————– explain SELECT LinksTbl2.id, LinksTbl2….| prjid_ix,item_desc | 4,1 | NULL | 294 | Using intersect(prjid_ix,item_desc); Using where | | 1 | SIMPLE | PersonTbl2 | eq_ref | PRIMARY | …
Comment: Multi Column indexes vs Index Merge
June 1, 2010 By 31 Comments
… confused. 1. mysql [localhost] {msandbox} (test)> EXPLAIN SELECT avg(length(val)) FROM …| i2,i1 | 4,4 | NULL | 959 | USING intersect(i2,i1); USING WHERE | 6. +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ 7. 1 row …
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… useful. SELECT * FROM t WHERE name=’kahxailo’ AND i > 100000; With a index on (name) the execution plan is Using intersect(name,PRIMARY); Using where and with an index on (name,id) the execution plan changes to Using where. There… and it can find the three different types of keys explained before. Lets try it: mysql> show create table t; [...] PRIMARY…
Comment: Adjusting Innodb for Memory resident workload
March 27, 2009 By 11 Comments
… size was smaller than innodb_pool_buffer (almost all tables using InnoDB), and server still had a lot of free memory… with “using intersect” in “Extra” and “index_merge” in “type” EXPLAIN fields. Other queries were fine. Further investigation showed that all tables used by…`s RHEL 5.0). Binary log is on (this instance used as a master for replication process). What happened? Is this…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… instead of using BETWEEN [1]. As all boolean operators are ‘AND’, mysql can use either indexes built on multiple columns or intersection of multiple accesses by indexes. When we talk about index_merge_intersection, we…, Gender) or (City, Gender, Age) – you should benchmark and check EXPLAINS which one is better for all combinations of (male, female…
Comment: Using UNION to implement loose index scan in MySQL
August 11, 2006 By 15 Comments
…intersect row pointers from both indexes. If you would need OR in this case Index merge would apply. mysql> explain… | NULL | 38821 | Using sort_union(age,zip); Using where | …
Comment: Data mart or data warehouse?
December 16, 2010 By 14 Comments
… use a bridge table with an allocation factor. In this case the categories would be a separate dimension and have an intersection… de Niro and Al Pacino twice. All these approaches are explained here too: http://www.pythian.com/news/364/implementing-many… being an all-round pentaho starter’s guide it also explains these basic data warehousing techniques, and illustrates them with examples…
