Re: increase index performance

On Tue, 12 May 2009, Greg Smith wrote:
> You should test what happens if you reduce the index to just being
> (city_id,street_id).

I think you're missing the point a little here. The point is that Thomas
is creating an index on (city_id, street_id, house_id, floor_id) and
running a query on (city_id, house_id, floor_id).

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.

Matthew

--
 An ant doesn't have a lot of processing power available to it. I'm not trying
 to be speciesist - I wouldn't want to detract you from such a wonderful
 creature, but, well, there isn't a lot there, is there?
                                        -- Computer Science Lecturer