MySql optimisation query

I was having issues with my website where a specific page was taking about 15 seconds to return data on my live website and 2 seconds on my staging website. They both used the same database backup though.

I then realized that for my staging database, I had deleted the old staging database and restored the converted backup as the new staging database.

However, with the live database, I had run the database conversions directly on the live database.

So then I did the same for my live database and deleted the existing one, then restored it from the converted backup and voila, the time went down from 15 seconds to 2 seconds for that one particular web page.

My question is why?? The only thing that comes to mind is that maybe the indexes were getting too large, and by deleting the database, the indexes were recreated?\开发者_开发知识库

Am I far off? Any other reasons? Also, if this was the case, how could I get around this in future without deleting the database??

Thanks

---

Try checking out the explain plan, or maybe your stats need to be updated. It's hard to determine these kind of things at a high level because there are so many different things that can happen across servers.

---

Is it a high-write or high-read issue (or both) database?

What "conversions" are you doing in the DB that you weren't doing on the staging DB?

Could you perhaps be a little bit more specific with whats going on?

Maybe read this book http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/dp/0596003064 (its very good) (I am not the author).

---

It could easily be that when you rebuilt the table the records were laid down in a sequence that put useful records near each other so the query doesn't need as many disk reads. The MySQL stats can show you read-counts if you want to check.