?
Solved

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

Posted on 2008-10-08
5
Medium Priority
?
410 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
  • 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 1200 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

601 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