MySQL Forums
Forum List  »  Newbie

Re: MySQL is eating all the CPU 100% - Guide me to fix this please
Posted by: Peter Brawley
Date: October 24, 2017 08:23AM

> i added quiz_id in index in questions table.

MySQL can use one index per table per query clause. I'd experiment with covering indexes like (id,quiz_id), (id,quiz_id),(question_id,quiz_id) etc to find the index combos that MyISAM likes best. Add a set of indexes, run explain to see if they're used, wait a day then run show variables and show global status to see if the problem numbers I mentioned have improved, adjust and repeat until the performance numbers are acceptable.

The aggregating queries also have invalidity issues. In a query ...

select a, b, c, d, group_concat(e) ... group by a

... the values returned for columns b, c ad d are entirely arbitrary except when values of b, c and d are all uniquely determined by values of a. A simple way to find if this is so is to add b,c,d to the Group By clause and compare results.



Edited 1 time(s). Last edit at 10/24/2017 10:15AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
November 07, 2017 09:51AM
Re: MySQL is eating all the CPU 100% - Guide me to fix this please
October 24, 2017 08:23AM


Sorry, only registered users may post in this forum.

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.