MySql optimisation query

Question

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

Parris Varney · Accepted Answer · 2011-01-07 19:29:55Z

1

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.

answered Jan 7, 2011 at 19:29

Parris Varney

11.5k12 gold badges49 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Paolo Broccardo Over a year ago

Hi PMV. Where is this explain plan you mentioned? Is it available in WorkBench or MYSQL Query Browser???

Parris Varney Over a year ago

Just write EXPLAIN before the query. beberlei.de/mysql_explain.html should help you understand the output.

Paolo Broccardo Over a year ago

Thanks - this lead me to slow queries which were due to a combination of large data and bad indexes

daidoji70 · Accepted Answer · 2011-01-07 19:21:04Z

0

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).

answered Jan 7, 2011 at 19:21

daidoji70

3714 silver badges15 bronze badges

1 Comment

Paolo Broccardo Over a year ago

The only difference between the live database and the staging database is as follows: For staging I took a copy of the live database, ran some conversion scripts on it and restored it as "staging" For live, I took ran the same conversion scripts on the live website. So essentially, the live and staging databases are identical. The only difference being that the staging db was deleted and restored from a copy of the live db, whereas live was NOT deleted and simply had the conversion run on it without being taken offline, and then restored.

dkretz · Accepted Answer · 2011-01-07 19:38:06Z

0

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.

answered Jan 7, 2011 at 19:38

dkretz

37.6k13 gold badges84 silver badges140 bronze badges

Collectives™ on Stack Overflow

MySql optimisation query

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related