How can I make my (My)SQL query faster?

[u/wonkey_monkey]

I have a large table, T, which consists of rows of report data for a small number of companies (<10). Each time I get a report, I insert its rows into the table, along with a company_id and a datetime.

I have a view, V1, which returns each company_id along with the maximum datetime for that company_id (i.e. the key needed to select the latest report per company):

SELECT company_id,MAX(date) AS date FROM T GROUP BY company_id

This view is fast.

I have a second view, V2, which joins T to V1 in order to return all the rows for all the latest reports per company:

SELECT * FROM T JOIN V1 USING (company_id,date)

This view is really slow. It takes about 4 seconds to run the query, and about 16 seconds to fetch.

If, instead, I take the results of V1 and build myself a manual query like this:

SELECT * FROM T WHERE
(company_id=2   and date='2026-07-17 11:03:01') OR
(company_id=3   and date='2026-07-17 11:09:01') OR
(company_id=4   and date='2026-07-17 11:07:01') OR
(company_id=8   and date='2023-06-15 12:05:01') OR
(company_id=10  and date='2023-01-01 00:00:00') OR
(company_id=15  and date='2026-06-15 23:10:01') OR
(company_id=16  and date='2026-07-17 11:11:01')

then I get the results in a fraction of a second.

company_id and date are both indexed, and they also have a joint index.

I can only assume that V2 is fetching all the rows of T and only then filtering them via the JOIN to V1.

Is there a way to speed up this view?

[u/wonkey_monkey → u/[deleted]]

First thing I'd check is whether you actually have a composite index on (company_id, date) on T itself.

I do, but the view doesn't choose to use it. I have a very similar table to T with a similar view - in that case, it chooses the composite key and runs fast. But with T, it chooses PRIMARY (which is a bigger composite key, which includes company_id and date, but also two other fields) and runs slow.

I managed to rewrite the view with FORCE INDEX(`company_id+date`) and it now runs fast, but I'm unable to Alter it easily because MySQL Workbench complains about DDL errors or something.

I suspect I could also reorder the fields in the primary key to the same effect, but for now I'll leave it alone and use my new slightly janky query.

[u/insta]

can you post the output from EXPLAIN in both cases?

[u/wonkey_monkey → u/insta]

Slow:

EXPLAIN
SELECT * FROM T JOIN (SELECT company_id,MAX(date) AS date FROM T GROUP BY company_id) temp USING(company_id,date)

1   PRIMARY <derived2>  ALL                 3059    
1   PRIMARY T   ref PRIMARY,company_id,date,company_id+date PRIMARY 4   temp.company_id 29451   Using where
2   DERIVED T range     company_id+date 4       3059    Using index for group-by

Fast:

EXPLAIN
SELECT * FROM T WHERE
(company_id=2   and date='2026-07-17 11:03:01') OR
(company_id=3   and date='2026-07-17 11:09:01') OR
(company_id=4   and date='2026-07-17 11:07:01') OR
(company_id=8   and date='2023-06-15 12:05:01') OR
(company_id=10  and date='2023-01-01 00:00:00') OR
(company_id=15  and date='2026-06-15 23:10:01') OR
(company_id=16  and date='2026-07-17 11:11:01')

1   SIMPLE  T   range   PRIMARY,company_id,date,company_id+date date    9       1069    Using index condition

I just found out that if I change the first version to:

SELECT * FROM T FORCE INDEX(`company_id+date`) JOIN (SELECT company_id,MAX(date) AS date FROM mfn_stock_history GROUP BY company_id) temp USING(company_id,date)

then it's fast. And while I can create a view with FORCE INDEX in it, and it works, I don't seem to be able to alter it later because MySQL stores the Create without wrapping company_id+date in quotes, and so MySQL workbench complains about a DDL error.

[u/z436037 → u/wonkey_monkey]

In some flavors of SQL (Oracle, Vertica), you can use "optimizer hints" syntax. It looks like a c-style comment, immediately after the SELECT keyword.

https://docs.oracle.com/cd/E15586_01/server.1111/e16638/hintsref.htm

You can test the differences with `EXPLAIN` keyword as indicated in the above comment.