{"id":95637,"date":"2023-01-31T19:48:04","date_gmt":"2023-01-31T19:48:04","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95637"},"modified":"2023-01-31T19:48:04","modified_gmt":"2023-01-31T19:48:04","slug":"optimizing-queries-in-mysql-optimizing-reads","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/optimizing-queries-in-mysql-optimizing-reads\/","title":{"rendered":"Optimizing Queries in MySQL: Optimizing Reads"},"content":{"rendered":"

In this blog, we\u2019re going to walk you through how to solve the issues posed by that concern. At the end of the article, we\u2019ll also walk you through some DDL operations to load test data.<\/p>\n

SELECT Queries in MySQL<\/h2>\n

In my introduction blog<\/a>, I noted: Queries are processes composed of tasks \u2013 their performance depends directly on the performance of those tasks<\/em>.<\/p>\n

To address problems posed by SELECT<\/code> queries, the first thing we need to understand is what happens when a SELECT<\/code> queries are executed. Here\u2019s what MySQL does in the background when we execute a SELECT<\/code> query:<\/p>\n

    \n
  1. Our SQL client sends the query to the server to be executed.<\/li>\n
  2. A specific query execution plan is followed.<\/li>\n
  3. The result is returned.<\/li>\n<\/ol>\n

    Understanding these steps is no less vital than diving deeper into the query execution plan, and the output provided by an EXPLAIN<\/code> statement execution – a query execution plan will provide us information in regards to what time MySQL takes to go through each step necessary to complete a query. The EXPLAIN<\/code> statement will provide us more details in how MySQL executes the query itself.<\/p>\n

    For the purposes of this blog, we\u2019re going to use the following table with three columns (it\u2019s not necessary to specify the storage engine \u2013 MySQL will create the table based on the InnoDB<\/code> storage engine by default if it\u2019s not specified. In this specific scenario, the indexing part is not necessary, but if it\u2019s specified, MySQL will create an index named \u201cemail\u201d (indexes can have any names) on a column named \u201cemail.\u201d) Also note the sizes of the email and username columns \u2013 in this case, emails shouldn\u2019t be longer than 35 characters in length, and usernames shouldn\u2019t be longer than 20.<\/p>\n

    <\/a> It\u2019s also worth noting that for the columns with the type of an integer<\/code> the length of the column is deprecated starting from MySQL 8. So you might as well want to consider leaving the columns with an integer type intact without specifying a length attribute:<\/p>\n

    CREATE TABLE demo_table (\r\n  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\r\n  email VARCHAR(35) NOT NULL,\r\n  username VARCHAR(20) NOT NULL DEFAULT '',\r\n  INDEX email(email)\r\n) ENGINE = InnoDB;<\/pre>\n
    To insert data into the table, use INSERT<\/code> or LOAD DATA INFILE<\/code> \u2013 INSERT<\/code> is an excellent choice if you\u2019re dealing with smaller data sets (less than 100 million rows), while LOAD DATA INFILE<\/code> can be very useful to work with big data sets. For INSERT, the syntax looks like so:<\/p>\n
    --A few rows for demos. At the end of the article is \r\n--a method to load more rows.\r\nINSERT INTO demo_table (email,username) \r\nVALUES ('demo@demo.com', 'Demo'), \r\n('demo2@demo.com', 'Demo'), \r\n('Another@Email.com'), \r\n('AnotherDemo');<\/pre>\n
    For LOAD DATA INFI<\/code>LE, the syntax looks like this \u2013 the FIELDS TERMINATED BY<\/code>. Part of the query can be used to specify the character that terminates fields from one another (one can also specify what columns to load the data into by specifying them at the end of the query if we wish):<\/p>\n
    LOAD DATA INFILE '\/path\/to\/file.txt' \r\n  INTO TABLE demo_table [FIELDS TERMINATED BY '|'];<\/pre>\n
    The Query Execution Plan<\/h2>\n
    To dive deeper into the query execution plan, we can use the MySQL query profiler or dig into the richer performance schema by following the steps outlined in the MySQL documentation<\/a>. For many, though, using the query schema is more difficult than going through the simpler query, so I will use the profiler:<\/p>\n
      \n
    1. Select a database, then issue a SET profiling = 1<\/code> query to enable profiling.<\/li>\n
    2. Run your query, then issue a SHOW PROFILES<\/code> query to investigate all of the profiles of all queries.<\/li>\n
    3. Select the ID of the query you just ran (identify the query from the list provided by the profiler), then execute a SHOW PROFILE FOR QUERY [id]<\/code> query.<\/li>\n<\/ol>\n
      Note: Query profiling with <\/em>SET profiling = 1<\/code> has been deprecated since MySQL 5.6.7 and people are advised to follow the performance schema instead \u2013 <\/em>the documentation will walk you through all of the necessary steps<\/em><\/a>, just note that getting started with it is a little more complex than using the optimizer. <\/em><\/p>\n
      \"D:\\Redgate\\Blogs\\October<\/p>\n
      Image 1 – Profiling a Query. Initializing<\/em><\/p>\n
      \"D:\\Redgate\\Blogs\\October<\/p>\n
      Image 2 – Profiling a Query. Results<\/em><\/p>\n
      The query execution plan is displayed above. Now we need to properly understand what\u2019s it all about, We will start from the top and move toward the bottom (for newer versions of MySQL, the results will look a little different \u2013 there may be more items such as \u201cExecuting hook on transaction\u201d and \u201cwaiting for handler commit\u201d, but the core premise is the same):<\/p>\n
        \n
      1. Starting<\/code> – The process of initializing the query.<\/li>\n
      2. Checking permissions<\/code> – Refers to the process of MySQL checking whether the user has sufficient permissions to run the query. If permissions are not present or they are not sufficient, the process terminates here.<\/li>\n
      3. Opening tables<\/code> – The process of MySQL opening all tables for operations. Once the tables are opened or if the tables are already open, MySQL proceeds to step #4.<\/li>\n
      4. Init<\/code> – MySQL performs initialization processes \u2013 for example, the InnoDB log and the binary log are being flushed.<\/li>\n
      5. System lock<\/code> – Once MySQL reaches this phase, it\u2019s waiting for a system lock on the table to be released if it\u2019s in place. In this phase, the query is making use of a function called mysql_lock_tables()<\/code> and is waiting for its completion \u2013 more information can be found in the documentation<\/a>, in the \u201cSystem lock\u201d paragraph.<\/li>\n
      6. Optimizing<\/code> – Internal processes performed by MySQL to determine how to execute the query in the quickest way. Think of math equations \u2013 if you multiply 2 by 0, you can realize the answer is 0 in a couple of ways: by solving the equation, or by realizing that multiplying anything by 0 yields 0. That\u2019s what MySQL does here.<\/li>\n
      7. Statistics<\/code> – Calculating statistics-related data to further develop the query execution plan.<\/li>\n
      8. Preparing<\/code> is pretty much self-explanatory: MySQL is preparing to run the query.<\/li>\n
      9. Executing<\/code> – Once the server reaches the execution phase, the query is being executed to start producing rows for output.<\/li>\n
      10. Sending data<\/code> – MySQL is doing internal work to execute the query and return the results of that query.<\/li>\n
      11. End<\/code> – The end of the query before the cleanup process.<\/li>\n
      12. Query end<\/code> – refers to the end of query processing.<\/li>\n
      13. Closing tables<\/code> – The tables that were opened and impacted by the query are being closed.<\/li>\n
      14. Freeing items<\/code> – Means that MySQL is freeing items from the thread used to execute a query.<\/li>\n
      15. Cleaning up<\/code> – Finally, this stage refers to the cleaning up of items in the memory and resetting certain necessary internal variables.<\/li>\n<\/ol>\n
        For those who use the profiler, the results will be a little different, but the result will be pretty much the same. To employ the profiler, employ the following:<\/p>\n
          \n
        1. Tell MySQL to enable monitoring for the user that runs the query by running two queries – the first one will ensure that monitoring is off for everyone, and the second one will enable monitoring for your specific account (replace \u201croot\u201d with your account name):<\/li>\n<\/ol>\n
          UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%';
          \nINSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('localhost','root','%','YES','YES')<\/code>; <\/p>\n
          <\/p>\n
            \n
          1. Enable two things – logs for statements and statement stages by running these queries:<\/li>\n<\/ol>\n
            UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
            \n<\/code> 
            \nUPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%'<\/code>; 
            \n<\/p>\n
              \n
            1. Update the setup_instruments<\/code> table with these queries (there are no matches for me because the values are already updated):
              \nUPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement\/%';<\/code>
              \nUPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage\/%';<\/code><\/li>\n<\/ol>\n
              <\/p>\n
                \n
              1. Finally, you can execute your query: 
                \nSELECT * FROM demo WHERE email = 'demo@demo.com';
                \n<\/code> 
                \n<\/li>\n
              2. Now identify the query by querying the query history – run this query (replace YOUR QUERY HERE with your actual query): 
                \nSELECT EVENT_ID, TRUNCATE(TIMER_WAIT\/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%YOUR QUERY HERE%';<\/code><\/li>\n
              3. Then to see the query duration and stage information, query the query history table like so (replace Query_ID<\/code> with the actual query ID<\/code>): 
                \nSELECT event_name AS Stage, 
                \n       TRUNCATE(TIMER_WAIT\/1000000000000,6) AS Duration 
                \nFROM performance_schema.events_stages_history_long 
                \nWHERE NESTING_EVENT_ID = QUERY_ID;<\/code><\/li>\n<\/ol>\n
                <\/p>\n
                As you can see, the results don\u2019t differ very much, and once we are aware of what the tasks in the query execution plan mean in the context of MySQL, we can start optimizing them.<\/p>\n
                Optimizing Tasks – the EXPLAIN Query<\/h2>\n
                To optimize the tasks that comprise a SELECT<\/code> query and make the query faster as a result, we need to take a look at the output of an EXPLAIN<\/code> query that explains what the query is making use of internally. Profiling will tell us how long each query phase takes while explaining queries will tell us how the query is actually executing. Issue a SELECT<\/code> query like you normally would, just add EXPLAIN<\/code> at the start. We will use an example query specified below:<\/p>\n
                EXPLAIN SELECT * \r\nFROM demo_table \r\nWHERE email = 'jfriesen@example.net';<\/code><\/pre>\n
                <\/p>\n
                The output of the EXPLAIN<\/code> query will tell us a number of things:<\/p>\n