… identifies the relationship, and whether you need to provide a hint or not. Running SchemaSpy (with Foreign Keys) When you run… possible relationships between columns/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ (woops!), single column…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… use index hints. Index hints are exactly what they sound like – “help” for the optimizer to favor or disfavor a particular index or set of indexes so that the query executes in a specific fashion. Index hints are fairly unintrusive and… that the index hint is now doing more harm than good. Caveats aside, there are some very good reasons to use index hints for…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… 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…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering… sec) As you can see if given no hint MySQL will prefer to use index on (cat_id,seller_id) and sort… using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint. The…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… a covering index and a full index scan in the EXPLAIN output. While a covering index (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index) is according… will always be in memory, we should add the FORCE INDEX hint to ensure optimal response time. Now let’s modify the…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… single column indexes (by hinting optimizer to ignore combined index) mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index (combined… hint would be added at some point. Finally let me mention the case when Index merge works much better than multiple column indexes…
Comment: The Optimization That (Often) Isn't: Index Merge Intersection
December 15, 2012 By 6 Comments
… conditions on those columns, we can easily see that the indexes on status and parent_id are effectively useless, particularly when… simply sees “hey, we have these three indexes and they fit the conditions for an index_merge, so let’s do it…-based) than my index hint query which has an estimated search space of about 32K. AFAIK, index hints (or multi-column indexes, when it makes…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
… of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into… is executed for example SELECT STRAIGHT_JOIN * FROM A FORCE INDEX(A) JOIN B The other part is session variable. If…_size=DEFAULT after executing the query. I noticed in production hints are used much more frequently than setting session variables for…
Comment: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… not mentioned it in the article but there is IGNORE INDEX() hint to force full table scan. There is no rule of… speaking about ? OPTIMIZE helps for certain problems – ie it sorts indexes themselves and removers row fragmentation (all for MYISAM tables). It…’t make row retrieval which is done by index sequential one. One more hint if you have all your ranges by specific…
Comment: Why InnoDB index cardinality varies strangely
October 8, 2009 By 5 Comments
… with the various indexes on tables. The query would not use the same execution plan for each execution with Index Hints, but when we analyze the table the execution plan is as per the Index hints. I am not…
