Solved

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

Posted on 2008-10-08
5
403 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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

765 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