Re: Subquery speed : IN (SELECT ...) versus IN (X, Y, Z, ...)
Hi there, thank you for your reply. I off course indexed all the required field. The problem is that, whatever you do, if you have more than one value in the IN or = ANY clause, index won't be used, just like when you do a OR. At least this is what I noticed already in the past. Concerning the query time to the zipcode selection, it's an instant query. It does not explain the difference between the 2 query times. On Thu, 24 Feb 2005 23:28:20 +0200, Eli [EMAIL PROTECTED] wrote: Hi, You may put indexes on the following fields: 1. cityname (in tblcity) 2. zip (in tblpeople) I assume it will speed up your queries. If you already do have those indexes, then try to look at the 'EXPLAIN' of the first query (with sub-query), and see the column 'type' that describes you how the tables are joined. (see: http://dev.mysql.com/doc/mysql/en/explain.html ) You may post here your EXPLAIN results. Also note that when you use the second query (B), you also spend time on the SQL1 query that you didn't consider about its time. -Eli HMax wrote: Hello list, We are currently tuning our queries speed and we found out that the ones using subqueries are quite slower than the 'usual' ones. Here is an example of a wierd behavior. We have a city list associated with zipcode, and user can search a database of people living in a given city. The problem is that a city can have several zip codes. Our first request is : SQL1 = SELECT zip FROM tblcity WHERE cityname = 'Paris' This request actually returns something like 20 results. The second request list the people living in areas with those zip codes: SQL2 = SELECT people FROM tblpeople WHERE zip IN (###) In ### we can either put - A : SQL1 - B : the list build from a recordset opened on SQL1 which would give something like : '75000', '75001', '75002', '75003', etc... Queries speed are 0.16s for A, and 0.05s for B. Can anybody explain this behavior, and maybe offer some advices on optimizing our queries. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- HMax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery speed : IN (SELECT ...) versus IN (X, Y, Z, ...)
Hello list, We are currently tuning our queries speed and we found out that the ones using subqueries are quite slower than the 'usual' ones. Here is an example of a wierd behavior. We have a city list associated with zipcode, and user can search a database of people living in a given city. The problem is that a city can have several zip codes. Our first request is : SQL1 = SELECT zip FROM tblcity WHERE cityname = 'Paris' This request actually returns something like 20 results. The second request list the people living in areas with those zip codes: SQL2 = SELECT people FROM tblpeople WHERE zip IN (###) In ### we can either put - A : SQL1 - B : the list build from a recordset opened on SQL1 which would give something like : '75000', '75001', '75002', '75003', etc... Queries speed are 0.16s for A, and 0.05s for B. Can anybody explain this behavior, and maybe offer some advices on optimizing our queries. Thanks -- HMax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery speed : IN (SELECT ...) versus IN (X, Y, Z, ...)
Hi, You may put indexes on the following fields: 1. cityname (in tblcity) 2. zip (in tblpeople) I assume it will speed up your queries. If you already do have those indexes, then try to look at the 'EXPLAIN' of the first query (with sub-query), and see the column 'type' that describes you how the tables are joined. (see: http://dev.mysql.com/doc/mysql/en/explain.html ) You may post here your EXPLAIN results. Also note that when you use the second query (B), you also spend time on the SQL1 query that you didn't consider about its time. -Eli HMax wrote: Hello list, We are currently tuning our queries speed and we found out that the ones using subqueries are quite slower than the 'usual' ones. Here is an example of a wierd behavior. We have a city list associated with zipcode, and user can search a database of people living in a given city. The problem is that a city can have several zip codes. Our first request is : SQL1 = SELECT zip FROM tblcity WHERE cityname = 'Paris' This request actually returns something like 20 results. The second request list the people living in areas with those zip codes: SQL2 = SELECT people FROM tblpeople WHERE zip IN (###) In ### we can either put - A : SQL1 - B : the list build from a recordset opened on SQL1 which would give something like : '75000', '75001', '75002', '75003', etc... Queries speed are 0.16s for A, and 0.05s for B. Can anybody explain this behavior, and maybe offer some advices on optimizing our queries. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
