CODEWITHSUNDEEP
mysqlsqlquery-performancedatabase-partitioningdatabase-indexes
silence
silence

Reputation: 1

Mysql partition columns (Date) execute plan

That's the structure of the table:

enter image description here

It's November 30, 2017 in Beijing. I want to use partition column make it more effective to get my data,You can see the execute plan between two diffrent sqls:

enter image description here enter image description here

I want to make sure that why the execute plans of both are diffrent? Actually I want to use sql2,but it's two slow.
I'm puzzled by this question.Thanks for answer advance.

Upvotes: 0

Views: 156

Answers (1)

O. Jones
O. Jones

Reputation: 108746

The two queries differ in this way ...

 WHERE datecolumn = CONSTANT

and

 WHERE datecolumn =  DIRTY(GREAT(FUNCTIONAL(expression)))

In the second way of doing this, the MySQL query planner can't tell enough about what datecolumn values to use to let it pick just one partition to search.

Notice that it probably doesn't make sense to use partitioning to enhance query performance unless

  1. You've already done everything you can to use indexes to enhance performance.
  2. Your table contains at least 100 million rows.

Partitioning lets you do things like swap out old data fast. But getting it to improve performance is very hard, and limits the kinds of queries you can do. If your labor is worth any money, spend a tenth of that money on some SSD disks instead.

Upvotes: 1

Related Questions