tailieunhanh - MySQL High Availability- P8

MySQL High Availability- P8: A lot of research has been done on replication, but most of the resulting concepts are never put into production. In contrast, MySQL replication is widely deployed but has never been adequately explained. This book changes that. Things are explained here that were previously limited to people willing to read a lot of source code and spend a lot of time debugging it in production, including a few late-night sessions. | id 2 select_type UNION table film type ref possible_keys film_rating key film_rating key_len 2 ref const rows 210 Extra Using where 3. row id NULL select_type UNION RESULT table union1 2 type ALL possible_keys NULL key NULL key_len NULL ref NULL rows NULL Extra 3 rows in set sec Success Now we can see we have a query plan that is using the index and processing far fewer rows. We can see from the result of the EXPLAIN command that the optimizer is running each query individually steps execute from row 1 down to row n and combines the result in the last step. MySQL has a session status variable named last_query_cost that stores the cost of the last query executed. Use this variable to compare two query plans for the same query. For example after each EXPLAIN check the value of the variable. The query with the lowest cost value is considered to be the more efficient less time-consuming query. A value of 0 indicates no query has been submitted for compilation. While this exercise may seem to be a lot of work for a little gain consider that there are many such queries being executed in applications without anyone noticing the inefficiency. Normally we encounter these types of queries only when the row count gets large enough to notice. In the sakila database there are only 1 000 rows but what if there were a million or tens of millions of rows Aside from EXPLAIN there is no single tool in a standard MySQL distribution that you can use to profile a query in MySQL. The Optimization chapter in the online MySQL Reference Manual has a host of tips and tricks to help an experienced DBA improve the performance of various query forms. Database Performance 327 Please purchase PDF Split-Merge on to remove this watermark. Using ANALYZE TABLE The MySQL optimizer like most traditional optimizers uses statistical information about tables to perform its analysis of the optimal query execution plan. These statistics include information about indexes distribution of