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]
