Hi, On Thursday 14 May 2009 11.48:08 Marco De Felice wrote: [query on big table]
> When I filter like orders.r_agent.has(Agents.name.ilike("test")) the
> query is too slow.
Is it sqlalchemy slowness or db slowness?
If the former, I can't help, being new to SQLAlchemy.
If the latter:
* have you created the necessary indices
* are the indices actually used?
And: are you sure you need like? stripping whitespace and using = would
certainly be faster. And if you use ilike only because you want to ignore
case: some db (PostgreSQL does, not sure about others) allow to create
indices on functions, so you'd create an index on lowercase(yourfield) and
then search this.
And if you actually need something like 'like', you may want to use some
sort of full text search feature (again, I only know PostgreSQL, which has
promoted their full text search from "contrib" module to fully supported a
few releases ago. But while pg obviously is superiour to all other systems
in the world I'm sure they're not the only one to have something like this.)
A simple query for one field should be fast even for multi-gigabyte tables
on databases today, if you use the available features.
cheers
-- vbi
--
Debug is human, de-fix divine.
signature.asc
Description: This is a digitally signed message part.
