… being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups… traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record… a similar thing when benchmarking ICP, the optimizer made a wrong choice. It looks like that there is still improvement and…
Comment: How Does Semisynchronous MySQL Replication Work?
March 15, 2012 By 23 Comments
Hi Mark, Which part of the following article is wrong according to you ? http://www.dbi-services.com/index.php/blog/entry/mysql-data-high-availability-with-semi-synchronous-replication Thanks for your feedback, best regards
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… to pick and choose indexes and it could really be picking up a wrong index simply because of the wrong estimate. The customer reported…_created key_len: 1 ref: const rows: 185440 Extra: Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count…_len: 1 ref: NULL rows: 339184 Extra: Using where; Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… do not specify index name MySQL will name index by the first column of index created, if there is such index already it will… comes from the automatic naming – depending on order you add indexes indexes can get different names which makes scripted upgrade and downgrade… auto generated index names you may drop the wrong indexes as part of upgrade process just because somebody was adding custom indexes to the…
Post: How to find wrong indexing with glance view
August 21, 2008 By 27 Comments
Quite common beginners mistake is not to understand how indexing works and so index all columns used in the queries…. separately. So you end up with table which has say 20 indexes but all single column ones. This can be spotted with… need to have multiple column indexes for optimal performance. But wait. Do not go ahead and index all combinations. This would likely…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is very good to be able… to use second index and hence index merge, what does it turn to ? It is not combined index but single index on another column… rows to traverse, which is wrong of course – it can’t be more than if only index prefix is used. MySQL does…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes… 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 Sort Data Another great benefit of BTREE index…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… it it can be used in the queries. This is wrong and hurts MySQL Performance. It is enough to create PRIMARY… index is significant, index (A,B) is not duplicate to index (B,A) So now what are Redundant indexes when ? I call redundant indexes BTREE indexes which are prefix of other index, for…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support… etc. All clauses which follow the range in the index will not use index for their operation. Let me give one more… also explain which indexes do you have on the table. SHOW CREATE TABLE is the best. Otherwise I can get you wrong.
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… it looks quite good – there is index on `tag_name` in `tags`, there is index on (`itm_prd_id`, `itm_order…_len: 98 ref: const rows: 1 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: p type… key_len is 8 bytes, so the full index length. It means both index columns will be used to execute the query…
