… as this thread started out by alluding to lots of single column indexes generally aren’t a good idea, well MySQL forces this… I need an index on order_date to help with finding orders for a given date. 8 columns 6 indexes on single columns. This is silly. You can imagine a table with more columns and FKs and more indexes. Sure you…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… a few columns from that index without losing any performance. And there’s the problem of mysql choosing single column indexes where multi-column indexes are present, and wasting memory and disk space. I still think that single column indexes make more sense…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
… containing single tinyint column which is almost shortest type possible (CHAR(0) could be taking less space), table with 1 tinyint column and char(99) column and table with 100 tinyint columns. The former two tables have the… stored in BTREE index very similarly to how indexes are stored. Summary: Beware of dynamic row format tables with many columns they might…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… point: My point is not what single column indexes are bad and you always need multiple column indexes but the fact it is quite typical… from multiple column indexes. If I see all tables have only single column indexes this typically means someone does not know about multiple column indexes and how…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… parts as covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index” in Extra column. mysql> explain… as covering index as we only have portion of “j” in the index. It is interesting I tested it with single character column values which all do fit in the index but MySQL does…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… performance. You can also use this approach when first key column is not in where clause at all if it has… would like to only lookup people within single zip I would advice to use index in (zip,age) instead of using this… birth instead of age, and put zip as first column in the index as it is more selective but it is good…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… see how many index parts are actually used for row lookup. Very common problem I see is multi column indexes which are used… uses 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 couple of columns index can be simply much smaller than the data which is one of the reason covering indexes help to…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… are many cases where multiple column indexes don’t work. That’s all. By looking at the index structure alone you cannot draw… that we disagree on index merge versus multiple column index. I’m saying that I see index merge working when multiple column indexes don’t. You… why, but I don’t disagree. One thing is clear: single column indexes are not enough, no doubt about that. Regarding billions of…
Comment: Multi Column indexes vs Index Merge
October 27, 2011 By 31 Comments
… 5 columns. I do a select using a WHERE on every of those 5 columns ANDed. Using the primary key (multicolumn index) it takes 25s, using a single index in one of the columns it takes…. The primary key has cardinality of about 7M and the single column about 80. Am i missing somehting?
Comment: Multiple column index vs multiple indexes
August 25, 2008 By 13 Comments
… single column indexes. I’ve used a 8 GB RAM computer dual core CPU. Tables are less than 1 GB in size. The index…> show index from t1000; +——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment…
