Query on information-schema

  • Hi There,

    Just to suggest a modification in the following query for better performance :

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = ‘wp534_hsa_plugin’ AND column_name = ‘hsa_options’

    by adding table_schema= DATABASE() in the where clause :

    The final version should be :

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema=DATABASE() and table_name = ‘wp534_hsa_plugin’ AND column_name = ‘hsa_options’;

    This is because, quering against information_schema when there are a lot of databases and tables could be a heavy operation when table_schema is missing.

    Thanks

    Stofa.

Viewing 1 replies (of 1 total)

  • Data to back this request up, as I’m seeing a lot of these running for 10+ seconds on the servers I look after:

    Without the additional WHERE clause:

    mysql> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'ru_hsa_plugin' 
AND column_name = 'hsa_options';
+-------------+
| COLUMN_NAME |
+-------------+
| hsa_options |
+-------------+
1 row in set (7.24 sec)

    With the additional WHERE clause:

    mysql> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema=DATABASE() 
AND table_name = 'ru_hsa_plugin' 
AND column_name = 'hsa_options';
+-------------+
| COLUMN_NAME |
+-------------+
| hsa_options |
+-------------+
1 row in set (0.01 sec)
    • This reply was modified 6 years, 2 months ago by serpentskiss.
Viewing 1 replies (of 1 total)
  • The topic ‘Query on information-schema’ is closed to new replies.