MySQL Forums
Forum List  »  Newbie

Re: MySQL is eating all the CPU 100% - Guide me to fix this please
Posted by: Mahesh Kumar
Date: October 23, 2017 11:11AM

This is the complete details



| questions | CREATE TABLE `questions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`question_direct` text COLLATE utf8mb4_unicode_ci NOT NULL,
`question_user` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`quiz_id` int(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `quiz_id` (`quiz_id`)
) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| choices | CREATE TABLE `choices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`question_id` int(10) unsigned NOT NULL,
`choice` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`points` int(5) unsigned NOT NULL,
`result_id` int(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `question_id` (`question_id`)
) ENGINE=MyISAM AUTO_INCREMENT=476 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| users | CREATE TABLE `users` (
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`random_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`country` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
`username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`join_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3258140 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| score | CREATE TABLE `score` (
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(5) unsigned NOT NULL,
`friend_id` int(5) unsigned NOT NULL,
`correct` int(5) unsigned NOT NULL,
`description` text COLLATE utf8mb4_unicode_ci NOT NULL,
`quiz_id` int(5) unsigned NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`,`friend_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4926153 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |


MariaDB [quiz]> explain SELECT `questions`.*, `user_question_answer`.*, GROUP_CONCAT(choices.id) as choice_id_array, GROUP_CONCAT(choices.choice) as choice_array, GROUP_CONCAT(choices.points) as points_array, GROUP_CONCAT(choices.result_id) as result_array FROM `questions` INNER JOIN `user_question_answer` ON `questions`.`id` = `user_question_answer`.`user_question_id` INNER JOIN `choices` ON `questions`.`id` = `choices`.`question_id` WHERE `user_id` = 1987 AND `user_question_answer`.`quiz_id` = 1 GROUP BY `questions`.`id`, `user_question_answer`.`id` ORDER BY RAND();

| user_question_answer | CREATE TABLE `user_question_answer` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(5) unsigned NOT NULL,
`user_question_id` int(5) unsigned NOT NULL,
`user_answer_id` int(5) unsigned NOT NULL,
`quiz_id` int(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=18264065 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |


+------+-------------+----------------------+--------+---------------+-------------+---------+--------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------------+--------+---------------+-------------+---------+--------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | user_question_answer | ref | user_id | user_id | 4 | const | 9 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | questions | eq_ref | PRIMARY | PRIMARY | 4 | quiz.user_question_answer.user_question_id | 1 | |
| 1 | SIMPLE | choices | ref | question_id | question_id | 4 | quiz.user_question_answer.user_question_id | 5 | |
+------+-------------+----------------------+--------+---------------+-------------+---------+--------------------------------------------+------+----------------------

MariaDB [quiz]> explain SELECT `questions`.`id`, `questions`.`question_direct`, `questions`.`question_user`, `questions`.`quiz_id`, GROUP_CONCAT(choices.id) as choice_id_array, GROUP_CONCAT(choices.choice) as choice_array, GROUP_CONCAT(choices.points) as points_array, GROUP_CONCAT(choices.result_id) as result_array FROM `questions` INNER JOIN `choices` ON `questions`.`id` = `choices`.`question_id` WHERE `quiz_id` = 1 GROUP BY `questions`.`id` ORDER BY RAND();
+------+-------------+-----------+------+-----------------+-------------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+-----------------+-------------+---------+-------------------+------+----------------------------------------------+
| 1 | SIMPLE | questions | ref | PRIMARY,quiz_id | quiz_id | 4 | const | 58 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | choices | ref | question_id | question_id | 4 | quiz.questions.id | 5 | |
+------+-------------+-----------+------+-----------------+-------------+---------+-------------------+------+----------------------------------------------+

MariaDB [quiz]> explain SELECT users.username as username, f.username as friend_name, score.* FROM `users` join score on users.id = score.user_id join users f on score.friend_id = f.id where users.id = 1994 AND score.quiz_id = 1 ORDER BY correct DESC;
+------+-------------+-------+--------+---------------+---------+---------+----------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+------+----------------+
| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | Using filesort |
| 1 | SIMPLE | score | ref | user_id | user_id | 4 | const | 16 | Using where |
| 1 | SIMPLE | f | eq_ref | PRIMARY | PRIMARY | 4 | quiz.score.friend_id | 1 | |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+------+----------------+

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 23, 2017 11:11AM


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.