MySQL table with no index
Moderator: General Moderators
MySQL table with no index
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!
Thanks!
Maybe I should be more specific
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.
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.
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...
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)
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)
`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.
`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...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.
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...
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...
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.
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.