0

I searched on some forums, perconna and more but i didn't find answers.

We have a legacy server with Mysql 5.0 and some tables on Myisam.

I'm trying to optimize my request.

We have 3 tables

mysql> desc RD3_surveyed;
+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| id                   | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| status               | int(11)          | YES  |     | NULL    |                |
| reserved             | tinyint(1)       | YES  |     | NULL    |                |
| failure_reason_id    | int(11)          | YES  | MUL | NULL    |                |
| blocked              | tinyint(1)       | YES  |     | NULL    |                |
| priority             | tinyint(1)       | YES  |     | NULL    |                |
| failure_comment      | text             | YES  |     | NULL    |                |
| recall_id            | int(11)          | YES  | MUL | NULL    |                |
| try_number           | int(11)          | YES  |     | NULL    |                |
| do_by_user_id        | int(11)          | YES  |     | NULL    |                |
| started_at           | datetime         | YES  |     | NULL    |                |
| duration             | int(11)          | YES  |     | NULL    |                |
| key                  | varchar(255)     | YES  |     | NULL    |                |
| surveyed_database_id | int(11)          | YES  | MUL | NULL    |                |
| blocked_evidence     | tinyint(1)       | YES  |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+

mysql> desc RD3_surveyed_attribute_value;
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| id                  | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| survey_attribute_id | int(11)          | YES  | MUL | NULL    |                |
| value               | varchar(255)     | YES  |     | NULL    |                |
| surveyed_id         | int(11)          | YES  | MUL | NULL    |                |
| is_correct          | tinyint(1)       | YES  |     | NULL    |                |
+---------------------+------------------+------+-----+---------+----------------+

mysql> desc RD3_survey_attribute;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| survey_id | int(11)          | YES  | MUL | NULL    |                |
| name      | varchar(255)     | YES  |     | NULL    |                |
| type      | varchar(100)     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

mysql> select count(*) from RD3_surveyed_attribute_value;
+----------+
| count(*) |
+----------+
|  1988928 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from RD3_survey_attribute;
+----------+
| count(*) |
+----------+
|       36 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from RD3_surveyed;
+----------+
| count(*) |
+----------+
|    55248 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE COUNT(DISTINCT s.id) FROM RD3_surveyed s LEFT JOIN RD3_surveyed_attribute_value sav ON (sav.surveyed_id = s.id) LEFT JOIN RD3_survey_attribute sa ON (sa.survey_id = sav.survey_attribute_id) WHERE s.surveyed_database_id = 1;
+----------------------+
| COUNT(DISTINCT s.id) |
+----------------------+
|                55248 |
+----------------------+
1 row in set (11.60 sec)

11.60sec is pretty big and i need to optimize this request, could you help us.

Thanks and regards

Thomas


Sorry, i don't specify that i need to keep the complete request as it's a base request of a view, the count is only to get total.

Here an example of another request

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS s.id, sav.value, sa.id  FROM RD3_surveyed s  LEFT JOIN RD3_surveyed_attribute_value sav ON (sav.surveyed_id = s.id)  LEFT JOIN RD3_survey_attribute sa ON (sa.survey_id = sav.survey_attribute_id)  WHERE s.surveyed_database_id = 1 LIMIT 0,30;

30 rows in set (14.63 sec)

Here show create table

| RD3_surveyed | CREATE TABLE `RD3_surveyed` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `status` int(11) default NULL,
  `reserved` tinyint(1) default NULL,
  `failure_reason_id` int(11) default NULL,
  `blocked` tinyint(1) default NULL,
  `priority` tinyint(1) default NULL,
  `failure_comment` text,
  `recall_id` int(11) default NULL,
  `try_number` int(11) default NULL,
  `do_by_user_id` int(11) default NULL,
  `started_at` datetime default NULL,
  `duration` int(11) default NULL,
  `key` varchar(255) default NULL,
  `surveyed_database_id` int(11) default NULL,
  `blocked_evidence` tinyint(1) default NULL,
  PRIMARY KEY  (`id`),
  KEY `surveyed_database_id` (`surveyed_database_id`),
  KEY `failure_reason_id` (`failure_reason_id`),
  KEY `recall_id` (`recall_id`)
) ENGINE=MyISAM AUTO_INCREMENT=55249 DEFAULT CHARSET=latin1

| RD3_survey_attribute | CREATE TABLE `RD3_survey_attribute` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `survey_id` int(11) default NULL,
  `name` varchar(255) default NULL,
  `type` varchar(100) default NULL,
  PRIMARY KEY  (`id`),
  KEY `survey_id` (`survey_id`)
) ENGINE=MyISAM AUTO_INCREMENT=37 DEFAULT CHARSET=latin1 |

| RD3_surveyed_attribute_value | CREATE TABLE `RD3_surveyed_attribute_value` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `survey_attribute_id` int(11) default NULL,
  `value` varchar(255) default NULL,
  `surveyed_id` int(11) default NULL,
  `is_correct` tinyint(1) default NULL,
  PRIMARY KEY  (`id`),
  KEY `surveyed_id` (`surveyed_id`),
  KEY `survey_attribute_id` (`survey_attribute_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1988929 DEFAULT CHARSET=latin1 |

And finally explain

EXPLAIN SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS s.id, sav.value, sa.id  FROM RD3_surveyed s  LEFT JOIN RD3_surveyed_attribute_value sav ON (sav.surveyed_id = s.id)  LEFT JOIN RD3_survey_attribute sa ON (sa.survey_id = sav.survey_attribute_id)  WHERE s.surveyed_database_id = 1 LIMIT 0,30;
+----+-------------+-------+------+----------------------+----------------------+---------+-----------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref                               | rows  | Extra       |
+----+-------------+-------+------+----------------------+----------------------+---------+-----------------------------------+-------+-------------+
|  1 | SIMPLE      | s     | ref  | surveyed_database_id | surveyed_database_id | 5       | const                             | 55246 | Using where |
|  1 | SIMPLE      | sav   | ref  | surveyed_id          | surveyed_id          | 5       | KIOSC_RD3.s.id                    |    36 |             |
|  1 | SIMPLE      | sa    | ref  | survey_id            | survey_id            | 5       | KIOSC_RD3.sav.survey_attribute_id |     4 |             |
+----+-------------+-------+------+----------------------+----------------------+---------+-----------------------------------+-------+-------------+

  • 1) Replace table's DESCs with their CREATE TABLE. 2) Show full query text and its EXPLAIN. – Akina May 30 at 13:17
0

Source query:

SELECT SQL_NO_CACHE COUNT(DISTINCT s.id) 
FROM RD3_surveyed s 
LEFT JOIN RD3_surveyed_attribute_value sav ON (sav.surveyed_id = s.id) 
LEFT JOIN RD3_survey_attribute sa ON (sa.survey_id = sav.survey_attribute_id) 
WHERE s.surveyed_database_id = 1;

is equal to

SELECT SQL_NO_CACHE COUNT(DISTINCT id) 
FROM RD3_surveyed 
WHERE surveyed_database_id = 1;

The index by (surveyed_database_id) or (surveyed_database_id, id) may help also.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.