Hi all, the manual says:
"If a subquery returns any rows at all, then EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. ... Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it doesn't matter." [http://dev.mysql.com/doc/mysql/en/exists-and-not-exists-subqueries.html ] However, please consider the following query and note that there's an index on file.document_id. EXPLAIN SELECT t1.id, t1.name, IF ( EXISTS ( -------,, NOTE that! SELECT id FROM file WHERE document_id = t1.id ), "file_exists", "no file" ) FROM document AS t1 *************************** 1. row *************************** [...] *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: file type: ref possible_keys: document_id key: document_id key_len: 2 ref: djbdms.t1.id rows: 1 Extra: Now as expected it's an dependent subquery and makes use of the index on document_id. BUT: If we change the "SELECT id" in the subquery to "SELECT document_id" or "SELECT 1", we get: *************************** 1. row *************************** [...] *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: file type: ref possible_keys: document_id key: document_id key_len: 2 ref: djbdms.t1.id rows: 1 Extra: Using index Note the "Using index"! I played around with it and it really makes a huge performance difference - in my case the file table contains a lot of large blobs, is fragmented and I'm running weak hardware. Seems as if "using index" is not just (irrelevant or wrong) EXPLAIN output but is what is really going on? Am I mistaking something, or is this an already-known bug? I did not find anything matching in the bug database. Probably not too hard to fix (whenever you're in an EXISTS subquery, just forget the select_expr), but maybe even worth a note on the manual page until it's fixed? Best regards, Matthias PS. Server version: 4.1.11-Debian_4sarge1 mysql Ver 14.7 Distrib 4.1.13, for pc-linux-gnu (i386) using readline 4.2a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
