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 kommentar(er)
