On Monday, June 07, 2004 19:09, Michael Stassen wrote; > This doesn't quite make sense. You seem to say that several rows > will match > but then you say only one will. It must be one or the other. > Perhaps I've > misunderstood you.
Firstly, Micheal thank you for your assistance; I'll elaborate. Michael I'll attempt first without supplying the table definitions and actual sql and etc.. Table 'a' contains a property which does not match any property on table 'b' directly, but matches within a range. Example; table 'a' property 'num' num --- 1000 2000 3000 table 'b' properties 'fromNum' and 'toNum' fromNum toNum row 1. 1 1500 row 2. 1501 2000 row 3. 2001 4000 select... where 'num' between 'fromNum' and 'toNum'. As seen in this example, using between only one row actually matches, however the query engine would at first see more than one matching row i.e., with 'fromNum' (between is same as num >= fromNum), value 2000 from table a would match row 1 & 2 on table b; and with 'toNum' (between is same as num <= toNum), value 2000 from table a would match row 2 & 3 on table b; only once the range is taken together the result matches one row, viz., row 2 on table b. Now please remember table b has 1.4 million rows as in this example, with fromNum and toNum running consecutively, so the query takes 4 seconds to find a row in table b. With say 200 rows in table a, that means the query runs for a long time. What I did was to use limit 1, and ran query with 1 row, this took .01 second. > You also seem to imply that with BETWEEN you > get a full > table scan even though there is only one match for each row. That sounds > like an indexing problem, but it is hard to say without more information. Indexed individually on; fromNum toNum Also experimented by adding combining index on; fromNum/toNum Either way no difference, query runs 4 seconds. > It would help us help you if you at least posted the query and > the results > of EXPLAIN. It would probably also help if you told us more about the > tables, perhaps with SHOW CREATE TABLE. If above does not help you, I'll bring the whole lot to the post. Michael, again thank you for you assistance. Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]