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
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.
banner and event ALTER TABLE mod_banner ADD INDEX banner_event (banner, event), but still over 100ms :P i.imgur.com/IxWoPZU.pngORDER BY made it instant. i.imgur.com/ZUQxksi.png But I need the one with the lowest last_view
WHERE the solution was dealing with the ORDER, adding INDEX on the last_view column i.imgur.com/uDu9oyT.png
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.
SQL_NO_CACHEwhen you want to benchmark queries because the results might be compromised due to the fact that the second query can get the result from mysql cachebannerand you index it while column has 10 unique values - if you have 200k rows then the cardinality is 10/200000. That's a low number and MySQL won't gain anything. Consequently, operations using primary key are fast, because PK cardinality is always 1.