Howdy all,
I've got a query which selects the count all listings
that meet meet a customer's search criteria. In
addition, there is a sent table that keeps track of
which listings have already been sent to that
customer. There are roughly 30,000 listings and 1.2
million sent records. As is stands right now, the
query can take as long as 15 seconds to run, which
seems like a ridiculously long time.
CREATE TABLE listing (
listing_id int(11) NOT NULL default '0',
mls_id int(11) NOT NULL,
price decimal(11,2) default NULL,
property_type varchar(50) default NULL,
city varchar(30) default NULL,
subdivision varchar(30) default NULL,
area varchar(20) default NULL,
tot_finished_sqft int(11) default NULL,
sqft int(11) default NULL,
bedrooms varchar(20) default NULL,
baths varchar(20) default NULL,
garage_spaces int(11) default NULL,
year smallint default NULL,
PRIMARY KEY (mls_id, listing_id),
KEY idx_price (price),
KEY idx_city (city),
KEY idx_area (area),
INDEX idx_tot_sqft (tot_finished_sqft),
INDEX idx_sqft (sqft),
INDEX idx_mls (mls_id),
FOREIGN KEY (mls_id) REFERENCES mls(mls_id)
) TYPE=INNODB;
CREATE TABLE sent (
customer_id int(11) NOT NULL,
pref_id int(11) NOT NULL,
listing_id int(11) NOT NULL,
mls_id int(11) NOT NULL,
office_id int(11) NOT NULL,
price decimal(11,2) NOT NULL default '0.00',
sent_dt datetime NOT NULL,
PRIMARY KEY (customer_id,pref_id,listing_id,
mls_id,price,sent_dt),
INDEX idx_price (price),
INDEX idx_customer(customer_id),
FOREIGN KEY (customer_id) REFERENCES
customer(customer_id),
INDEX idx_listing(listing_id, mls_id),
INDEX idx_office(office_id),
FOREIGN KEY (office_id) REFERENCES
office(office_id),
INDEX idx_pref(pref_id),
FOREIGN KEY (pref_id) REFERENCES
customer_listing_pref(pref_id),
) TYPE=INNODB;
Here's the query:
SELECT COUNT(listing.listing_id) AS listing_count FROM
listing LEFT JOIN sent ON sent.customer_id = 28080 AND
sent.pref_id = 28483 AND sent.office_id = 12 AND
sent.listing_id = listing.listing_id AND sent.mls_id =
listing.mls_id AND sent.price = listing.price WHERE
sent.listing_id IS NULL AND listing.price <= 250000.0
AND listing.price >= 89000.0 AND city IN ('Arvada')
AND area IN ('JNC','JFN') AND tot_finished_sqft >=
1000 AND baths >= 2.0 AND bedrooms >= 4 AND
garage_spaces >= 0 AND property_type IN ('RES') AND
year <= 32767 AND baths <= 99.0 AND bedrooms <= 99 AND
sqft <= 9999999 AND ns <= 9999999 AND ew <= 9999999;
the explain shows that a range scan is being performed
on the listing table and that sent join type is ref.
The idx_city index is being used to lookup listings.
and the idx_listing index is being used on the sent
table. I wanted to include more of the explain but it
wasn't very readable.
Any help speeding this query up would be greatly
appreciated.
Thanks,
Tripp
__________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
