Problem with indexes ?

[krystoffff]

Hi

I have the following database under MySQL 4.1 :

////////////////////////////////////////////////////
# Table structure for table `affiliate_lockout`
#

CREATE TABLE `affiliate_lockout` (
`member_id` bigint(20) NOT NULL default '0',
`affiliate_locked_id` bigint(20) NOT NULL default '0',
PRIMARY KEY (`member_id`,`affiliate_locked_id`)
) TYPE=MyISAM CHARSET=latin1;

# --------------------------------------------------------

#
# Table structure for table `lead`
#

CREATE TABLE `lead` (
`id` int(11) NOT NULL auto_increment,
`borrower_last_name` varchar(30) default NULL,
`co_borrower_last_name` varchar(30) default NULL,
`borrower_ss_number` varchar(20) default NULL,
`prop_address1` varchar(100) default NULL,
`prop_city` varchar(20) default NULL,
`prop_state` char(3) default NULL,
`prop_zip` varchar(10) default NULL,
`home_phone` varchar(15) default NULL,
`office_phone` varchar(15) default NULL,
`prop_type` varchar(30) default NULL,
`prop_is` varchar(30) default NULL,
`prop_value` double default NULL,
`current_value` double default NULL,
`purchase_price` double default NULL,
`balance` double default NULL,
`first_mortgage_interest_rate` double default NULL,
`first_mortgage_monthly_payment` double default NULL,
`down_payment` double default NULL,
`date_acquired` date default NULL,
`behind` tinyint(4) default NULL,
`first_mortgage_fixed_or_ajustab` varchar(10) default NULL,
`borrower_credit_rating` varchar(20) default NULL,
`co_borrower_credit_rating` varchar(20) default NULL,
`time` varchar(30) default NULL,
`loan_amount` double default NULL,
`loan_type` varchar(50) default NULL,
`email` varchar(60) default NULL,
`information` blob,
`mail_auth` tinyint(4) default '0',
`date_creation` timestamp NOT NULL,
`exclusive` tinyint(4) default NULL,
`prop_address2` varchar(100) default NULL,
`prop_country` varchar(20) default NULL,
`borrower_address1` varchar(100) default NULL,
`borrower_address2` varchar(100) default NULL,
`borrower_zip` varchar(10) default NULL,
`borrower_city` varchar(20) default NULL,
`borrower_state` char(3) default NULL,
`borrower_country` varchar(20) default NULL,
`co_borrower_ss_number` varchar(20) default NULL,
`homeowner` tinyint(4) default '1',
`purchase_date` date default NULL,
`first_mortgage_amount_owed` double default NULL,
`second_mortgage_amount_owed` double default NULL,
`second_mortgage_interest_rate` double default NULL,
`second_mortgage_fixed_or_ajusta` varchar(10) default NULL,
`second_mortgage_monthly_payment` double default NULL,
`borrower_first_name` varchar(30) default NULL,
`borrower_employer` varchar(30) default NULL,
`borrower_employer_nb_years` float default NULL,
`borrower_gross_yearly_income` double default NULL,
`co_borrower_first_name` varchar(30) default NULL,
`co_borrower_employer` varchar(30) default NULL,
`co_borrower_employer_nb_years` float default NULL,
`co_borrower_gross_yearly_income` double default NULL,
`nb_units` float default NULL,
`ip_address` varchar(20) default NULL,
`refinance_date` date default NULL,
`refinance_liens` double default NULL,
`referer` varchar(150) default NULL,
`bad_lead` tinyint(4) default NULL,
`affiliate_id` bigint(20) default NULL,
`nb_purchases` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `date_creation` (`date_creation`),
KEY `exclusive` (`exclusive`),
KEY `nb_purchases` (`nb_purchases`)
) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=8876 ;

# --------------------------------------------------------

#
# Table structure for table `member`
#

