Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Is a unique multi-column index as good as a unique single column index.

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,
profile text,
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.
0
iSac1984
Asked:
iSac1984
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just curious why your id is split in 2 fields (I do know some reasons for that, so it's not impossible :)

anyho:
the more tables are related to this one, the better a dedicated primary key field will be, as you will save the space you "loose" on this table on all the other tables referenced (apart from the coding issues).
after that, you can still make id_one + id_tho as unique index, ensuring that it contains unique values indeed.
0
 
iSac1984Author Commented:
Thanks angellll:

Let's assume that due to my strange set-up, and it is a little strange (although I base that on absolutely no knowledge of other set-ups), I would not save any space in other tables by having the unique single column primary key. Is there any query performance improvement, or is my "awkward looking = inefficient" theorem off base.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the single-column solution uses the same space as the 2-column, there will be no space difference, and hence, no (real) difference on the index part (ie the indexed values lookup).
0
 
iSac1984Author Commented:
Thanks angellll:

So is this accurate: in general a 10 byte single column index will be exactly as fast as a two column index whose columns are each five bytes. This would make sense based on the little I know about indexes.
0
 
iSac1984Author Commented:
Thanks so much!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now