Re: Will number of rows degrade performance?
Posted: Tue Aug 05, 2008 12:45 pm
Sure, but remember that indexes hardly ever work in isolation in the real world. When you have a number of indexes that can be used in a query (which may come from ON, HAVING, ORDER BY, GROUP BY, and nearly any clause of the query) the order in which the indexes are used is very relevant to the performance of the query. It is not the same to inspect 10,000 out of 100,000 rows (because we have an index on, say status_id) than to inspect every single row because we are indexing a column with high cardinality (such as the user's full name).
MySQL knows this and thus will try to use the smallest possible candidate index first. That way, right off the bat, we may eliminate 3/4 of the records we have to look through.
Furthermore, index lookup isn't a free operation either, and traversing an index with 100,000 entries will take a lot longer than traversing one with 4 entries. In some extreme cases it may even take as long as a full table scan (if the number of rows in the table equals the number of entries in the index), in which case the index is nearly useless or even counterproductive. Using an index also carries the additional overhead of retrieving the rows that the index points to. Luckily, MySQL knows this too, and will simply not use the index if it believes it will be as costly as doing a full table scan (ie: when the cardinality of the index is the same as the table)
MySQL knows this and thus will try to use the smallest possible candidate index first. That way, right off the bat, we may eliminate 3/4 of the records we have to look through.
Furthermore, index lookup isn't a free operation either, and traversing an index with 100,000 entries will take a lot longer than traversing one with 4 entries. In some extreme cases it may even take as long as a full table scan (if the number of rows in the table equals the number of entries in the index), in which case the index is nearly useless or even counterproductive. Using an index also carries the additional overhead of retrieving the rows that the index points to. Luckily, MySQL knows this too, and will simply not use the index if it believes it will be as costly as doing a full table scan (ie: when the cardinality of the index is the same as the table)