Optimization and the use of indexes
I thought I heard at the conference that the optimizer only uses a one index per table in a query regardless of the number of indexes on the table. Is this true? Are there any more details regarding the use of indexes I can find in the documentation? Specifically how the optimizer picks which index to use? I read through 7.2.4 and several other sections but no luck. Paul D...Jeremy Z, are you out there? Save me! :-) Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimization and the use of indexes
On Mon, Apr 26, 2004 at 05:17:45PM -0500, Boyd E. Hemphill wrote: I thought I heard at the conference that the optimizer only uses a one index per table in a query regardless of the number of indexes on the table. Is this true? Yes. The only exception is in a UNION, but that's best thought of as multiple queries anyway. Are there any more details regarding the use of indexes I can find in the documentation? Specifically how the optimizer picks which index to use? I read through 7.2.4 and several other sections but no luck. The optimizer looks at the available indexes and attempts to find the one that will require the least amount of work, which usually translates to reading the fewest records to find a match. Have you run your queries thru EXPLAIN to see which keys is considers and which one it chooses? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimization and the use of indexes
Jeremy: That has to be a record for fastest response with the most use. WOW! I do use Explain but knowing this info allows me to use it in a more intelligent way. Thank you very much for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, April 26, 2004 4:23 PM To: Boyd E. Hemphill Cc: [EMAIL PROTECTED] Subject: Re: Optimization and the use of indexes On Mon, Apr 26, 2004 at 05:17:45PM -0500, Boyd E. Hemphill wrote: I thought I heard at the conference that the optimizer only uses a one index per table in a query regardless of the number of indexes on the table. Is this true? Yes. The only exception is in a UNION, but that's best thought of as multiple queries anyway. Are there any more details regarding the use of indexes I can find in the documentation? Specifically how the optimizer picks which index to use? I read through 7.2.4 and several other sections but no luck. The optimizer looks at the available indexes and attempts to find the one that will require the least amount of work, which usually translates to reading the fewest records to find a match. Have you run your queries thru EXPLAIN to see which keys is considers and which one it chooses? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
