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
