I am going to have a table called mail_queue, that is going to store the member id, the mailing id, and the date. Each time a new mailing is setup (usually daily), another 100,000 rows (one for each member) will be added to the table.
When a member reads the mailing, that row will be removed from the table. This is the most normalized way to set this up, and allows me to: *) Make sure members don't get credit for reading the same mailing twice *) Not allow members who aren't allowed credit for a specific mailing don't get credited *) See how many people read each mailing (by determining how many mailings were sent, and subtract the number of rows left in the table for that mailing id) My question is about indexing. I know a lot about indexing, and using left-most indexes, etc...since this table will have millions of rows, I'm wondering what kind of index I should use? I for sure need an index on the mail_id field (the primary key will be between member_id and mail_id), so an index of mail_id AND member_id makes sense. This index would work for queries like: WHERE mail_id = XXX WHERE mail_id = XXX AND member_id = YYY However, that leaves out queries like: WHERE member_id = YYY That is also a query I need to be able to do on a regular basis, and if this query yields 20,000 results, with 5 million rows in the table, it's going to take forever (I assume). So I was thinking about making two indexes: mail_id AND member_id member_id Would this take up way too much disk space? Sincerely, Daren Cotter CEO, InboxDollars.com [EMAIL PROTECTED] http://www.inboxdollars.com (507) 382-0435 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
