CODEWITHSUNDEEP
mysql
michalzuber
michalzuber

Reputation: 5215

Adding index increases query execution

How's that possible when I added index to a column it slowed down the execution time? Trying to get rid of the query from slow queries log. My slow-query settings:

slow_query_log = 1
long_query_time = 1 # seconds
log_queries_not_using_indexes = 1
slow_query_log_file = /var/log/mysql-slow.log

describe table index slows query

Upvotes: 1

Views: 1268

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Indexes do not always speed up execution. The effect of an index depends primarily on the "selectivity" of the query: how many rows are processed by the overall query.

In general, reading a database (a "full table scan") is an efficient operation. The database engine knows what pages it needs to read and can read ahead to get them. Such I/O often occurs in the background, while processing the pages is in the foreground. When the next page is needed though, there is a good chance it is already in the page cache.

The performance issue with full table scans is that tables are big. So even efficient reads take time. When you are looking for one row in a million ("needle-in-the-haystack" queries), the reads are a waste of time. This is where indexes fix things.

However, say you have 100 records per page and you are reading more than 1% of the records. On average, every page will need to be read -- whether you are using an index or a full-table scan. The problem is that index reads are less efficient than scan reads. A read-ahead mechanism doesn't help them, because the reads are random.

This problem can be further exacerbated through something called thrashing. If the table does not fit into memory, then each random read is likely to be a "cache miss", incurring the overhead of a read from disk. The full table scan would just read the data, and with a decent look-ahead system, there would be no cache misses.

In your example, you could increase the selectivity of the index by including both banner and event in the index (these are compared using equality) and one of the other fields.

Upvotes: 4

noamik
noamik

Reputation: 827

Depending on structure of the data on disk, it might be faster to just load the entire db/column and sort/filter it in ram (which will likely happen when no index exists), than to traverse a sparsed index on disk. I don't know if this applies to your specific context or if you have another issue here though.

Upvotes: 0

Related Questions