CODEWITHSUNDEEP
mysql
Prasanna Kumar J
Prasanna Kumar J

Reputation: 1598

Drop and Recreate Index on MYSQL table, it will improve the performance?

I have Executed the query in the newly imported MySQL database but it takes 68sec to complete. Then I have dropped and recreated the same indexes on 2 main tables then it takes 24sec only.

Why it has occurred? Is it a good practice or not?

Thanks in Advance

Upvotes: 1

Views: 1640

Answers (1)

Gordan Bobić
Gordan Bobić

Reputation: 1858

You are misinterpreting the results and the cause. Dropping and re-creating the indexes isn't what makes it go faster. There are two things that could be going on:

1) DB doesn't fit into RAM so when you recreated two indexes that made most of them stick in the buffer pool by the time you ran the query.

2) Table was fragmented or had very lightly filled blocks. Recreating indexes probably rebuilt the table and that may have improved page occupancy If your query requires a full table scan, this would have meant fewer GBs of table to scan and possibly less fragmented (can matter on spinning rust).

As a general rule you should never need to do that. If you disable the query cache (query_cache_type=0, query_cache_size=0 on MySQL < 8), and run the query twice, the second time is the speed you can expect with hit buffer pool.

Upvotes: 2

Related Questions