In mathematics, a monotonic function (or monotone function) is a function which preserves the given order. [Wikipedia]
To be more precise, a function f is monotonic increasing, if for every x ≤ y it holds that f(x) ≤ f(y). f is said to be strictly monotonic increasing is for every x < y it holds that f(x) [...]
Ever so often a query provides a “bad” execution plan. Adding a missing index can many times solve the problem. However, not everything can be solved with an index. I wish to highlight the point of having an implicit cast, which negates the use of an index on MySQL.
I see this happening a lot on [...]
Here’s a complication of some common and useful time & date calculations and equations. Some, though very simple, are often misunderstood, leading to inefficient or incorrect implementations.
There are many ways to solve such problems. I’ll present my favorites.
Querying for time difference
Given two timestamps: ts1 (older) and ts2 (newer), how much time has passed between them?
One [...]
I’ve encountered the same problem twice for different customers, so I guess it’s worth a discussion.
A common task for web applications is to find out the country/region of a user, based on her IP address, as can be detected in the HTTP request. Depending on the country of origin, the website can translate dates for [...]
I came today upon a very strange issue. It seems like MySQL is unable to utilize a compound index when evaluating a plan for a query with a range condition. I’m looking for an explanation. I’ll appreciate any insight on this.
InnoDB and MyISAM use B+ and B trees for indexes (InnoDB also has internal hash index).
In both these structures, the depth of the index is an important factor. When looking for an indexed row, a search is made on the index, from root to leaves.
Assuming the index is not in memory, the depth of the [...]
Sometimes MySQL gets it wrong. It doesn’t use the right index.
It happens that MySQL generates a query plan which is really bad (EXPLAIN says it’s going to explore some 10,000,000 rows), when another plan (soon to show how was generated) says: “Sure, I can do that with 100 rows using a key”.
A true story
A customer [...]
A set of useful queries on INFORMATION_SCHEMA follows. These queries can be used when approaching a new database, to learn about some of its properties, or they can be regularly used on an existing schema, so as to verify its integrity.
I will present queries for:
* Checking on database engines and size
* Locating duplicate and redundant indexes
* Checking on character sets for columns and tables, looking for variances
* Checking on processes and long queries
In Part I of this article, we have seen how the internal structure of the storage engine’s index can affect an execution plan. We’ve seen that some plans are inherent to the way engines are implemented.
We wish to present a second scenario in which execution plans vary for different storage engines. Again, we will consider [...]
A popping question is: “Can an execution plan change for different storage engines?”
The answer is “Yes”. I will present two such cases, where the MySQL optimizer will choose different execution plans, based on our choice of storage engine.
We will consider MyISAM and InnoDB, the two most popular engines. The two differ in many respects, and [...]