12/9/2023 0 Comments Mysql like on select![]() ![]() So, for example, if you think that you have indexed and partitioned your tables properly, but your queries still refuse to run as fast as you want them to, it might be time to tell them to EXPLAIN themselves. In the MySQL space, everything is pretty simple: EXPLAIN queries can (and should) be used when you are unsure whether your query does what it is supposed to do. However, we have not yet covered when you should use such queries in the first place. When to Use EXPLAINīy now, you should be aware of the power of EXPLAIN queries in MySQL. However, once you grasp its features and understand how to best use it to achieve your performance goals, using it becomes easier. There's no doubt that EXPLAIN provides a lot of information. Any extra information relevant to the query.The percentage of rows filtered by a specified condition.The number of rows accessed by the query.The length of the index chosen by MySQL.The table on which your query was running.The type of your SELECT (if you are running a SELECT).In other words, this keyword provides information about how a database executes a certain query – add EXPLAIN in front of a query that you run, and you will be able to observe: ![]() In the MySQL world, EXPLAIN is a keyword used to gain information about query execution. The EXTENDED command can be used to gain further information about how MySQL executes the query: it can be used to determine, for example, what function is used by MySQL to compare columns to the index to select rows from the table, etc. The extended information is provided for SELECT, DELETE, INSERT, REPLACE, and UPDATE statements if you are using MySQL 8.0.12 or newer, and only for SELECT queries in older versions of MySQL or MariaDB. The output above is derived from the EXPLAIN EXTENDED command-you can append EXTENDED if you want MySQL to provide you with some further information. You can see which table you just ran the query on, what partitions were used, the type of the query, the indexes that may have been used and were used, the length of the indexes, the number of rows, and whether any percentage of rows were filtered by a condition you specified. row **********************Īs you can see, EXPLAIN provides you with quite a lot of data, including the ID of the query and the type of SELECT query you just ran ( SIMPLE refers to the fact that the query did not use JOIN operations). To use EXPLAIN in its simplest form, append the keyword to the beginning of your query like so:ĮXPLAIN SELECT * FROM demo_table WHERE demo_column = 'Demo Value' Ī query like the above should produce output similar to the following: ********************* 1. ![]() After reading this blog post, you should understand the importance of this keyword (if you don't already). Want to know what index is used by your query? EXPLAIN. Want to know what your queries do on a deeper level? EXPLAIN. Have you ever sought out advice regarding query performance? EXPLAIN. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |