Hi All, Let's say I'm creating an affiliate program to track surfer clicks to sales. I have a question on indexing that's always bothered me. I'm hoping an index guru can help me out. Here's an example click-recording table:
CREATE TABLE clicks ( clickID int unsigned NOT NULL auto_increment, initDate datetime NOT NULL default '0000-00-00 00:00:00', siteID int unsigned NOT NULL default '0', ipAddress int NOT NULL default '0', webmasterID int NOT NULL default '0', optInfo varchar(16) NOT NULL default '', referer varchar(255) NOT NULL default '', PRIMARY KEY (clickID) ) TYPE=MyISAM; I'll set a cookie in the surfer's browser with clickID, so I can match clicks to sales at a later point. siteID is the destination site for the click, webmasterID is the referring webmaster who sent the click (may be blank, signified by '0'). In the reports I want to provide, I want to show how many clicks each site receives over certain time frames and also how many clicks each referring webmaster sent (over a time frame). My question is: would it be better to have a single wide index, like the following: KEY ix_clicks_initDate (initDate,siteID,webmasterID) or multiple indexes: KEY ix_clicks_site (initDate,siteID) KEY ix_clicks_webm (initDate,webmasterID) If I'm searching a date range looking for a particular webmasterID, or grouping by webmasterID's, is the wide index a good choice in this circumstance -- where the middle field of the index isn't referenced in the query? Or does it hurt, because I'm not using all the pieces of the index? I've tried wide indexes like this in the past for reporting purposes, but have always had nagging questions about their use. This is a simple example, but it applies to decisions I need to make about more complex indexing and whether to use wide indexes or not. Can somebody shed some light? Thanks, --jeff --------------------------------------------------------------------- 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
