MySQL Forums
Forum List  »  Performance

Re: Single index or composite indexes?
Posted by: James Day
Date: March 31, 2005 10:51PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
3041
March 31, 2005 08:42PM
Re: Single index or composite indexes?
1981
March 31, 2005 10:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.