MySQL Forums
Forum List  »  Newbie

Re: MySQL Partitioning showing low performance
Posted by: Rick James
Date: February 06, 2013 08:15PM

> SELECT column8, column9 FROM myTable WHERE column2 = ? AND column3 = ? AND column4 =? AND column5 = ? AND column7 = ? AND column6 = ? LIMIT 1;

There is nothing magical about this SELECT with/without partitioning. It will use the perfect index. PARTITIONed may be slower because it may check each partition.

> But surprisingly, the response time of the application got reduced by 3 times when doing the load testing after partitioning was implemented.
> However, as I said, the performance after implementing partitioning was reduced by a shocking 3 times. Any idea what may have caused this?

I'm puzzled -- exactly what did you test that ran 3 times as fast? If we are looking at fractions of a second, I would not take much stock in that result.

> Also, please let me know if doing any further change in the table structure or indexing may help resolve this issue.

Will you be purging old data? If so DROP PARTITION will be infinitely faster than DELETEing the rows for that old month.

What other queries will you be running? Most people are surprised when PARTITIONing does not speed up anything.

It is possible to get a query to run 10 times as fast, even without doing anything. I warn people frequently about this. Bring up the system; do a SELECT; so the same SELECT again. The first SELECT warms up the cache (buffer_pool); the second one takes advantage of the cache. Also if the Query Cache is turned on, it could easily run 1000 times as fast. I like turn off the Query Cache, then quote the second SELECT.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.