Re: increase index performance

Matthew Wakeling wrote:
> Thomas, the order of columns in the index matters. The index is
> basically a tree structure, which resolves the left-most column before
> resolving the column to the right of it. So to answer your query, it
> will resolve the city_id, then it will have to scan almost all of the
> tree under that, because you are not constraining for street_id. A much
> better index to answer your query is (city_id, house_id, floor_id) -
> then it can just look up straight away. Instead of the index returning
> 200000 rows to check, it will return just the 2000.

Thats something I was a bit unsure about, because of the cardinality of
the data. But thanks, I will try it. Just need to populate a new data
base with the new index. (Apparently, creating a new index on an already
existing database is slower than just recreating the db, when the db is
250GB big)


thomas