At 8:17 AM -0500 4/12/11, Allen Shaw wrote:
On 04/12/2011 06:03 AM, tedd wrote:
In a simple purchase table that contains only the customer-id and
purchased item-id would it be better to have an index of not?
1) If you're going to be searching or joining based on either of
those columns (and you probably will be joining, at least), indexing
the column will improve performance.
2) Having a unique ID per row will likely lessen your headaches down
the road when it comes to deleting and/or updating records, and
distinguishing between similar-but-separate records. Adding that ID
column is worth it.
Allen Shaw
AND
At 7:32 AM -0400 4/12/11, Rob Marscher wrote:
I'm going with better to have the id column and maybe a datetime
column too. What if the person purchases the same item twice?
If the customer and item combination were unique, then I'd say skip
the id column and make "primary key (Customer_ID, Item_ID)" and
probably add an extra "index idx_item (Item_ID)" for fast lookups on
items as well.
Normally I add an indexed ID to tables like that, but recently
someone asked me "Why the ID?" and I really wasn't sure if my habit
actually improved things over the expense of having another field.
So, I just wanted to confirm my thoughts.
Adding an unique auto-increment ID (index) plus a datetime field
(column) makes sense.
Thanks gentlemen.
tedd
--
-------
http://sperling.com/
_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
