… 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…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
tgabi, Multiple column indexes will be a lot faster than multiple single column indexes in case there is good cardinality on both columns and full index can be used. Relaying on index-merge is actually very typical design mistake.
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… 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… 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
… what you really disagree with. It is the question of multiple column indexing speeding up your queries. If they do not. There is… 5 column index it will be faster than 5 single column indexes. And also much easier to maintain. Really it may look as columns are scary but 5 ints for example is just 20 bytes so the index would be same as index on…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… scan to index ref scan (causing a lot of IOs) In case of low selectivity indexes you too can often build multiple column indexes, assuming you can use index prefix which is selective enough. Though there are times when bitmap indexes…
Post: Multi-Column IN clause - Unexpected MySQL Issue
April 4, 2008 By 21 Comments
… massive amount of urls. To save on indexes instead of using URL we index CRC32 of the URL which allows to find… using multiple column IN makes MySQL to pick doing full table scan in this case, even though the cardinality on the first column… case we could simply rewrite query using more standard single column IN clause: mysql> explain SELECT url FROM 106pages.106pages WHERE…
Comment: Multiple column index vs multiple indexes
August 26, 2008 By 13 Comments
What about the impact of multiple column index on ORDER BY speed ? e.g. : SELECT * FROM a WHERE b=1 AND c=2 ORDER BY d Here, an index on (b,c,d) will avoid MySQL using filesorting. If three indexes are put on b, c and d, will MySQL be able to use index merge on d to avoid filesorting ?
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… seen developers ask the database to do row lookups by multiple columns of varchars is likely. Don’t do it. I can… remember > we’re here to help. Partial agreement: “Use Indexes smartly.” Indexing columns that have very low cardinality will often be a waste… the column’s possibilities. Also, indexing the full width of very wide columns will take up tremendous amounts of memory and disk. Because indexes…
