Hi Jasper,

>
> 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.

No index usage,
building result set by natural scan and sorting in memory operation.

>
> 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

Scan table using telefoon index; indirect full-scan.
your "where" conditions field unreleted with "order by" field here.

>
> 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
>

55 columns? 55* 4 byte fields? or 50*12KB + 5 small fields ?
your one page may saves 16 rows or your one row may saving by 16
pages...
Also, how many rows are there?

There is no problem i think..

Regards,

Ali
FFM #208