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]
