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]
