MySQL table T holds report rows for fewer than 10 companies. A view finds the latest date for each company, and another joins those company and date pairs back to T to get all rows from the latest reports.
The join is slow, but a query with the same pairs written as OR conditions is much faster. T has separate indexes and a composite index on company_id and date. What should I check?
Start with EXPLAIN for both queries rather than assuming the join reads all of T. In the slow plan, the grouped result is read first, but the lookup into T uses PRIMARY with only company_id as the reference. That could mean it reads a lot of rows for each company and filters by date afterward.
The grouped part is already using the company_id and date index for its group-by. The part to investigate is why the join back to T doesn't use both columns of that index. Compare the estimated and, where your MySQL version supports it, actual row counts with EXPLAIN ANALYZE. Refresh table statistics and check whether the choice changes.
Try the join with a FORCE INDEX hint for the composite company_id and date index as a diagnostic. If EXPLAIN shows both columns being used and the query gets faster, that gives you a useful comparison with the optimizer's preferred plan.
Don't assume the hint is a permanent fix, though. Check the actual timing and row counts, since the best choice can depend on the data and MySQL version.
Separate execution time from fetching the results. SELECT * may return a lot of report rows and columns, so client transfer and processing can take time even after the database starts returning rows.
Compare both queries with the same selected columns and make sure they return the same number of rows. If the report is large, selecting only the columns the consumer needs may help regardless of the join plan.
The manual OR query gives the optimizer a small set of known company and date pairs. The join has to calculate those pairs first, so it can end up with a different plan even though the results are equivalent.
If the view remains troublesome after checking the plan, running the latest-date query first and then querying T for those pairs is a possible workaround. Be careful about reports arriving between the two queries. Use a transaction with suitable isolation if both steps need a consistent snapshot.
Answers are reviewed before publication.
c.58+windup_19:
Also check the column order in the indexes. A primary key that contains both columns isn't necessarily useful for a lookup on both if other columns come between them.