CODEWITHSUNDEEP
mysqlselectoptimization
Crash Override
Crash Override

Reputation: 461

MySQL optimize query with Date range

I have the following table structure.

id (INT) index
date (TIMESTAMP) index
companyId (INT) index

This is the problem I am facing

companyId 111: hasta a total of 100000 rows in a 1 year time period.

companyId 222: has a total of 8000 rows in a 1 year time period.

If companyId 111 has 100 rows between '2020-09-01 00:00:00' AND '2020-09-06 23:59:59' and companyId 222 has 2000 rows in the same date range, companyId 111 is much slower than 222 even if it has less rows in the selected date range.

Shouldn't MySQL ignore all the rows outside the date range so the query becomes faster?

This is a query example I am using:

SELECT columns FROM table WHERE date BETWEEN '2020-09-01 00:00:00' AND '2020-09-06 23:59:59' AND companyId = 111;

Thank you

Upvotes: 1

Views: 405

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

I would suggest a composite index here:

CREATE INDEX idx ON yourTable (companyId, date);

The problem with your premise is that, while you have an index on each column, you don't have any indices completely covering the WHERE clause of your example query. As a result, MySQL might even choose to not use any of your indices. You can also try reversing the order of the index above to compare performance:

CREATE INDEX idx ON yourTable (date, companyId);

Upvotes: 2

Related Questions