… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… MySQL prefers when it is possible to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index… Extra: Using where 1 row in set (0.00 sec) As you can see if given no hint MySQL will prefer to use index on… badly selective using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint. The other…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… | NULL | 959 | Using intersect(i2,i1); Using where | +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ 1 row in set (0.00 sec) Hm… Optimizer decides to use index merge in this… know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used. I hope hint would be…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX…=DEFAULT after executing the query. I noticed in production hints are used much more frequently than setting session variables for given query… it is not created dynamically some complex way) – adding extra MySQL calls with possibly their own error control is more complicated…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… it without side load. Setting up 2 MySQL Servers side by side (for example with MySQL Sandbox) can especially be helpful. Once… one by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… you don’t have such compound index then mysql most probably use index on “available” column only using ref access and after that filter by… of compound index. Why? Because more than 70% percents of people will mark “show people with fotos only” and mysql can use index to…, you most probably should cheat a little and give mysql a hint by using “gender IN (‘male’,'female’)” when people looking for any…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… use Index Merge technique for low cardinality table but instead pick to do single index scan. I’m not aware of the optimizer hint which would allow to force index merge as you can do with index accesses in general. Note2 Q2/Q3 can’t use Index… index is faster for Q3 in case of low cardinality even though MySQL can’t use index well. You’re right MySQL can’t and MySQL…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… them in the right order. MySQL can chose only one of them to execute the query – use index to find rows. This is… MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select * from table FORCE INDEX…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… circumvent that you need to help it out with optimizer hints like straight_join to have it look at tables in… indexes generally aren’t a good idea, well MySQL forces this upon you if you actually implement a proper database design and use… operators. MySQL will create the following indexes: unique index on order_id index on item_type_id index on locale_id index on currency_id index on…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… >= MySQL-4.1.1 and stored already) 6353 InnoDB: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY…) == dict_index_get_page(index)) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com… example. Another hint – you may want to CHECK your MyISAM table you use for recovery after MySQL crashes to make sure indexes are not…
