Performance first low, then high after adding index, then keep high after drop index

Question

I am testing performance of a complicated select query. It is very slow.

Using explain, I have found a ALL in type COLUMN. Then I ADD INDEX. The result comes out fast. However, when I DROP the INDEX I added, the result comes out fast too.

I have set query_cache_size=0. There is no cache for each query result.

I am a new learner. Could some experts tell me why and what other items I need to check? How can I go back to the first slow scenario and verify that it is the index I need to add which has big impact of the performance?

Sample:

select col1,col2 from tbl1 groupby col1,col2;

show create table shows "index(col1), index(col2)"

explain shows the select query have type=ALL.

Then I add index(col1, col2) which made explain type=RANGE.

Answer 1

Most likely the statistics on the field were out of date. Adding the index created/updated statistics with a full table scan. Or creating the index pulled all the data you need into memory, like any scan would.

Adding an index will only perform better if it is being used. Add the index and then check the query plan. If the stats are up to date and the query optimizer elects to use it there is a big chance it will improve performance. Removing statistics has no use as it will result most likely in a suboptimal query plan.

More info on statistics https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/engine-independent-table-statistics/

Answer 2

Since you have dropped your index and probably stats aren't up to date I'd suggest running ANALYZE against a table to get valid execution plan.

MariaDB - ANALYZE TABLE