From: Evgeny Potemkin Date: August 25 2010 3:46pm Subject: bzr commit into mysql-5.5-bugfixing branch (epotemkin:3181) Bug#39653 Bug#55656 List-Archive: http://lists.mysql.com/commits/116781 X-Bug: 39653,55656 Message-Id: <0L7P00FMET5AXY80@fe-emea-09.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Boundary_(ID_2Y+Q9GXd4tvL36Rjv0HzYQ)" --Boundary_(ID_2Y+Q9GXd4tvL36Rjv0HzYQ) MIME-version: 1.0 Content-type: text/plain; CHARSET=US-ASCII Content-transfer-encoding: 7BIT Content-disposition: inline #At file:///work/bzrroot/55204-bug-5.5-bugfixing/ based on revid:alik@stripped 3181 Evgeny Potemkin 2010-08-25 Bug #55656: mysqldump can be slower after bug #39653 fix. After fix for bug#39653 the shortest available secondary index was used for full table scan. Primary clustered key was used only if no secondary index can be used. However, when chosen secondary index includes all fields of the table being scanned it's better to use primary index since the amount of data to scan is the same but the primary index is clustered. Now the find_shortest_key function takes this into account. @ mysql-test/suite/innodb/r/innodb_mysql.result Aadded a test case for the bug#55656. @ mysql-test/suite/innodb/t/innodb_mysql.test Aadded a test case for the bug#55656. @ sql/sql_select.cc Bug #55656: mysqldump can be slower after bug #39653 fix. The find_shortest_key function now prefers clustered primary key if found secondary key includes all fields of the table. modified: mysql-test/suite/innodb/r/innodb_mysql.result mysql-test/suite/innodb/t/innodb_mysql.test sql/sql_select.cc === modified file 'mysql-test/suite/innodb/r/innodb_mysql.result' --- a/mysql-test/suite/innodb/r/innodb_mysql.result 2010-07-07 12:18:20 +0000 +++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2010-08-25 15:46:17 +0000 @@ -2592,3 +2592,61 @@ COMMIT; COMMIT; DROP TABLE t1; DROP FUNCTION f1; +# +# Bug#55656: mysqldump can be slower after bug #39653 fix +# +CREATE TABLE t1 (a INT , b INT, c INT, d INT, +KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key b +key_len 4 +ref NULL +rows 3 +Extra Using index +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key b +key_len 8 +ref NULL +rows 3 +Extra Using index +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b,c); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key b +key_len 13 +ref NULL +rows 3 +Extra Using index +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b,c,d); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key PRIMARY +key_len 8 +ref NULL +rows 3 +Extra Using index +DROP TABLE t1; +# === modified file 'mysql-test/suite/innodb/t/innodb_mysql.test' --- a/mysql-test/suite/innodb/t/innodb_mysql.test 2010-08-05 12:41:07 +0000 +++ b/mysql-test/suite/innodb/t/innodb_mysql.test 2010-08-25 15:46:17 +0000 @@ -805,3 +805,29 @@ connection default; COMMIT; DROP TABLE t1; DROP FUNCTION f1; + +--echo # +--echo # Bug#55656: mysqldump can be slower after bug #39653 fix +--echo # + +CREATE TABLE t1 (a INT , b INT, c INT, d INT, + KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3); +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b); +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b,c); +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b,c,d); +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP TABLE t1; + +--echo # + === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-08-05 12:53:09 +0000 +++ b/sql/sql_select.cc 2010-08-25 15:46:17 +0000 @@ -13256,14 +13256,15 @@ uint find_shortest_key(TABLE *table, con primary key fields (at least InnoDB and its derivatives don't duplicate primary key fields there, even if the primary and the secondary keys have a common subset of key fields), - then secondary index entry data is always a subset of primary key - entry, and the PK is always longer. + then secondary index entry data is always a subset of or same as + primary key entry. Unfortunately, key_info[nr].key_length doesn't show the length of key/pointer pair but a sum of key field lengths only, thus we can't estimate index IO volume comparing only this key_length value of seconday keys and clustered PK. So, try secondary keys first, and choose PK only if there are no - usable secondary covering keys: + usable secondary covering keys or found best secondary key include + all table fields (i.e. same as PK): */ if (nr == usable_clustered_pk) continue; @@ -13277,7 +13278,19 @@ uint find_shortest_key(TABLE *table, con } } } - return best != MAX_KEY ? best : usable_clustered_pk; + if (usable_clustered_pk != MAX_KEY) + { + /* + If the primary key is clustered and found shorter key covers all table + fields then primary key scan normally would be faster because amount of + data to scan is the same but PK is clustered. + This check assumes that key parts aren't duplicated. + */ + if (best == MAX_KEY || + table->key_info[best].key_parts >= table->s->fields) + best= usable_clustered_pk; + } + return best; } /** --Boundary_(ID_2Y+Q9GXd4tvL36Rjv0HzYQ) MIME-version: 1.0 Content-type: text/bzr-bundle; CHARSET=US-ASCII; name="bzr/epotemkin@stripped" Content-transfer-encoding: 7BIT Content-disposition: inline; filename="bzr/epotemkin@stripped" # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: epotemkin@stripped # target_branch: file:///work/bzrroot/55204-bug-5.5-bugfixing/ # testament_sha1: 6acd6a89ad2f9987fdf7e9ed17db69cc5388a00a # timestamp: 2010-08-25 19:46:21 +0400 # base_revision_id: alik@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWUEpgxAABM5fgHwwePf//3/v 32q////+YArHfW7uVEmqgBujutyx0aHW7dNdaACa+EkUGpqj9Uenqm1P1RvSnlH6npqaaj1ND1Hq NqaD1P1I9R6jQPUGgSUCaNTaKn4BCp5ok/SepqPKaZom1DIAAaADQ5hNGRoaGQwjQyGmjQAYjJkA wgGASIoZFNqbUGk02JPSTaExNoNAgaPU00GAmBCpGjEaYQDTCaAAaGhiGI0GmEYhoASRIyARhATJ gkZqak/Uynqe1RkNPTTUepoANPTUga2SNYbfsDJg5w/coXA6cCi2t6GxBjxtx4+KaJdGw+ezdNx/ 3LPa1OC1u3MdOXN/oYDw9NWF2eMMIX4yWDSCm50bbbkLwZhUtEWsWecV8sNTuwjRzc2IbNlf3BuX 42XhTZa1tAaA+0Nrh3pIKhBx5hzF/E2hQyDi6kx6Tvr2LUm2m0+17g+c1nqSGtkjHZCY4HiYIQn4 ScCw3AwGSoW0XIeIsN8mKTF6iQFbbtPFZ+AFpaaEbTn6wcHhHtadM5nsBUbUUxc6iZEwRPyCgni2 dkxONN1eRtGR2lTFcZi2UecYHScOj6wMWOqP8tyKWYMM7m/nDlWNSizFHZfcr/rkUd+e4KabTpF8 bbmpGOBGdhueoNJ6zWKBikNa4SroLxaNE+JozzTmx3KCNWExTfltbVxY7/Lj58OmPobrTR9+xbVX vpS0LHuNtjRKGYaeyBIjITFwbvN6j9QFxI9g51wfR3jQh7veB9ucsFnHDlaK0VQuYFYWGkBloFQ3 /gDZ3tsZbls6D+65e34B+C4W0dRRET8/y5FId/GhYEIHVmse222x8DPOSRMnVSR6X+MiOXz8itz1 RxYa+JYSToL0GdkZQQ5mJezZG6bykVgAzSwCikX2TFz2YLAaNhQWslWUswiETJhHaXDOQ2wVRaim hU5FB46aCMq3ibsyyDIPiDidTjmDC8hdT2VpB5yQ6x/p2GrRz3Qm1wKqaZbmSuL5tREYR60PVnfZ rdZahReKZYImtXcuff08lGttoLBY6zvLDLfGeJ2iO01yQfMRlfv7Clc+IuJuzAWfgMDOfN4Wp1vU pCHW2Mzq4OriqC5O504cmEzWVQNkifbu0yFzkcCuefmDG/UjXtFtILaNipCJZbMlBhcYEyRfcedY wzVXqRvtuhFpqWZWrdXplCtYTeh0mZZD1Qr1hhrC/ioD68BGB0KNYw13mNdSqGsj7qiESD2GlXtX tyVwjZfnPStZlCpEVt3IsT57hwiaopW1+8VOEAkXlIsRwcMH025ptAyLhpBUmKaYLtWnSoyJPdwG KkYWpi2F6ctPgJER5BVUyEU+NytitbkSFSRjAvb0UC2vRZYSPAWme1+xbBELFcuZaJqvmyYh0k6x gyU8CZVfqOwxmuCZczgZKxY8fTq9EaDEYu8R0VKx7NGt4lNhVfe41HNdxeKhgipPaQNMuyBKgbVd K/QgMLbuvsHKQcSLC57xNQ4jJVltOM+1lnjwsKDmG5QMl3QDZC8L2VDM2vRuS3KpmdqkIDNWtiLV FXAXxS2RVvdO7rjMDEwdgcODQHLt2TEtTMzeBY3GQzTes4tSiz99XkiGY3V5Q6APofqsQGd40ZNN Nt+89wXWmQi4CQOg6AUgbQZgGEnzA2pA9IF4z6GIKQvD0CxgJAwHgqhtgSC+RxlCRH8/2Qp+xnAm BcwyAvArMgPzVQwJwTCU4xnGCIDAPSBXmQLwKgTAP8AkA4/MDaFTggBmXzA2AZjOmIgWFAMUAWDi UwRAcuy3iQM4F4UMARUvgGM8/X3DUjkkhP8WQIcPSIawuyakQUYyUgXB8CLeTDgQA8kG0nNqNPKS mi7mnAoodOSF/20Ko7MYShGK69rDxrngt3oe8NSgrVBhMaeE4bMkjDQDGxjJPQVI4LNLuU8lCaEZ evV0Uwk/3fI4jCsCHKo+8v3nI+FfxUO6sR0xTfgXi5EB3bBj/n0fEjiuhuSXQKlD5UJfefiOOvaI c29gMuw4K3lrMxvW7ZvMQtC70och4RMEVc1yywi5U9Sk8GMpexQT0eokgNYpVQKk5BEevyEbKwkR XdJ2ZDG0xgjHHEy2m8c5IotZq0MVFlaJpgJhyfYOld7pesuGEbt5AzlRtOzfOQgWCU4jqFKwEYIw tcM+1YY0gTou9GpuJM45lAJLBtqE5B6olcbS7ZxGm3sHZZwHMSvVou39yCprPWsVt053T3grRZVU 8whpSigtPKGrfCDWsPxgQLdPJ6I3NEMfi+ZWRvp9TY/J+SL0F4LEMlC1HiuY6LP5HyBl4/PkeQWg VbuEALZjFg9PUJHLs0OGypMwmTDtiiuZ7WFQJTpUCFLUHQqBj4NzQVkmYascy4bLK12gmm6wU3ln 4e4sVqZSCK5+qsijFIy7/qDzpnnOC3nEVInBxMnwVIiHKcy8dXHSMcVBZgJ7kqA6GllPWkscKQuB wuRaFvoLud7zsXCuqQPD33aVB2eYRBuSI+zfFm4Irct9KFWlUR1BXOnFOOjzBkV23SCnD2eR9ck7 unp09N1mcsFH1smY3xNWckSwYBQVN8RUIz9e06FotsH0u8Wdb3dpDJdowvGAeEwTUKQKAu2lUhii 5AuAaFSIZTQXSdEui7NEUThoSimQzZJ04wmCZBcFnjcBLBjbQai3YIf1O4rkAdoxtqTF5FFKq8IG fDAFTl1X1VFQNUFnLomsmPzSNYjeFwZypFUHWaE24kzKTlWShUSHVRS4hNQyALEmJaIgRc88AUZw TGzFV5KotZlQyZktadPkpZg9KsZiuxQ5xDmwBTnahi3iCa4sQ1LuhQUjRhqpiqgqS4OE6dw+LJGq +Szly1V+/iH0vCSNYjhjwZMjg7qcOIGfjcSMl4COGjoHFIhXaH72Lvsm0+foKFkID7OSukohuuPB aXQMzNodOe32uqjmE6CUyWgLKRH6EaNE7hHZ0oFDxVYYaHMs1NFYUkQZFw/anJQcEztMHcHAJ+sX hOcvCf6OQZSIxgQeQ/1+CpvXaGlBRCgaCTLBF4Z9WzuTrg0VQMVg/8wvFMri9lOGxcODVO7u716E kPCCmmDEM4YuIGxSoFVInRn8VPKEmWuFpKafq+Dp1miakWTFoeYMJDlbRTHsFIJlsCLMfRlGwlGF mAJ/QjfqIJPSdwayA8BUUp0Lq3qhXJGsvMJKwTrMDKKZGq9vFbw0BMtjU78FIOIe8ZMMYRMNY0VI MUBw8T34rXPvykwJpbDuxXKxeVU8l4lQtbzUPq7U8W1YKjyJh6xGBhzJ6HcF5VvTnhuVXOTENgzG 8RxzFiWQNG7iQ22bbIE2JqIXaqPE4Nu9fGq1P4titFkBINzKEhYzVq3d6jiFN684DQiAjPsgH8Xc kU4UJBBKYMQA --Boundary_(ID_2Y+Q9GXd4tvL36Rjv0HzYQ)--