… table: users type: index_merge possible_keys: PRIMARY,parent_id,status,user_type key: user_type,status,parent_id key_len: 2,2,4 ref: NULL rows: 8100 Extra: Using intersect(user_type,status,parent_id); Using where; Using index; Using… select_type: SIMPLE table: users type: ref possible_keys: user_type key: user_type key_len: 2 ref: const rows: 32404 Extra: Using where…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… only part of the index when full index can be used or using shorter index while there is longer index available. The last item… table: msg132 type: ref possible_keys: group_id,thread_id,groupid_published,grp_subj key: group_id key_len: 8 ref: const,const rows… select_type: SIMPLE table: msg132 type: range possible_keys: group_id key: group_id key_len: 12 ref: NULL rows: 51 Extra: Using where…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
… not use index mysql> explain select count(name) from testr where has_something=0; +—-+————-+——-+——+—————+——+———+——+———-+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref… index in execution plan: mysql> explain select count(name) from testr where has_something=0; +—-+————-+——-+——+—————+—————+———+——-+———+——-+ | id | select_type | table | type | possible_keys | key | key_len…
Comment: COUNT(*) for Innodb Tables
April 3, 2009 By 32 Comments
… use index(physmessage_id_index)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dbmail_messageblks type: index possible_keys: NULL key: physmessage_id_index key_len: 8 ref: NULL rows: 1930310 Extra: Using index
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…: SIMPLE table: goods type: ref possible_keys: cat_id,cat_id_2 key: cat_id_2 key_len: 8 ref: const,const rows: 296338… select_type: SIMPLE table: goods type: ref possible_keys: cat_id key: cat_id key_len: 4 ref: const rows: 989171 Extra: Using where…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously discussed… type: ref possible_keys: role_id_note key: role_id_note key_len: 4 ref: const rows: 10259274 Extra: Using index condition 1 row in…: ref possible_keys: role_id_note key: role_id_note key_len: 4 ref: const rows: 10259274 Extra: Using where; Using index 1 row in…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…key_len: NULL ref: NULL rows: 262144 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys: j key: j key_len… workload results are likely to be different as longer indexes expected to have much worse cache fit, especially if …
Post: Indexes in MySQL
June 2, 2006 By 29 Comments
…| select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——+—————+——+———+——-+——-+————-+ | 1 | …6 times. Why does MySQL choose index access? MySQL doesn’t calculate index selectivity, just estimates count …
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… it is not index prefix. It is important to look at key_len column in explain plan to see how many index parts are… single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index to Sort Data Another great benefit of BTREE index…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… table: dl type: range possible_keys: PRIMARY key: PRIMARY key_len: 3 ref: NULL rows: 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1… index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 10 Extra: Using index…
