Jim, here's an example query:
SELECT COUNT(listing.listing_id) AS listing_count FROM
listing_city_xref INNER JOIN listing
ON listing.listing_id = listing_city_xref.listing_id
AND listing.group_id = listing_city_xref.group_id
WHERE listing_city_xref.group_id =2
AND listing_city_xref.city_id IN (83,88) AND
listing.price BETWEEN 189000.00 AND 230000.00
AND tot_finished_sqft >= 1100 AND sqft >= 1000 AND
baths >= 1 AND bedrooms >= 3 AND garage_spaces >= 1
AND property_type IN ('RES'));
I created an index on the following fields in the
listing table:
group_id, price, tot_finished_sqft, baths, bedrooms,
garage_spaces, property_type.
Here's the result of the explain on the query above:
| 1 | SIMPLE | listing | ref |
PRIMARY, idx_search | idx_search | 4 | const | 8708 |
Using where |
| 1 | SIMPLE | listing_wiz_city_xref | ref |
PRIMARY, idx_listing, idx_wiz_city | PRIMARY | 36|
const, ahf_test.listing.listing_id | 1 | Using
where; Using index |
This query took 12.91 seconds. Running it multiple
times produces similar run times.
Now, if I remove the sqft >= 1100 condition from the
where clause I get the following explain output:
| 1 | SIMPLE | listing | range | PRIMARY,
idx_search | idx_search | 15 | NULL | 8688 |
Using where; Using index |
| 1 | SIMPLE | listing_wiz_city_xref | ref |
PRIMARY, idx_listing, idx_wiz_city | PRIMARY | 36
| const, ahf_test.listing.listing_id | 1 | Using
where; Using index |
The query executes in 0.09 seconds. Running it
multiple times produces similar runtimes.
One interesting difference between the explain outputs
is that in the second case the "ref" is NULL. In the
first scenario it is const.
Why would adding extra fields to the query slow the
query down so much? In either case it looks like it's
using the same index.
Thanks,
Tripp
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
