From: Date: August 25 2008 11:19am
Subject: bzr commit into mysql-5.1 branch (gluh:2719) Bug#38918
List-Archive: http://lists.mysql.com/commits/52424
X-Bug: 38918
Message-Id: <20080825091947.D7D3624A0078@eagle.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///home/gluh/MySQL/bazaar/mysql-5.1-bug-38918/

 2719 Sergey Glukhov	2008-08-25
      Bug#38918 selecting from information_schema.columns is disproportionately slow
      The problem: table_open_method is not calculated properly if '*' is used in 'select'
      The fix: added table_open_method calculation for such case
modified:
  mysql-test/r/information_schema.result
  mysql-test/t/information_schema.test
  sql/sql_show.cc

per-file messages:
  mysql-test/r/information_schema.result
    test result
  mysql-test/t/information_schema.test
    test case
  sql/sql_show.cc
    The problem: table_open_method is not calculated properly if '*' is used in 'select'
    The fix: added table_open_method calculation for such case
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result	2008-03-27 11:54:45 +0000
+++ b/mysql-test/r/information_schema.result	2008-08-25 09:19:38 +0000
@@ -1646,4 +1646,13 @@ drop table t1;
 drop function f1;
 select * from information_schema.tables where 1=sleep(100000);
 select * from information_schema.columns where 1=sleep(100000);
+explain select count(*) from information_schema.tables;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
+explain select count(*) from information_schema.columns;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	columns	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
+explain select count(*) from information_schema.views;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	views	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
 End of 5.1 tests.

=== modified file 'mysql-test/t/information_schema.test'
--- a/mysql-test/t/information_schema.test	2008-03-27 11:54:45 +0000
+++ b/mysql-test/t/information_schema.test	2008-08-25 09:19:38 +0000
@@ -1338,4 +1338,12 @@ where state='User sleep' and
 info='select * from information_schema.columns where 1=sleep(100000)';
 --source include/wait_condition.inc
 
+
+#
+# Bug#38918 selecting from information_schema.columns is disproportionately slow
+#
+explain select count(*) from information_schema.tables;
+explain select count(*) from information_schema.columns;
+explain select count(*) from information_schema.views;
+
 --echo End of 5.1 tests.

=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc	2008-08-20 22:18:33 +0000
+++ b/sql/sql_show.cc	2008-08-25 09:19:38 +0000
@@ -2962,7 +2962,7 @@ static int fill_schema_table_names(THD *
     @retval       SKIP_OPEN_TABLE | OPEN_FRM_ONLY | OPEN_FULL_TABLE
 */
 
-static uint get_table_open_method(TABLE_LIST *tables,
+uint get_table_open_method(TABLE_LIST *tables,
                                   ST_SCHEMA_TABLE *schema_table,
                                   enum enum_schema_tables schema_table_idx)
 {
@@ -2973,12 +2973,22 @@ static uint get_table_open_method(TABLE_
   {
     Field **ptr, *field;
     int table_open_method= 0, field_indx= 0;
+    uint star_table_open_method= OPEN_FULL_TABLE;
+    bool used_star= true;                  // true if '*' is used in select
     for (ptr=tables->table->field; (field= *ptr) ; ptr++)
     {
+      star_table_open_method=
+        min(star_table_open_method,
+            schema_table->fields_info[field_indx].open_method);
       if (bitmap_is_set(tables->table->read_set, field->field_index))
+      {
+        used_star= false;
         table_open_method|= schema_table->fields_info[field_indx].open_method;
+      }
       field_indx++;
     }
+    if (used_star)
+      return star_table_open_method;
     return table_open_method;
   }
   /* I_S tables which use get_all_tables but can not be optimized */