Solved

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

Posted on 2008-10-08
5
405 Views
Last Modified: 2012-08-14
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
Comment
Question by:iSac1984
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22667058
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
 

Author Comment

by:iSac1984
ID: 22667220
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 22667245
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
 

Author Comment

by:iSac1984
ID: 22667283
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
 

Author Closing Comment

by:iSac1984
ID: 31504141
Thanks so much!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question