Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-08
5
Medium Priority
?
408 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 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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

715 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