I specifically wrote my import script to parse out the fields that we need and add NULLs. I was told that having NULL values was faster than using ' '. If this isn't the case, I'd like to know.
Thanks. -----Original Message----- From: Kurt Hansen [mailto:[EMAIL PROTECTED] Sent: Monday, February 23, 2004 11:42 AM To: Chris Fossenier; [EMAIL PROTECTED] Subject: Re: MySQL versus MS SQL Hi Chris, Chris Fossenier wrote: > This is a long post, my apologies. Speaking for myself, I found the detail most helpful. Thanks! See response at bottom. ...snip... > QUERY1 > ~~~~~~~~~~~ > Indexed Fields (link, phone_pander,state, exact_age, estimated_age, > phone, first, last, address) MS SQL QUERY (TIME TO COMPLETE: 21m33s) ...snip... > MySQL QUERY(TIME TO COMPLETE: 42m27s) > SELECT > a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, > state.state, a2.zip, a1.plus4, a3.county, > a1.phone_pander_flag, h1.homeowner, h2.probable_homeowner, > h2.homeowner_probability_model, p1c.exact_age, p1c.estimated_age > FROM a1 > INNER JOIN a2 on a2.link = a1.link > INNER JOIN a3 on a3.link = a1.link > INNER JOIN h1 on h1.link = a1.link > INNER JOIN h2 on h2.link = a1.link > INNER JOIN p1a on p1a.link = a1.link > INNER JOIN p1c on p1c.link = a1.link > INNER JOIN state on state.state_code = a2.state_code > WHERE > a1.phone_pander_flag <> 'Y' > AND state.state = 'PA' > AND ( h1.homeowner = 'Y' > OR h2.probable_homeowner IN ('8','9') > OR h2.homeowner_probability_model BETWEEN '080' AND '102' ) > AND ( p1c.exact_age BETWEEN '40' AND '60' > OR estimated_age BETWEEN '40' AND '60' ) > AND a1.phone is not null > AND p1a.first is not null > AND p1a.last is not null > AND a3.address is not null; > > QUERY2 > ~~~~~~~~ > Indexed Fields (link, zip, phone_pander,phone, first, last, address) > MS SQL QUERY(12 seconds) ...snip... > MySQL QUERY(10min51secs) > SELECT > a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, > state.state, a2.zip, a1.plus4, a3.county, a1.phone_pander_flag, > h1.homeowner, > h2.probable_homeowner, h2.homeowner_probability_model, > a1.number_of_units, p1a.primary_ethnic_market_code, > c2k.2000_census_hispanic > FROM a1 > INNER JOIN a2 on a2.link = a1.link > INNER JOIN a3 on a3.link = a1.link > INNER JOIN h1 on h1.link = a1.link > INNER JOIN h2 on h2.link = a1.link > INNER JOIN p1a on p1a.link = a1.link > INNER JOIN c2k on c2k.link = a1.link > INNER JOIN state on state.state_code = a2.state_code > WHERE > a2.zip in ('60402', '60650', '60607', '60608', '60612', '60622') > AND a1.phone_pander_flag <> 'Y' > AND ( h1.homeowner='Y' > OR h2.probable_homeowner in ('8','9') > OR h2.homeowner_probability_model BETWEEN '080' AND '102') > AND a1.phone is not null > AND p1a.first is not null > AND p1a.last is not null > AND a3.address is not null > AND a1.number_of_units IN ('0001','0002') > AND ( p1a.primary_ethnic_market_code='38' > OR c2k.2000_census_hispanic >='0850' ); > > > QUERY3 > ~~~~~~~~~ > Indexed Fields (link, zip, phone_pander, phone, first, last, address) > MS SQL QUERY(10seconds) ...snip... > MySQL QUERY(5min22secs) > SELECT > a1.phone, p1a.first, p1a.last, a3.address, a1.address2, a1.city, > state.state, a2.zip, a1.plus4, a3.county, h1.home_purchase_date, > mtg.mortgage_amount_in_thousands, a1.phone_pander_flag > FROM a1 > INNER JOIN a2 on a2.link = a1.link > INNER JOIN a3 on a3.link = a1.link > INNER JOIN h1 on h1.link = a1.link > INNER JOIN p1a on p1a.link = a1.link > INNER JOIN mtg on mtg.link = a1.link > INNER JOIN state on state.state_code = a2.state_code > WHERE > a2.zip IN ('14221', '14224', '14226', '14227', '14051', '14031', '14057') > AND a1.phone_pander_flag <> 'Y' > AND a1.phone is not null > AND p1a.first is not null > AND p1a.last is not null > AND a3.address is not null > AND mtg.mortgage_amount_in_thousands>='0075' > AND (h1.home_purchase_date > '2003' and h1.home_purchase_date < '2004'); > > > QUERY4 > ~~~~~~~~~~ > Indexed Fields (link, state, first, last, address) ...snip... > MySQL QUERY(2hrs17mins) > SELECT > a1.phone, p1a.first, p1a.last, a3.address, a1.address2, > a1.city, state.state, a2.zip, a1.plus4, a3.county, > mtg.mortgage_loan_type, mtg.mortgage_amount_in_thousands, > h1.home_purchase_price, h1.home_purchase_date > FROM a1 > INNER JOIN a2 on a2.link = a1.link > INNER JOIN a3 on a3.link = a1.link > INNER JOIN h1 on h1.link = a1.link > INNER JOIN p1a on p1a.link = a1.link > INNER JOIN mtg on mtg.link = a1.link > INNER JOIN state on state.state_code = a2.state_code > WHERE > state.state IN ('PA', 'OH') > AND p1a.first is not null > AND p1a.last is not null > AND a3.address is not null > AND mtg.mortgage_loan_type = 'V' > AND (mtg.mortgage_amount_in_thousands>='0100' OR > h1.home_purchase_price>='0100000') > AND h1.home_purchase_date between '2002' and '2003'; > > QUERY5 > ~~~~~~~~~~ > Indexed Fields (link, state, first, last, address) > MS SQL QUERY(27min28secs) ...snip... > MySQL QUERY(3hrs53mins) > SELECT > a1.phone, p1a.first, p1a.last, a3.address, a1.address2, > a1.city, state.state, a2.zip, a1.plus4, a3.county, > a1.phone_pander_flag, a1.number_of_units, h1.dwelling_type, > h2.homeowner_probability_model, h1.home_property_indicator > FROM a1 > INNER JOIN a2 on a2.link = a1.link > INNER JOIN a3 on a3.link = a1.link > INNER JOIN h1 on h1.link = a1.link > INNER JOIN h2 on h2.link = a1.link > INNER JOIN p1a on p1a.link = a1.link > INNER JOIN mtg on mtg.link = a1.link > INNER JOIN state on state.state_code = a2.state_code > WHERE > state.state = 'FL' > AND h2.homeowner_probability_model <= '020' > AND p1a.first is not null > AND p1a.last is not null > AND a3.address is not null > AND ( a1.number_of_units>='0003' > OR h1.dwelling_type='A' ) > AND h1.home_property_indicator <> '2'; Is it the "not null"s? I remember reading that having nulls are not good for speed or storage. It's better to have a defined empty value. This memory may be faulty, and I don't remember why this is so. I do remember begin confused by this advice. Probably why it stuck. :-) If more knowledgeable people contradict my advice, trust them. Take care, Kurt Hansen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
