…, then worst case you could be reading the same page multiple times into the buffer pool. So considering this drawback, Batched… point index lookups to one or more index range lookups. This means MySQL can employ many other optimizations like for example if columns other then the secondary key columns…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index…: For benchmarked queries we can see Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… Index merge works much better than multiple column indexes. This is in case you’re using OR between the columns. In this case the combined index… combined index in this case. In case you’re using OR between columns – single column indexes are required for index merge to work and combined indexes…
Post: How to find wrong indexing with glance view
August 21, 2008 By 27 Comments
…. If you have queries with multiple column restrictions in WHERE clause you most likely will need to have multiple column indexes for optimal performance. But wait. Do not go ahead and index all combinations. This would likely…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… the indexes. Lets first speak about naming. If you do not specify index name MySQL will name index by the first column of index created, if there is such index already it will… names are not really helpful for multiple column indexes – you can’t easily see what is the index MySQL trying to use from explain…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… there are many cases where multiple column indexes don’t work. That’s all. By looking at the index structure alone you cannot draw… seems 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’re…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… you always need multiple column indexes but the fact it is quite typical there are some queries which will benefit 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: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
… tbl WHERE index_col like “%0%” for each index. Only one such query per index is enough even if it is multiple column index. To fetch BLOB/TEXT externally stored columns you can…, say you have bunch of tables having few indexes – should you run multiple queries in parallel to get best preload speed ? It…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
…. Speaking about index merge – indeed there are cases when index merge works when multiple column index does not however if they both work multiple column index are most…% of all cases. If you made deliberate choice going with multiple column indexes good for you – if you can make this deliberate choice…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… Will only use only first column of multiple column index in case you have Range on the first column like col1 BETWEEN 5 and 10 – it is exactly skill of creating multi column indexes to ensure they… hell a lot of difference) Saying MySQL can’t use multiple column indexes is simular to saying MySQL always does full table scan…
