Is a unique multi-column index as good as a unique single column index.
Posted on 2008-10-08
Using MySQL 5.0.45
I have a table that stores user profiles, the create statement looks something like
CREATE table profiles(
id_one int etc,
id_two int etc,
PRIMARY (id_one, id_two))
Although the id_one id_two index uniquely identifies records, it makes for awkward multi-record retrieves. As a relative newcomer to this, my guess would be that an awkward query, with lots of:
WHERE (id_one = x and id_two = y) or (id_one = z and id_two = a) or ...
would be slower than a unique single column index.
Is it worth creating a separate unique single column index? Every byte counts, but if there'd be a good query speed improvement I'd be willing to make it.