bzr commit into mysql-5.1 branch (Sergey.Glukhov:2770) Bug#38918

[Sergey Glukhov]

#At file:///home/gluh/MySQL/mysql-5.1-bugteam/

 2770 Sergey Glukhov	2008-10-09
      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-10-09 07:50:29 +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-10-09 07:50:29 +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-10-02 10:08:15 +0000
+++ b/sql/sql_show.cc	2008-10-09 07:50:29 +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 */