"Copying to tmp table" queries puts huge load on server

[puneetjain87]

mysql queries are causing server troubles for me.

Here is how it started.

I migrated from 1.5 to 1.7 and then to 2.5
I did a fresh install of 1.7 and just imported my articles and users from 1.5 to 1.7 (I did not use jupgrade)
I used J2XML to export my files from joomla 1.5
and used
J2XML Importer to import into Joomla 1.7

So, after I started using Joomla 1.7 all the trouble started.

Upgrading to 2.5 from 1.7 was easy, did it with a hit on a button.

When this issue happens?
It happens every time I clear the cache or it get automatically cleared. After cache is cleared a query "Copying to tmp table" starts these queries cause lots of server load causing it to stop working.

I read a few thread where it was suggested that asset table is causing this issue, however in my case I checked the asset tablet twice and it exactly same as what is suggested here http://docs.joomla.org/Fixing_the_assets_table

If anybody has any solution for this, please share. I will be really grateful.

[puneetjain87]

I am still looking for a possible solution and while searching I landed on this https://groups.google.com/forum/#!topic ... -9HOB7zEN4
It's seems to be a Joomla issue for sure. It's there on Joomlacode as well, but it seems there are very less people who are bothered to get it working.

PS: my jos_content is a heavy one. Over 4000 articles and its size is around 18MB

[puneetjain87]

I spend lots of my time to read about Joomla 2.5 performance issues, as the new versions are a blunder for website having huge amount of content.

Anyways, I was trying to figure out how to get over this copy to tmp table query and in the process I realized what was causing it. It was the Recent Articles module, I disabled it and those slow queries are now gone. Though I am testing it from last few hours, so it's bit early to claim, still I feel it is the cause of the stupid heavy mysql usage.

If you are having the same issue, please try this and share your experience here.

[codigoaberto]

try change the sql query directly of the module to your needs if you know that

[puneetjain87]

I won't be able to do that. However my mysql heavy usage issue is not resolved. It was a buggy module which was causing it.

[ervik]

Which module was causing the heavy usage issue? I see similar problems with copying to tmp table. I have about 3100 articles and at a size of 14 MB.

[puneetjain87]

Ervik which all modules are you using?