Slow running Magento1.9 query killing site

[jayreis]

I migrated our Magento 1.9 website to use an Amazon rds database and now for some reason the database is running really slow and when I login to the mysql and do a show processlist command I see the below query that keeps running and running multiple times and is essentially killing the website.

SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_a'

I logged into the server via ssh and manually reindexed via the command line and that did not fix the issue. I also tried to manually replare each table in the database wusing mysql workbench however sill there is this query running over and over??

[Damian Culotta]

Hi @jayreis,

Did you found where the query is executed? (I've found something similar at the EAV module).

Also, are you using flat tables?

[jayreis]

No we are not using flat tables.  Also I did not find which (if any Module is making the query run.

However after reading through a bunch of posts on other sites and here I wound up manually running a mysql repair on each table in the magento database and then manually reindexed via ssh and then cleared cache via ssh  and now the site seems to be back up and running faster. 

would love  to know what caused this though.