Jump to content
Larry Ullman's Book Forums

Am I Misusing Mysql Indexes?


Recommended Posts

Sometimes I have a group of fields that I want to be unique. For instance, say I have dog_name, kennel_name and breeder_name. On their own, they don't have to be unique, but as a set, they must be. The only way I know to enforce this rule is to assign a single index to the three columns. Is this how I should be doing it?

 

Confused? Here's examples.

 

Okay:

dog_name: Fluffy
kennel_name: River Hills
breeder_name: amanda72

 

dog_name: Fluffy
kennel_name: Rivendell

breeder_name: green_pastures

 

Not Okay:

dog_name: Fluffy
kennel_name: River Hills
breeder_name: amanda72

 

dog_name: Fluffy
kennel_name: River Hills
breeder_name: amanda72

Link to comment
Share on other sites

It's ok and nothing really unusual to do. If it should be unique, add indexes as necassary. DBMS are incredible potent, and you don't really need to focus heavily on performance unless you work with really large data. A good tip is to focus on data integrity first, then focus on performance if things start going slow.

 

The "correct" way here would be to work with IDs rather than names, but unless you need the three tables dogs, kennels and breeders, you don't really need to enforce that. The principle is called normalization, and teaches you how data should be split into the correct table entities.

 

Good luck, Buttercream.

Link to comment
Share on other sites

Hmm. Well I simplified everything for the sake of example. In my real database, it'd be more like this:

 

dogs_table
---------------

-dog_id
-name
-kennel_id
-breeder_id

-bunch of other dog columns like gender (enum, male or female) and altered (enum, yes or no) , basically, if it was something that had a 1 to 1 relationship with a dog, it went here

 

---------

 

Anyhoo, another question. I have another table with a unique index that involves about 15 columns. Is that too much?

 

Why so many columns? Well, the table keeps track of all the possible dog coat color combinations. You see, my image generator combines various layers to come up with all the colors and patterns that a dog can come in. I don't want it making the same variation twice. So it knows what it's made before, I use my dog_colors table to keep track of all the variations and to assign each one a color_id (int, auto_increment). It would be really bad if the same combination was added twice, so I created a unique index to stop this. Just about every column is part of the index. The only exceptions are color_id and color_name.

Link to comment
Share on other sites

I'm mearly guessing here, but I think the index will really only slow down your INSERT / UPDATE queries, but not affect SELECT or DELETE. Slow inserts are often not really problematic — it's the select queries you want to go fast.

 

Another guess would be that SELECT queries will actually be quicker due to your index, as indexes builds binary trees (b-trees) on your data.  It's pretty much boils down to this: How does SELECT queries against the 15 columned uniquely indexed table compared to other queries to other tables? You'll get execution times in PHPMyAdmin.

 

Larry might know more about this than me.

Link to comment
Share on other sites

 Share

×
×
  • Create New...