Page 1 of 1

MySQL table with no index

Posted: Fri May 18, 2007 12:31 pm
by fgomez
This is a quickie: Is there a reason to avoid creating tables with no index? I guess I could throw in an auto-increment column but I don't really have a need for it...

Thanks!

Posted: Fri May 18, 2007 12:38 pm
by volka
Sounds like you don't need a database ;)

Posted: Fri May 18, 2007 12:47 pm
by fgomez
Maybe I should be more specific :wink:

I'm really using it just to count votes. The client doesn't want visitors to have to register before voting, so cheating will be checked (as best as possible, anyway) by cookies. There are only three or four columns and because of the nature of the data there will be duplicates.

I use phpMyAdmin and it's giving me this ugly "Warning: No index defined!"... which is why I ask.

Posted: Fri May 18, 2007 12:51 pm
by feyd
Without an index searching in the information will be rather slow, compared to with an index.. relatively speaking. What is the composition of the records in this table? Will there not be more than one vote, ever?

Posted: Fri May 18, 2007 12:59 pm
by fgomez
There will be more than one vote cast, but basically it's just a holding place for the votes until the contest is over, then I'll come in via phpMyAdmin and do a count() query... I've only ever really used indexes (or indices?) as a primary key to prevent duplicates, but as there will be duplicates by the nature of the data, I didn't see the point. Seems I'm being told that I'm selling the index short by using it exclusively as a dup-preventer...

Posted: Fri May 18, 2007 1:03 pm
by fgomez
The composition of the records in the table? Well, basically the structure is this:

item_id (the item being voted on)
favorite (the items belong to different categories -- say shoes, shirts, and pants -- and this boolean indicates their favorite item overall)
year (this vote will be done again next year...)
email (optional, to enter a lottery)

Posted: Fri May 18, 2007 1:23 pm
by fgomez
Maybe given this information someone could suggest an index?

Thanks again.

Posted: Fri May 18, 2007 1:29 pm
by bdlang
`item_id` - should be PRIMARY KEY
`email` - I'd suggest using UNIQUE on this field to keep one vote per email (unless you don't care)


The way you want to find and define a column index is through the query itself, and using EXPLAIN on the query to find spots for improvement. Basic rule of thumb - if a column is being used in a JOIN or WHERE clause, index it.

Posted: Fri May 18, 2007 1:32 pm
by Benjamin
item_id should NOT be a primary key as there will be duplicates. I'd probably index it however.

Posted: Fri May 18, 2007 1:35 pm
by fgomez
bdlang wrote:`item_id` - should be PRIMARY KEY
`email` - I'd suggest using UNIQUE on this field to keep one vote per email (unless you don't care)


The way you want to find and define a column index is through the query itself, and using EXPLAIN on the query to find spots for improvement. Basic rule of thumb - if a column is being used in a JOIN or WHERE clause, index it.
Thanks, bdlang! Very useful rules of thumb -- seems I have something new to read up on. Question, though: doesn't the primary index prevent duplicate entries? So if "red shoe" is item #054, then "red shoe" could only ever get one vote, I think...

Posted: Fri May 18, 2007 1:47 pm
by bdlang
astions wrote:item_id should NOT be a primary key as there will be duplicates. I'd probably index it however.
Hmm. I missed that, I didn't read the thread thoroughly enough. Yes I would do a standard INDEX on it then. Makes me want to look at the application more closely and redesign.

Posted: Fri May 18, 2007 2:03 pm
by fgomez
Ha! Well, I'm just getting started, and I suppose things could be a little neater, but this is supposed to be a quickie... Maybe it would make more sense for the table to look like this:

shoe | shirt | pants | favorite | year | email

... where `favorite` is the value of the shoe, shirt, or pant (whichever they like best, regardless of ), respectively... We wanted to allow people to skip sections (like if you're just not a pants man) and vote for the other categories, but I guess we should always just go NULL for that...

If nothing else it offers the advantage that all three votes get counted with just one insert rather than three...

Posted: Fri May 18, 2007 2:29 pm
by califdon
Let me try to address the original question of whether a primary index is necessary, or why MySQL gives you a warning if you don't declare one.

Relational database theory is based on mathematical relations. If you have more than one table and there are relationships between tables, it is essential that there is a way to uniquely identify records--that is, that there can never be ambiguity about which record something refers to. This is the role of the primary key, and the database engine enforces this uniqueness by creating an index on the primary key and not allowing any duplicates.

If you have relations among tables, you must have primary keys (and therefore indexes) in at least some of the tables--namely, the table(s) on the one side of any one-to-many relationship.

Indexes on other fields are important if you are going to do searching or reporting, especially if the number of records is large. This is entirely separate from primary key indexes.

If you only have one table and it's relatively small, you don't really need a primary key. But good habits are important, too, so I always have a primary key in every table. Then I won't forget to include one when it is really needed.