![]() They areequivalent arithmetically, but quite different for optimization purposes: WHERE mycol = 'Mac' AND last_name ALTER TABLE member ADD INDEX (expiration) The following WHERE clauses illustrate how this works. Sometimesthis is unavoidable, but many times you can rewrite a query to get the indexedcolumn to appear by itself. If you use a column in a function call or as part of amore complex term in an arithmetic expression, MySQL can't use the indexbecause it must compute the value of the expression for every row. Try to make indexed columns stand alone in comparisonexpressions. If the columns you'recomparing have different types, you can use ALTER TABLE tomodify one of them so that the types match. CHAR(10) isconsidered the same as CHAR(10) or VARCHAR(10) but differentfrom CHAR(12) or VARCHAR(12). Forexample, INT is different from BIGINT. Whenyou use indexed columns in comparisons, use columns that are of the same type.Identical data types will give you better performance than dissimilar types. Try to compare columns that have the same data type. You can help the optimizer take advantage of indexes by using the followingguidelines: As a result, the optimizerwill test col2 first because doing so results in less work overall. That's only 270 failedtests, so less computation and disk I/O is required. That's 870 failed tests.Testing col2 first results in 300 rows that must be examined to findthe 30 that also match the col1 value. Suppose also that the test on col1 matches 900 rows, the test on col2 matches 300 rows, and that both tests together succeed on 30 rows.Testing col1 first results in 900 rows that must be examined to findthe 30 that also match the col2 value. Suppose that you have a query that tests two columns, each ofwhich has an index on it: SELECT col3 FROM mytableWHERE col1 = 'some value' AND col2 = 'some other value' Queries can be processed more quickly if the most restrictive tests canbe done first. The reason theoptimizer tries to reject rows is that the faster it can eliminate rows fromconsideration, the more quickly the rows that do match your criteria can befound. After all, your goal in issuing a SELECT statement is to find rows, not to reject them. That last part mightsound backward and nonintuitive. The MySQL query optimizer has several goals, but its primary aims are to useindexes whenever possible and to use the most restrictive index in order toeliminate as many rows as possible as soon as possible. Normally, EXPLAIN returns more information than that, including non- NULL 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 examined from each table. row *************************** id: 1 select_type: SIMPLE table: NULL type: NULLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Impossible WHERE 1 To use EXPLAIN, justput the word EXPLAIN in front of the SELECT statement: mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G*************************** 1. You can see this by issuing an EXPLAIN statement, which tellsMySQL to display some information about how it would execute a SELECTquery without actually executing it. In this case, MySQL looks at the WHERE clause, realizes that no rowscan possibly satisfy the query, and doesn't even bother to search thetable. For example, if you issue the following query, MySQLwill execute it very quickly, no matter how large the table is: SELECT * FROM tbl_name WHERE 0 ![]() ![]() The MySQL query optimizer takes advantage of indexes, of course, but it alsouses other information. For additional informationabout optimization measures that MySQL takes, consult the optimization chapterin the MySQL Reference Manual. 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 anyoptimizations can be used to process the query more quickly. Learn More Buy The MySQL Query Optimizer
0 Comments
Leave a Reply. |