Checking If Field Is Indexed

[carbonize]

Ok I'll be brief. I need to find a way to check if a particular MySQL field has been indexed or not. It is for the smart updater I am writing for Lazarus.

[TweezerMan]

There's more than one way to do this.

 

1) mysql_field_flags() function in PHP

 

If the field is indexed, the string returned by mysql_field_flags() will contain "primary_key", "unique_key", or "multiple_key". I'd suggest searching the returned string for "_key" (common to all 3).

 

2) 'SHOW INDEX FROM table_name' query

 

You could run a SHOW INDEX query on the table containing your field (which returns a table), and search for a record containing your field (by examining the 'Column_name' field of each record). If the field is indexed, it will be present, and if it is not, it won't.

 

3) 'DESCRIBE table_name field_name' query

You could run a DESCRIBE query on the table and field, which will return a single record containing information about your field. If the field is indexed, the "Key" column will contain "PRI", "UNI", or "MUL". Otherwise, the "Key" column will be empty.

 

To get a better idea of what SHOW INDEX and DESCRIBE queries return, you can run them in the SQL tab in phpMyAdmin and view the results in your browser.

 

Hope this helps...

[carbonize]

Cheers I'll go with the SHOW INDEX route.