![]() ![]() OPTIMIZE TABLE copies the data part of the table and rebuilds the indexes. The reorganized tables require less disk I/O to perform full table scans. For the second expression, MySQL must retrieve the value of mycol for each row, multiply by 2, and then compare the result to 4.OPTIMIZE TABLE statement is used to reorganize tables and compact any wasted space. For the first line, the optimizer will simplify the expression 4/2 to the value 2 and then use an index on mycol to quickly find values less than 2. They are equivalent arithmetically, but quite different for optimization purposes. The following WHERE clauses illustrate how this works. Sometimes this is unavoidable, but many times you can rewrite a query to get the indexed column to appear by itself. If you use a column in a function call or as part of a more complex term in an arithmetic expression, MySQL can't use the index because it must compute the value of the expression for every row. Try to make indexed columns stand alone in comparison expressions. If the columns you're comparing are of different types, you can use ALTER TABLE to modify one of them so that the types match. From 3.23 on, this is not strictly necessary, but identical column types will still give you better performance than dissimilar types. Using columns of the same type is a requirement prior to MySQL 3.23, or indexes on the columns will not be used. For example, CHAR(10) is considered the same as CHAR(10) or VARCHAR(10) but different than CHAR(12) or VARCHAR(12). When you use indexed columns in comparisons, use columns that are of the same type. Try to compare columns that have the same type. You can help the optimizer take advantage of indexes by using the following guidelines. As a result, the optimizer will attempt to test col2 first. ![]() That's only 270 failed tests, so less computation and disk I/O is required. Testing col2 first results in 300 rows that must be examined to find the 30 that also match the col1 value. Testing col1 first results in 900 rows that must be examined to find the 30 that also match the col2 value. Suppose also that the test on col1 matches 900 rows, the test on col2 matches 300 rows, and that both tests succeed on 30 rows. WHERE col1 = 'some value' AND col2 = 'some other value' Suppose you have a query that tests two columns, each of which has an index on it: Queries can be processed more quickly if the most restrictive tests can be done first. The reason the optimizer works this way is that the faster it can eliminate rows from consideration, the more quickly the rows that do match your criteria can be found. After all, because your goal in issuing a SELECT statement is to find rows, not to reject them. That last part may sound backward because it's non-intuitive. The MySQL query optimizer has several goals, but its primary aims are to use indexes whenever possible and to use the most restrictive index to eliminate as many rows as possible as soon as possible. Normally, EXPLAIN returns more information than that, including information about the indexes that will be used to scan tables, the types of joins that will be used, and estimates of the number of rows that will need to be scanned from each table. Mysql> EXPLAIN SELECT * FROM tbl_name WHERE 1 = 0 To use EXPLAIN, just put the word EXPLAIN in front of the SELECT statement: ![]() You can see this by issuing an EXPLAIN statement, which tells MySQL to display some information about how it would execute a SELECT query without actually executing it. In this case, MySQL looks at the WHERE clause, realizes that no rows can possibly satisfy the query, and doesn't even bother to search the table. For example, if you issue the following query, MySQL will execute it very quickly, no matter how large the table is: The MySQL query optimizer takes advantage of indexes, of course, but it also uses other information. For additional information, consult the optimization chapter in the MySQL Reference Manual it describes various optimization measures that MySQL takes. In this section, we'll look at how the query optimizer works. When you issue a query that selects rows, MySQL analyzes it to see if any optimizations can be used to process the query more quickly. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |