Multicolumn index and searching

[ejaboneta]

I'll try to provide as much details as possible. I'm creating a database of people with dynamic fields. To achieve this, I've created tables for `people`,`fields` and `values`.

• people: has an id and name
• fields: has an id and field name
• values: has an id, peopleid, fieldid, value, and delta(because there may be multiple values for certain fields)

 

 

The index on the `values` table is set up with peopleid,fieldid,delta.

 

Question 1: is this the best way to index the table?

 

Question 2: if I wanted to search the table for first name(fieldid=0) and last name(fieldid=1), is this the best way to do it?

SELECT f0.peopleid FROM `values` f0 JOIN `values` f1 ON f0.peopleid=f1.peopleid WHERE (f0.fieldid= 0 AND f0.value='John') AND (f1.fieldid= 1 AND f1.value='Doe')

Please also let me know if there is a better way to structure the database and any other tips. Thank you.

Edited July 29, 2013 by ejaboneta

[ejaboneta]

MySQL database is  5.1.56

[PravinS]

you can optimize your SQL queries by checking that how MySQL executes your queries and that can be done by using EXPLAIN statement

 

refer: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

 

so when you index your table use EXPLAIN statement to check its possible keys, rows and other details