…index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index…Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders…the optimizer made a wrong choice. It looks…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes for… case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the index order. It is very important to understand in which conditions index can be used to sort data together with…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… in sorted order. For example when you query AGE=18 with single column BTREE index MySQL will dive into the index to find first matching row and when will continue scanning index in order until it… to traverse, which is wrong of course – it can’t be more than if only index prefix is used. MySQL does not know…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… scanned in reverse order and it will well be. This is how MySQL will optimize indexed ORDER BY col …All clauses which follow the range in the index will not use index for their operation. Let me give one… indexes do you have on the table. SHOW CREATE TABLE is the best. Otherwise I can get you wrong…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… we create KEY so it it can be used in the queries. This is wrong and hurts MySQL Performance. It is enough… example is BTREE index and FULLTEXT index, while other combinations may also make sense. Note: Order of columns in index is significant, index (A,B) is not duplicate to index (B,A) So now what are Redundant indexes when…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…on order you add indexes indexes can get different names which makes scripted upgrade and downgrade processes complicated. If you use auto generated index names you may drop the wrong indexes as part of upgrade process just because somebody was adding custom indexes to…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… is index on `tag_name` in `tags`, there is index on (`itm_prd_id`, `itm_order_timestamp`) in `items_ordered` and indexes on other columns used…__and__itm_order_timestamp key: itm_prd_id__and__itm_order_timestamp key_len: 8 ref: NULL rows: 1306 Extra: Using where; Using index In…,const rows: 206494 Extra: Using where; Using index This query uses 7 bytes of `itm_prd_id__and__itm_order_date` index – 4 bytes is…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… is used so using values generated elseware > might be faster. I disagree with you on this completely because InnoDB uses a clustered index based on (in order… the data much easier. > Regarding if you use many tables you’re doing something wrong it > is frequently told by people with…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… index stats remain as good as new. Looking at stats accuracy is however a wrong way to look at the problem. Your index… as with MyISAM but instead we get very wrong cardinality which shows us index prefix is unique (245900 is estimate for the… are very inexact. Fortunately for most queries which use these stats accuracy at the order of magnitude is enough. Sometimes it is…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
… to undo changes at rollback. Using this approach, support for Fake Changes have been implemented in order to remove the overhead and… clustered index by InnoDB in the absence of a PRIMARY KEY. #851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong estimate for RANGE query with compound indexes). #832528 (Alexey Kopytov). Added a new boolean…
