mysql show table / columns - performance question

Question

Hi, I'm working on a basic php/mysql CMS and have a few questions regarding performance.

When viewing a blog page (or other sortable data) from the front-end, I want to allow a simple 'sort' variable to be added to the querystring, allowing posts to be sorted by any column. Obviously I can't accept anything from the querystring, and need to make sure the column exists on the table.

At the moment I'm using

SHOW TABLES;

to get a list of all of the tables in the database, then looping the array of table names and performing

SHOW COLUMNS;

on each.

My worry is that my CMS might take a performance hit here. I thought about using a static array of the table names but need to keep this flexible as I'm implementing a plugin system.

Does anybody have any suggestions on how I can keep this more concise?

Thankyou

Answer 1

You could query the table up front and store the columns in a cache layer (i.e. memcache or APC). You could then set the expire time on the file to infinite and only delete and re-create the cache file when a plugin has been newly added, updated, etc.

Answer 2

I guess the best bet is to put all that stuff ur getting from Show tables etc in a file already and just include it, instead of running that every time. Or implement some sort of caching if the project is still in development and u think the fields will change.

Answer 3

Since you're currently hitting the db twice before you do your actual query, you might want to consider just wrapping the actual query in a try{} block. Then if the query works you've only done one operation instead of 3. And if the query fails, you've still only wasted one query instead of potentially two.

The important caveat (as usual!) is that any user input be cleaned before doing this.

Answer 4

If you using mysql 5+ then you'll find database information_schema usefull for your task. In this database you can access information of tables, columns, references by simple SQL queries. For example you can find if there is specific column at the table:

SELECT count(*) from COLUMNS 
WHERE 
    TABLE_SCHEMA='your_database_name' AND
    TABLE_NAME='your_table' AND
    COLUMN_NAME='your_column';

Here is list of tables with specific column exists:

SELECT TABLE_SCHEMA, TABLE_NAME from COLUMNS WHERE COLUMN_NAME='your_column';