MySQL optimize query with Date range

Question

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

Please post TEXT results of SHOW CREATE TABLE table; so we can see if you have applied any of the suggestions to this point in time. Someone will help you get high performance. — Wilson Hauck
– Wilson Hauck, Commented Sep 13, 2020 at 20:05
Would you have time to post current TEXT results of SHOW CREATE TABLE table; so we can see what you ended up with for a table with indexes? — Wilson Hauck
– Wilson Hauck, Commented Oct 12, 2020 at 20:17

Tim Biegeleisen · Accepted Answer · 2020-09-10 15:55:04Z

2

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);

answered Sep 10, 2020 at 15:55

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Crash Override Over a year ago

Should I create this new Index and leave the others intact or should I edit/remove them?

Rick James Over a year ago

@CrashOverride - Please provide SHOW CREATE TABLE; we can't guess at what other indexes you have. In general, toss any index that is the left part of another index.

Rick James Over a year ago

Put companyID first because it is tested with"=". Then all the necessary 'rows' of the index are consecutive.

Tim Biegeleisen Over a year ago

@RickJames I also would have suggested the first version under the assumption that companyId would have higher cardinality.

Rick James Over a year ago

Cardinality is not relevant. The structure of the index is.

Collectives™ on Stack Overflow

MySQL optimize query with Date range

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related