CODEWITHSUNDEEP
mysqldatabaseperformanceoptimizationquery-optimization
user642328
user642328

Reputation: 21

MySQL's Query Optimizer's take on disk access time

I don't have a ton of background with MySQL, and I was wondering of anyone can give me some insight into MySQL's query optimizer and the impact on the cost of disk accesses in determining a query plan for query execution. I'm interested in whether any statistics collected on disk access times can play an impact on the query execution plan for a fixed set of queries. In particular, when running the same set of queries on the same database image that resides on different drives with varying performance. (note that from MySQL's point of view, this is the same database; the data directory simply resides on different drives that get switched "under" MySQL without it knowing). Could this change in observed disk performance potentially impact the query plan decisions the query optimizer makes at runtime? I expect that there are many other things more related to SQL itself that the optimizer could do before taking disk accesses into account, but some of you have a lot more experience in dealing with the query optimizer than me.

Thanks for the help!

Upvotes: 2

Views: 554

Answers (1)

Mike Lue
Mike Lue

Reputation: 839

I don't know about the optimization for pyhsical I/O access in MySQL. The query optimizer, however, would try to minimize the requisite I/Os(say, the number of blocks) on which queries execute.

The gap of speed between memory and secondary I/O system is so huge that keeping effective cache in memory is the key issue of performance in most of relational database systems.

The query plan, therefore, should keep the requisite I/Os as less as possible for a query however quick the device is.

<Query Result> <--[Logical I/O]-- <Main Memory> <--[Physical I/O]-- <Secondary I/O System>

Upvotes: 1

Related Questions