Hi Jasper.

Indexes are useful mainly in determining which records to return, i.e.
for the fields in the WHERE clause. Occasionally (I don't quite know
when, but guess at least when a field is both in the WHERE and ORDER BY
clauses), the index can be useful for sorting as well. In general, it is
quicker not to use an index than to use it unless you can eliminate a
lot of rows through using the index.

Your case happens to be one of those cases where the use of an index
doesn't help. In general, you should return as few rows as possible -
i.e. look up the one instantie you're interested in rather than return
10000 rows and look through them (the same goes for fields, return the
fields of interest and leave the other on the server).

Of course you may want 10000 records every once in a while, but those
are the rare cases where it doesn't matter if you have to wait a few
seconds, minutes and possibly even hours. E.g. a few weeks ago I
retrieved over 10000 records (as a result of processing a lot more
rows), they were then printed, put in about 50 different envelopes, sent
and now we're expecting those we sent them to to look up each individual
record and return information to us. You should never be interested in
10000 rows unless each individual row is the subject of further
attention. If you really need the extra milliseconds, you could do
something like

ORDER BY Instantie.telefoon+0

Set

jasperelzinga wrote:

> Hello,
>
> Im having a strange problem after i create indices.
>
> Normal situation:
>>>> timeExecute("SELECT Instantie.id FROM Instantie WHERE
> Instantie.deleted=0 ORDER BY Instantie.telefoon ASC", [])
> execute time: 0.56299996376
>>>> a = timeFetchall()
> fetchall time: 0.140999794006
>
> This selects 10.000 records, sorted on column 'telefoon' which has no
> index.
>
> Create an index on telefoon:
>>>> cur.execute("CREATE INDEX temp ON Instantie (telefoon)")
>>>> con.commit()
>>>>
>
> And now i execute the exact same query:
>>>> timeExecute("SELECT Instantie.id FROM Instantie WHERE
> Instantie.deleted=0 ORDER BY Instantie.telefoon ASC", [])
> execute time: 0.0
>>>> b = timeFetchall()
> fetchall time: 0.65700006485
>
> The execute time has been reduced a lot, that is what i'd expected.
> But.. the fetchall time is now more than half a second!
>
> Ok.. and to be sure, check if the results are te same:
>>>> a == b
> True
>
> Im really puzzeled by this situation. The fetchall method just returns
> the data (a list of tuples), which is exactly the same in both
> situations. I can't think of any reason why it could slow down. There
> are 10.000 records in the table and it has 55 columns, with 'id' as
> primary key with an index on it.
>
> What could be the reason and what could i do to fix this problem?
>
> Thanks in advance,
>
> Jasper Elzinga