type - defines how the tables are accessed / joined.In the screenshot above, you can see 'v' and 'p' mentioned, as those are the aliases defined for the tables votes and posts. table - the table name, or alias, this row refers to. ![]() More information about access_types can be found in MySQL's docs. In more complex cases, it will contain other types such as SUBQUERY (for subqueries), UNION (second or later statements in a union), DERIVED (a derived table) and others. In our case, it's a simple query as it contains no subqueries or unions. select_type - The type of SELECT query.The query above contains no subqueries nor unions, so therefore the id for both rows is 1, as there is actually only 1 query. id - In most cases, the id field will present a sequential number of the SELECT query this row belongs to.MySQL chooses the table it thinks will be best to start the journey with (the outer "loop") and then touches the next table using the values from the outer "loop".Įach of the rows in the EXPLAIN contains the following fields: As an analogy to the coding world, you can look at the concept of an inner join as very similar to a nested loop. Each of these tables gets represented in a different row in the execution plan above. The query we're analyzing involves two tables in the process, which are joined using an inner join. The first thing we notice, is that it can include more than one row. This is the original EXPLAIN plan for this query:īefore rushing to optimize the query, let's take a closer look at the output of the EXPLAIN command, to make sure we fully understand all aspects of it. I stopped waiting and cancelled the execution after more than a minute has passed. ![]() The original query's execution duration is very long. For the sake of this example, my user id is 12345678. The following SQL query will find the details of users who added my StackOverflow questions to their favorites list. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `votes` ( `Title` varchar(250) CHARACTER SET utf8 DEFAULT NULL, `Body` longtext CHARACTER SET utf8 NOT NULL, ![]() The following is the structure of the two tables used by this example query ( posts and votes): CREATE TABLE `posts` ( Each post will walk you through a more advanced SQL query than the previous post, while demonstrating more insights which can be obtained from MySQL's execution plans. This is the first article in a series of posts. We'll first analyze the original query, then attempt to optimize the query and look into the optimized query's execution plan to see what changed and why. EXPLAIN can work with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. The EXPLAIN command provides information about how MySQL executes queries. So in this post, I chose to demonstrate how to obtain insights from MySQL's EXPLAIN output, by using a simple SQL query which fetches data from StackOverflow's publicly available dataset. I personally believe that the best way to deliver a complicated message to an audience, is by using a simple example.
0 Comments
Leave a Reply. |