the postgres trick is to use partial index on the column to improve the
query speed.
1. create a substring index on the table, lets say 5 characters wide:
CREATE INDEX speed_shortuuid ON table(substr(shortuuid, 0, 5)
2. have all queries include a match against that index, calculating the
substring in sql or python
WHERE shortuuid = :input AND substr(shortuuid, 0, 5) = substr(:input,
0, 5)
WHERE shortuuid = :input AND substr(shortuuid, 0, 5) = :input_substring
Postgres will only use the index if it appears exactly in the query - so
you can implement that with custom compiler in sqlalchemy, so it
automatically upgrades a search by shortuuid to also have the substring.
You can also do the same thing with the postgres primary key.
I use this a lot on uuids and md5s. it takes a bit more disk space, but
queries can run 100x faster. sqlalchemy's various hooks let you
consolidate the implementation and easily tweak it.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
