Re: MySQL Partitioning showing low performance

[Rick James]

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