I have a large table, T, containing report rows for fewer than 10 companies. Every time a report arrives, I insert its rows with a company_id and datetime value. I use one view to find the newest datetime for each company: SELECT company_id, MAX(date) AS date FROM T GROUP BY company_id. That query is fast. A second view joins T to those results to return every row belonging to each company's latest report: SELECT * FROM T JOIN V1 USING (company_id, date). However, this query takes roughly 4 seconds to execute and another 16 seconds to fetch the results.
If I manually turn the results from V1 into a series of OR conditions matching each company_id and datetime, the same data returns in a fraction of a second. Both columns are indexed, and I also have a composite index on (company_id, date). EXPLAIN shows that the join chooses the PRIMARY index, which contains additional columns, rather than the company_id/date index. Forcing the composite index makes the query fast, but I would prefer a cleaner and maintainable solution. How should I rewrite or index this query so MySQL uses an efficient plan?
3 Answers
The execution plan explains the difference. The derived table containing the maximum date is materialized without a useful index, and the join then chooses the larger PRIMARY index on T. That leads to many more rows being examined than the manually written range query.
Make sure T has a composite index beginning with `(company_id, date)`, ideally in the order needed by the query. Then compare the plans with EXPLAIN. If you are using MySQL 8 or newer, a window-function version is another option:
`SELECT * FROM (SELECT T.*, ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY date DESC) AS rn FROM T) AS x WHERE rn = 1;`
The optimizer may still choose a different plan depending on table statistics, so refreshing statistics and checking the estimated row counts can also help. If the forced index consistently produces the best plan, it may be reasonable to keep the hint, but verify it after major data or version changes.
The important part of the slow plan is that the join uses `PRIMARY` and estimates around 29,000 rows for each company_id lookup. The manual query uses a range scan on the date index and examines far fewer rows. That is why the two queries behave so differently even though they are logically equivalent.
You can try `ANALYZE TABLE T` to refresh index statistics, then rerun EXPLAIN. Also make sure the composite index is defined exactly as `(company_id, date)` and that both sides of the join have matching data types. If the optimizer still chooses the wrong key, an index hint such as `FORCE INDEX (company_id_date)` can be appropriate for this stable workload. Giving the index a simple name also avoids quoting and view-definition problems.
Forcing the `(company_id, date)` index does solve the performance problem. The remaining issue is mainly that altering the view afterward is cumbersome because of how the index hint is stored by the database tools.
If the requirement is to return every row from the newest report, be careful with the window-function example: `ROW_NUMBER()` is appropriate only when one row per company is wanted. Since each report contains multiple rows, the join against `(company_id, MAX(date))` preserves all rows sharing that report timestamp, which matches your goal.
For older MySQL versions, the aggregate-and-join pattern is valid. The main fix is getting the join to use the `(company_id, date)` composite index rather than the wider primary key. Refreshing statistics, simplifying the primary-key layout if practical, or using an index hint are the most direct solutions.
The composite `(company_id, date)` index already exists. EXPLAIN shows that MySQL prefers the PRIMARY index, which has those columns plus two more and performs much worse here. Adding `FORCE INDEX` makes the join fast, although maintaining the view with that hint has been awkward in my database client.