ejaboneta Posted July 29, 2013 Share Posted July 29, 2013 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/280629-multicolumn-index-and-searching/ Share on other sites More sharing options...
ejaboneta Posted July 30, 2013 Author Share Posted July 30, 2013 MySQL database is 5.1.56 Quote Link to comment https://forums.phpfreaks.com/topic/280629-multicolumn-index-and-searching/#findComment-1442626 Share on other sites More sharing options...
PravinS Posted July 30, 2013 Share Posted July 30, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/280629-multicolumn-index-and-searching/#findComment-1442639 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.