MySQL - Most Efficient query to get multiple settings?

Name: Settings. Columns: id / name / value

Example

id / name / value

1 / site_name / Blah

2 / root_path / blah

3 / something / bloh

What is the most efficient way to select a number of these settings without getti开发者_开发知识库ng them all?

Use a WHERE:

SELECT * FROM `table_name` WHERE id='1'

OR:

SELECT * FROM `table_name` WHERE name='site_name'

OR with an in:

SELECT * FROM `table_name` WHERE name in ('site_name','root_path')

OR (get only ids):

SELECT id FROM `table_name`;

etcetera etcetera -- this can go on with multiple combinations.

An IN() list. This will effectively utilize an index on name column if you have one defined.

select * from `settings`
WHERE name IN('site_name', 'root_path')

SELECT name,value WHERE name in ('site_name','something')

Or if you know ids.

SELECT name,value WHERE id in (1,3)

If these are simple 3 column rows, and of settings, efficiency really isn't a concern. I can't see you having more than 10k records in that, and in fact I really can't see you having more than 100. Lookup time will be very neat either way. Indexing 'name' will probably be the best bet, as im guessing that's what you will be querying against.