Re: Slow Query stuck on "Sending Data"

[Rick James]

The bad part now is much harder to solve -- you are filtering on two tables that are JOINed together -- addresses (city) and listings (category). The best the optimizer can do is gather a lot of rows from one table (filtering on what it can), repeatedly reach over into the other table (filtering the rest of the way), sort (ORDER BY), and deliver 10 (LIMIT).

What % of listings has category = 1? For the rest of this discussion, I will assume it is a small percentage. `visible`=1 is a worse nuisance, since it is probably 1 for 95% of the rows.

Not all hope is lost...

The first step is to get all the filtering into one table. `listings` seems to be the right table. I'm going to optimize the SELECT that restricts to one category in one city. It would help to have a city_id in `listings`. This id would point to the normalized table I mentioned, having the city, state, region (but not street address, etc). listings would need another pointer to the rest of address stuff. Messy, yes. But now the WHERE clause says only
listings.city_id = ... AND
listings.visible = 1 AND
listings.category = 1
How these indexes would be good:
INDEX(category, city_id, visible)
INDEX(city_id, category, visible)
(I included both, in case you have some variations in the WHERE clause, such that one or the other might not be useful.)

To further flesh it out...
FROM listings AS l
JOIN cities AS c ON l.city_id = c.city_id
AND c.city = 'Toronto'
AND c.state = 'Ontario'
AND c.country = 'Canada'
WHERE
listings.visible = 1 AND
listings.category = 1

With that, it will do a quick lookup in cities to get city_id, then use either of those indexes to find the set of rows in listings, including filtering by both location and category (and visible). `cities` should have
INDEX(country, state, city)

Now for the coup de grâce. The optimizer with do all the "=" things it can, then do _one_ other thing. That one thing can be a range (eg, BETWEEN..AND..), or GROUP BY, or ORDER BY. If the optimizer gets through the ORDER BY, then the LIMIT can be cut short!

So, let's change the indexes to
INDEX(category, city_id, visible, feature)
INDEX(city_id, category, visible, feature)
(They should work equally well for _this_ query.)
Now the 23518 "rows examined" become only 10 (the LIMIT).
(Actually 11, because we had to hit one row in `cities`.)