… keys” ? Should you try to force it ? mysql> explain select * from article force index (PRIMARY) where article_id=10; +—-+————-+———+——+—————+——+———+——+——-+————-+ | id | select_type… is not going to use the index. Lets check if your guess is right: mysql> explain select * from article where article_id…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… slower In this post I’m going to explain the different types of duplicate indexes and how to find and remove them… and it can find the three different types of keys explained before. Lets try it: mysql> show create table t; [...] PRIMARY… prefix of the clustered index [...] # To shorten this duplicate clustered index, execute: ALTER TABLE `test`.`t` DROP INDEX `name`, ADD INDEX `name` (`name`); # Key…
Post: Indexes in MySQL
June 2, 2006 By 29 Comments
… table scan if selectivity over 25%. What with MySQL: mysql> EXPLAIN SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1; +—-+————-+——-+——+—————+——+———+——-+——-+————-+ | id | select… ID1=1) d2; 0.9492 = 94.92% Explain still claims MySQL will use index. Execution time: SELECT COUNT(SUBNAME) FROM t2 WHERE…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” which was added in MySQL 4.1 EXPLAIN EXTENDED can show… a part of any good SQL optimizer. EXPLAIN EXTENDED adds a warning message to the EXPLAIN output which displays additional information, including… will be read. This is because these tables contain no indexes. There is another interesting thing, and I probably should have…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
….56 sec) Let’s check execution time with and without index mysql> select count(name) from testr force key (has_something… with index the time is by 3.5 times slower. Good that mysql in this case choose do not use index mysql> explain… with index is still 2 times slower. and this time mysql is going to use index in execution plan: mysql> explain select count…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
… | PRIMARY | PRIMARY | 4 | func | 1 | Using index; Using where | +—-+——————–+——–+—————–+—————+———+———+——+———+————————–+ 2 rows in … fraction of the second. Anyway EXPLAIN EXTENDED is very valuable addition to EXPLAIN…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… it connect over the SSH tunnel to query EXPLAIN and find out the index usage on the remote server, and store the…’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | queries | | tables | +———————–+ Now let’s run some queries…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… in MySQL 5.6 change the best practices for index design? Fundamentally, index design best practices will not change. MySQL 5.6… can use indexes, such as: Index merge improvements. But I expect that defining the right compound index will still be superior. Index condition pushdown… is when we see “Using temporary; Using filesort” in the EXPLAIN report. Hopefully, the size of this interim result set is…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… do not specify index name MySQL will name index by the first column of index created, if there is such index already it will… indexes – you can’t easily see what is the index MySQL trying to use from explain, such as if you have some smart index naming conversion such as idx_i_j for the first index it…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support… etc. All clauses which follow the range in the index will not use index for their operation. Let me give one more….S If you post queries in your comments please also explain which indexes do you have on the table. SHOW CREATE TABLE…
