… 3 second query into a millisecond query. It’s hard to argue with success, although in many cases I recommend against using index hints… might see USE INDEX() and suddenly start believing that they can always outsmart the optimizer by manually manipulating indexes in every query they write…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 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 case which is probably the worse decision it could do. Indeed the query takes 360ms Note…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… with this one: which queries sometimes use different indexes, and what fraction of the time is each index chosen? SELECT iu.query_id, CONCAT_WS…(*) AS variations FROM index_usage GROUP BY query_id, db, tbl HAVING COUNT(*) > 1 ) AS qv USING(query_id, db, tbl); +———————-+————————————————————–+————+—–+———+ | query_id | idx | variations…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… both directions for different queries. This will use fast primary key for some queries and use key K as covering index for lookup in other… ORDER BY B will use index (A,B) for sorting optimization, for more complicated conditions you will need to use something like Trick… lot more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary…
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 cannot be used for filtering records. For…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… extending index when there are queries which can use more key part. Typically this is safe operation, unless index length increases dramatically queries which can use index can also use prefix…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…. 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…: 300584 Extra: Using index The optimizer chooses the full index scan. It is a good choice because the index now covers the query, which means…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… MySQL can use indexes, such as: Index merge improvements. But I expect that defining the right compound index will still be superior. Index condition pushdown. Q: Is it always the case that no second-star index exists for a query…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… which indexes would it use. At the end of the process you’ll have an output with a list of not used indexes. This is an example: root@debian:/var/log# pt-index-usage slow.log slow.log… sometimes the real query can use a different execution plan. pt-index-usage tries to convert non-SELECT queries to SELECT queries and is not…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… A>0 instantly jumping to onces have B>6 by using index. It is possibe. So I was shocked and upset to… databases. Designing the indexes for MySQL you should only make sure queries use “=” for all keyparts in the last of index. Only last one… etc. All clauses which follow the range in the index will not use index for their operation. Let me give one more example…
