Re: Single index or composite indexes?

[James Day]

Before version 5 of MySQL, the composite index. The best order depends on the way your queries will use the records. If necessary you can have one index in each possible order.

For MySQL 5, see http://dev.mysql.com/doc/mysql/en/index-merge-optimization.html .

Remember that if you want the database to enforce uniqueness you will need at least one unique index which contains the userid, formname and fieldname fileds, in any order.

If your normal use is retrieving all fields for one user for one form, put the user id as the first part of the primary key and the form name as the second part. If using InnoDB, this will keep the records for each user in the same or similar database pages and improve performance, even if they are created at different times.

For both InnoDB and MyISAM, if the index is a covering index (contains all fields used in the query) this will also be very fast because the index will be used to retrieve the data, without the chance of an extra disk seek per record to get to the data records.