… require 1TB in size. Now to see this point of intersection, let’s review different workload (which actually is closer to…=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on…/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are all defaults, as I understand from documentation TokuDB comes with…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… off by default with default size of 1MB while in MySQL 5.5 and before it was “ON” by default with query cache… sense in my opinion. MySQL 5.6 adds a lot more optimizer switches which you can play with: mysql [localhost] {msandbox} (test) > select…_union=on,index_merge_intersection=on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… that didn’t understand this limitation would often have tables with lots of single-column indexes on columns which commonly appeared… result sets. Replace that “OR” with an “AND” and you might see the set-theoretic intersection of the result sets. Sounds like… intersect(user_type,status,parent_id); Using where; Using index; Using filesort At first glance, this might not look too bad. MySQL…
Post: SystemTap - DTrace for Linux ?
September 14, 2009 By 17 Comments
… story going on. To profile userspace application with SystemTap your kernel should be patches with uprobes patch, which fortunately is included in…, this case is maybe too simple, but basically you can intersect internal MySQL function and script (measure time, count of call, statistics…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… example: mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> show… you to identify them with more meaningful names. Redundant keys on composite indexes Let’s start with an example: mysql> show create table…’ AND i > 100000; With a index on (name) the execution plan is Using intersect(name,PRIMARY); Using where and with an index on…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…intersect(i1,i2); Using where | +—-+————-+———+————-+—————+——-+———+——+——+————————————-+ 1 row in set (0.00 sec) As you can see in this case MySQL does the Intersection… to deal with ranges: mysql [localhost] {msandbox} (test)…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… multiple columns or intersection of multiple accesses by indexes. When we talk about index_merge_intersection, we should understand that mysql finds one… looking for any gender. Other. Location is mandatory with quite good selectivity comparing with other enumns. Age as well is mandatory, but…
Comment: Multi Column indexes vs Index Merge
May 10, 2012 By 31 Comments
… (intersect). Below are two explains that I executed for the same SQL query with a couple of mins gap on the same mysql… merge executes in 3.8 secs. I dont understand why MySQL uses index merge after a couple of mins when previously…_ix | prjid_ix,item_desc | 4,1 | NULL | 294 | Using intersect(prjid_ix,item_desc); Using where | | 1 | SIMPLE | PersonTbl2 | eq…
Comment: Adjusting Innodb for Memory resident workload
March 27, 2009 By 11 Comments
… set of queries that hurt performance badly, all of them with “using intersect” in “Extra” and “index_merge” in “type” EXPLAIN fields… with low cardinality (< 100). I just dropped those indexes and queries started to execute in less then a second. We using MySQL 5.0.27 with no patches (except maybe for OS vendor specific…
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 i and j having just 3 values… of low cardinality even though MySQL can’t use index well. You’re right MySQL can’t and MySQL does not – Full table… is also worth to watchout a MySQL may decide not to do Index merge (either intersection or union) but instead do full…
