Hi,
I have a simple table like
CREATE TABLE RecipeIngredients (
id integer primary key,
recipeid integer(10),
unitId integer(10),
...
);
With an index on recipeid and unitid (foreign keys)
In the table there are 11 rows where recipeid is "558134" and 243042 rows
where the unitid is "3" from totally 5.4 mill. rows.
If I do a
select recipeid, unitid from RecipeIngredients
where recipeid = 558134 and unitid = 3
it takes about 80 seconds. If I change the order in the expression to
select recipeid, unitid from RecipeIngredients
where unitid = 3 and recipeid = 558134
it also takes about 80 seconds.
But if I do
select recipeid, unitid from RecipeIngredients
where unitid > 3 and recipeid = 558134
It takes less than 1 second.
I tried to do a "explain" and can see that there are a difference in the way
it is looping through data, but don't really understand why.
Any one has a good explanation?
Frank Tolstrup
