Two columns indexed as one, or one column indexed

What is preferred? Take two columns and INDEX them together, or create a new column and INDEX that column? For examp开发者_StackOverflow中文版le:

INDEX(user_id, key)

Then run:

SELECT value FROM preferences WHERE user_id = 1 AND key = 'something';

Or create another column named reference and that would contain a value 1_something and then run:

SELECT value FROM preferences WHERE reference = '1_something';

Best regards,

Andrew

As with all things indexing, it depends on your specific situation.

Personally I would opt for the two columns, one index version as it's more correct when thinking about the data, makes queries clearer, allows for range selections should you want them, etc. The composite reference column will most likely be of a varchar type, while the two separate could be an int and a varchar which even though it's two comparisons I believe would be faster..

Will you ever query on user_id without the key? IE to return all preferences for a specific user.

Will you ever perform range queries on either all users with a certain setting, or making the 1_something inefficient? (comparing varchars against a range of values == inefficient)

Can't be certain without knowing what other queries you might want to run, how big the tables are expected to be, sample values, etc, etc.

The first one (index on 2 columns)looks much better. This index will be also used for queries that benefit from index on user_id. In case of one extra column you will have to synchronize it's value every time user_id or something changes.

The First one is always better, in the case when you are querying with just one column user_id, then still you can use same index. and composite key index gives better key search also.