Problem with indexes ?

[krystoffff]

Hi

I have the following database under MySQL 4.1 :

////////////////////////////////////////////////////
# Table structure for table `affiliate_lock out`
#

CREATE TABLE `affiliate_lock out` (
`member_id` bigint(20) NOT NULL default '0',
`affiliate_lock ed_id` bigint(20) NOT NULL default '0',
PRIMARY KEY (`member_id`,`a ffiliate_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_la st_name` varchar(30) default NULL,
`borrower_ss_nu mber` 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_paymen t` double default NULL,
`down_payment` double default NULL,
`date_acquired` date default NULL,
`behind` tinyint(4) default NULL,
`first_mortgage _fixed_or_ajust ab` varchar(10) default NULL,
`borrower_credi t_rating` varchar(20) default NULL,
`co_borrower_cr edit_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_addre ss1` varchar(100) default NULL,
`borrower_addre ss2` varchar(100) default NULL,
`borrower_zip` varchar(10) default NULL,
`borrower_city` varchar(20) default NULL,
`borrower_state ` char(3) default NULL,
`borrower_count ry` 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_mortgag e_amount_owed` double default NULL,
`second_mortgag e_interest_rate ` double default NULL,
`second_mortgag e_fixed_or_ajus ta` varchar(10) default NULL,
`second_mortgag e_monthly_payme nt` double default NULL,
`borrower_first _name` varchar(30) default NULL,
`borrower_emplo yer` varchar(30) default NULL,
`borrower_emplo yer_nb_years` float default NULL,
`borrower_gross _yearly_income` double default NULL,
`co_borrower_fi rst_name` varchar(30) default NULL,
`co_borrower_em ployer` varchar(30) default NULL,
`co_borrower_em ployer_nb_years ` float default NULL,
`co_borrower_gr oss_yearly_inco me` double default NULL,
`nb_units` float default NULL,
`ip_address` varchar(20) default NULL,
`refinance_date ` date default NULL,
`refinance_lien s` 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_websit e` varchar(60) default NULL,
`id` bigint(20) NOT NULL default '0',
`date_subscript ion` timestamp NOT NULL,
`company_name` varchar(50) default NULL,
`balance_in_poi nts` double default NULL,
PRIMARY KEY (`id`),
KEY `balance_in_poi nts` (`balance_in_po ints`)
) TYPE=MyISAM CHARSET=latin1;

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

#
# Table structure for table `member_exclusi on`
#

CREATE TABLE `member_exclusi on` (
`member_id` bigint(20) NOT NULL default '0',
`member_id_to_e xclude` bigint(20) NOT NULL default '0',
PRIMARY KEY (`member_id`,`m ember_id_to_exc lude`)
) 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_excl usive` varchar(10) default NULL,
PRIMARY KEY (`lead_id`,`mem ber_id`),
KEY `member_id_purc hase_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_locke d_id
FROM affiliate_locko ut
WHERE member_id = 21101
) AND id NOT
IN (

SELECT lead_id
FROM purchase
INNER JOIN member_exclusio n
WHERE purchase.member _id = member_exclusio n.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_pu rchases
NULL NULL NULL 4490 Using where
4 DEPENDENT SUBSELECT member_exclusio n index NULL
PRIMARY 16 NULL 3 Using where; Using index
4 DEPENDENT SUBSELECT purchase index member_id_purch ase_key
PRIMARY 12 NULL 11022 Using where; Using index
3 DEPENDENT SUBSELECT affiliate_locko ut ref PRIMARY
PRIMARY 8 const 2 Using where; Using index
2 DEPENDENT SUBSELECT purchase index member_id_purch ase_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