CREATE TABLE `member` (
`login` varchar(10) NOT NULL default '',
`website` varchar(15) NOT NULL default '',
`valid` tinyint(4) NOT NULL default '0',
`password` varchar(10) NOT NULL default '',
`balance` double NOT NULL default '0',
`first_name` varchar(20) default NULL,
`last_name` varchar(20) default NULL,
`address1` varchar(100) default NULL,
`city` varchar(20) default NULL,
`state` char(3) default NULL,
`office_phone` varchar(15) default NULL,
`other_phone` varchar(15) default NULL,
`fax` varchar(15) default NULL,
`email` varchar(60) default NULL,
`zip` varchar(10) default NULL,
`loan_types` blob,
`property_types` varchar(100) default NULL,
`credit_ratings` varchar(50) default NULL,
`states` varchar(200) default NULL,
`ss_number` varchar(20) default NULL,
`mail_auth` tinyint(4) default '0',
`address2` varchar(100) default NULL,
`country` varchar(20) default NULL,
`company_website` varchar(60) default NULL,
`id` bigint(20) NOT NULL default '0',
`date_subscription` timestamp NOT NULL,
`company_name` varchar(50) default NULL,
`balance_in_points` double default NULL,
PRIMARY KEY (`id`),
KEY `balance_in_points` (`balance_in_points`)
) TYPE=MyISAM CHARSET=latin1;

# --------------------------------------------------------

#
# Table structure for table `member_exclusion`
#

CREATE TABLE `member_exclusion` (
`member_id` bigint(20) NOT NULL default '0',
`member_id_to_exclude` bigint(20) NOT NULL default '0',
PRIMARY KEY (`member_id`,`member_id_to_exclude`)
) TYPE=MyISAM CHARSET=latin1;

# --------------------------------------------------------

#
# Table structure for table `purchase`
#

CREATE TABLE `purchase` (
`lead_id` int(11) NOT NULL default '0',
`date` timestamp NOT NULL,
`automated` tinyint(4) default '0',
`member_id` bigint(20) NOT NULL default '0',
`email_sent` tinyint(4) default '0',
`shared_or_exclusive` varchar(10) default NULL,
PRIMARY KEY (`lead_id`,`member_id`),
KEY `member_id_purchase_key` (`member_id`),
KEY `lead_id` (`lead_id`)
) TYPE=MyISAM CHARSET=latin1;

////////////////////////////////////////////////////////////
I have to run the following query (maybe it can be optimized, but I
really don't know how ... I sent another post to see if somebody could
optimize it) which is very very slow to run (about 30s).

//////////////////////////////////////////////////////////

SELECT *
FROM lead
WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( date_creation ) <= (
6 * 24 * 3600 ) AND
(exclusive IS NULL OR (exclusive = 0 AND nb_purchases < 3)) AND

id NOT IN (

SELECT lead_id
FROM purchase
WHERE member_id = 21101
) AND affiliate_id NOT
IN (

SELECT affiliate_locked_id
FROM affiliate_lockout
WHERE member_id = 21101
) AND id NOT
IN (

SELECT lead_id
FROM purchase
INNER JOIN member_exclusion
WHERE purchase.member_id = member_exclusion.member_id_to_exclude AND
purchase.member_id = 21101
)

////////////////////////////////////////////////////////

So I did an explain of this query, and I have the following result:

id select_type table type possible_keys
key key_len ref rows Extra
1 PRIMARY lead ALL exclusive,nb_purchases
NULL NULL NULL 4490 Using where
4 DEPENDENT SUBSELECT member_exclusion index NULL
PRIMARY 16 NULL 3 Using where; Using index
4 DEPENDENT SUBSELECT purchase index member_id_purchase_key
PRIMARY 12 NULL 11022 Using where; Using index
3 DEPENDENT SUBSELECT affiliate_lockout ref PRIMARY
PRIMARY 8 const 2 Using where; Using index
2 DEPENDENT SUBSELECT purchase index member_id_purchase_key
PRIMARY 12 NULL 11022 Using where; Using index
According to the documentation, it is a bad result, but I don't know
how to modify th database to get better (what index to add, ...)

Could somebody help me ? I really need to get a better result for my
work ...
krystoffff