Re: MySQL is eating all the CPU 100% - Guide me to fix this please
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 | |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+------+----------------+