… MySQL can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes …how MySQL 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 …
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to pick and choose indexes and it could really be picking up a wrong index simply because of the wrong estimate. The customer reported…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… index lookups. So we spoke about how MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL… not allow MySQL optimizer to use second index and hence index merge, what does it turn to ? It is not combined index but single index on… MySQL estimates 20% more rows to traverse, which is wrong of course – it can’t be more than if only index prefix is used. MySQL…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… wrong. The index lookup is being perform but as only actual rows which are found and returned up to the top level MySQL… but because the access type to the tables is “const” MySQL does not count it as access to two tables. In… optimization is possible. For example I might have query which uses some index scans 1000 rows and sends 10… I still might…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… 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… column indexes – you can’t easily see what is the index MySQL trying to use from explain, such as if you have some smart index… 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: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… in Wikipedia table structure. Loose index scan – Number of years ago when I just started using MySQL I thought it would have any… databases. Designing the indexes for MySQL you should only make sure queries use “=” for all keyparts in the last of index. Only last one… also explain which indexes do you have on the table. SHOW CREATE TABLE is the best. Otherwise I can get you wrong.
Post: Why Index could refuse to work ?
September 8, 2006 By 15 Comments
… | Using where | +—-+————-+———+——+—————+——+———+——+——-+————-+ 1 row in set (0.00 sec) Why on the earth index would not be used you would think, even if MySQL… by using “”. You may ask why MySQL can’t use index in this case, simply by converting number to the string and performing index lookup ? This can’t be done as it would result in wrong result for some queries…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… create KEY so it it can be used in the queries. This is wrong and hurts MySQL Performance. It is enough to create PRIMARY KEY and it will enforce unique values and will be used… the index) – such indexes may become way too long to be efficiently used by other queries. There is currently no tool in MySQL distribution…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
… what is ‘Using Index condition’, what I was able to find is Baron’s bug with no mention in MySQL 6.0 manual… Random I/O we had during execution of query, as MySQL used index to scan rows and then did access to data to… works. What else worth to mention here is that MySQL is wrong choosing index scan here. If we try to execute query with…
Post: MySQL Error Message Nonsenses
March 16, 2008 By 7 Comments
… as give some better explanation on what is wrong. The new parser for MySQL was spoke since 5.0 times but it…@sl1 ~]$ perror 124 OS error code 124: Wrong medium type MySQL error code 124: Wrong index given to function The same code instead of… Innodb error code. Other storage engines may or may not use same error codes for same kind of error complicating automatic…
