StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySql very slow count on query with joins
primarykey
Id
11253996
data
AcceptedAnswerId
0
AnswerCount
9
ClosedDate
CommentCount
20
CommunityOwnedDate
CreationDate
2012-06-28T22:50:00.390
FavoriteCount
2
LastActivityDate
2012-07-08T07:57:42.540
LastEditDate
2012-07-04T08:21:39.130
LastEditorUserId
239354
OwnerUserId
239354
ParentId
0
PostTypeId
1
Score
6
ViewCount
2952
LastEditorDisplayName
text
Body
I have a view for mysql : <pre><code>CREATE VIEW loggingquarantine_quarantine ( id, mail_id, partition_tag, content, rs, subject, sender, TIME, spam_level, size, sid, email ) AS SELECT concat(CAST(`mr`.`mail_id` AS CHAR(255) charset utf8),CAST(`mr`.`partition_tag` AS CHAR(255) charset utf8)) AS `id`, `mr`.`mail_id` AS `mail_id`, `mr`.`partition_tag` AS `partition_tag`, `mr`.`content` AS `content`, `mr`.`rs` AS `rs`, `m`.`subject` AS `subject`, `m`.`from_addr` AS `sender`, `m`.`time_num` AS `TIME`, `m`.`spam_level` AS `spam_level`, `m`.`size` AS `size`, `m`.`sid` AS `sid`, `maddr`.`email` AS `email` FROM (((`msgrcpt` `mr` JOIN `msgs` `m` ON ( `m`.`partition_tag` = `mr`.`partition_tag` AND `m`.`mail_id` = `mr`.`mail_id` ) ) JOIN `maddr` maddr ON ( `mr`.`rid` = `maddr`.`id` ) )) </code></pre> When I try to make a count for this view it take about 13min for 2.5 million of record. Thats incredibly slow. All fields have indexes. If I do count on each table it take no longer than 20seconds. Here is what mysql explain shows: <pre><code>mysql> explain SELECT COUNT(*) FROM `loggingquarantine_quarantine`; +----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+ | 1 | SIMPLE | maddr | index | PRIMARY | maddr_partition_tag_idx | 5 | NULL | 1016497 | Using index | | 1 | SIMPLE | mr | ref | PRIMARY,msgrcpt_idx_rid,msgrcpt_mail_id_idx,msgrcpt_partition_tag_idx | msgrcpt_idx_rid | 8 | mroute_logquar.maddr.id | 2 | Using index | | 1 | SIMPLE | m | eq_ref | PRIMARY,msgs_mail_id_idx,msgs_partition_tag_idx | PRIMARY | 22 | mroute_logquar.mr.partition_tag,mroute_logquar.mr.mail_id | 1 | Using index | +----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+ </code></pre> How can I optimize the query/view so it will not take 13 minutes to make a count.. What's wrong with a current query? UPDATE. If I do a select count directly on select without view its still get same 14 min query. <pre><code>mysql> select count(1) FROM (((`msgrcpt` `mr` JOIN `msgs` `m` ON ( `m`.`partition_tag` = `mr`.`partition_tag` AND `m`.`mail_id` = `mr`.`mail_id` ) ) JOIN `maddr` maddr ON ( `mr`.`rid` = `maddr`.`id` ) )); +----------+ | count(1) | +----------+ | 2582227 | +----------+ 1 row in set (14 min 28.96 sec) </code></pre> And here is result for count when I do it in three separate queries : <pre><code>mysql> select count(1) from msgrcpt; +----------+ | count(1) | +----------+ | 2587307 | +----------+ 1 row in set (46.02 sec) mysql> select count(1) from msgs; +----------+ | count(1) | +----------+ | 2421710 | +----------+ 1 row in set (7.77 sec) mysql> select count(1) from maddr; +----------+ | count(1) | +----------+ | 994880 | +----------+ 1 row in set (0.23 sec) </code></pre> UPDATE 2. All tables are InnoDB. <pre><code>mysql> SHOW status like 'key_%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 26792 | | Key_blocks_used | 0 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | +------------------------+-------+ </code></pre> The msgs and msgrcpt tables have a composite primary key ( mail_id, partation_tag for msgs and (<code>partition_tag</code>,<code>mail_id</code>,<code>rseqnum</code>) for msgrpt). UPDATE Explain for single tables: <pre><code>mysql> explain select count(1) from msgs; +----+-------------+-------+-------+---------------+-------------------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------------------+---------+------+---------+-------------+ | 1 | SIMPLE | msgs | index | NULL | msgs_idx_time_num | 4 | NULL | 2357360 | Using index | +----+-------------+-------+-------+---------------+-------------------+---------+------+---------+-------------+ 1 row in set (0.00 sec) mysql> explain select count(1) from msgrcpt; +----+-------------+---------+-------+---------------+----------------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+----------------+---------+------+---------+-------------+ | 1 | SIMPLE | msgrcpt | index | NULL | msgrcpt_rs_idx | 3 | NULL | 2620758 | Using index | +----+-------------+---------+-------+---------------+----------------+---------+------+---------+-------------+ 1 row in set (0.00 sec) mysql> explain select count(1) from maddr; +----+-------------+-------+-------+---------------+-------------------------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------------------------+---------+------+--------+-------------+ | 1 | SIMPLE | maddr | index | NULL | maddr_partition_tag_idx | 5 | NULL | 967058 | Using index | +----+-------------+-------+-------+---------------+-------------------------+---------+------+--------+-------------+ 1 row in set (0.00 sec) </code></pre> UPDATE. Create table for all tables: <pre><code>mysql> show create table msgrcpt; | Table | Create Table | msgrcpt | CREATE TABLE `msgrcpt` ( `partition_tag` int(11) NOT NULL DEFAULT '0', `mail_id` varbinary(16) NOT NULL, `rseqnum` int(11) NOT NULL DEFAULT '0', `rid` bigint(20) unsigned NOT NULL, `is_local` char(1) NOT NULL DEFAULT '', `content` char(1) NOT NULL DEFAULT '', `ds` char(1) NOT NULL, `rs` char(1) NOT NULL, `bl` char(1) DEFAULT '', `wl` char(1) DEFAULT '', `bspam_level` float DEFAULT NULL, `smtp_resp` varchar(255) DEFAULT '', PRIMARY KEY (`partition_tag`,`mail_id`,`rseqnum`), KEY `msgrcpt_idx_rid` (`rid`), KEY `msgrcpt_mail_id_idx` (`mail_id`), KEY `msgrcpt_rs_idx` (`rs`), KEY `msgrcpt_ds_idx` (`ds`), KEY `msgrcpt_partition_tag_idx` (`partition_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | | msgs | CREATE TABLE `msgs` ( `partition_tag` int(11) NOT NULL DEFAULT '0', `mail_id` varbinary(16) NOT NULL, `secret_id` varbinary(16) DEFAULT '', `am_id` varchar(20) NOT NULL, `time_num` int(10) unsigned NOT NULL, `time_iso` char(16) NOT NULL, `sid` bigint(20) unsigned NOT NULL, `policy` varchar(255) DEFAULT '', `client_addr` varchar(255) DEFAULT '', `size` int(10) unsigned NOT NULL, `originating` char(1) NOT NULL DEFAULT '', `content` char(1) DEFAULT NULL, `quar_type` char(1) DEFAULT NULL, `quar_loc` varbinary(255) DEFAULT '', `dsn_sent` char(1) DEFAULT NULL, `spam_level` float DEFAULT NULL, `message_id` varchar(255) DEFAULT '', `from_addr` varchar(255) DEFAULT '', `subject` varchar(255) DEFAULT '', `host` varchar(255) NOT NULL, PRIMARY KEY (`partition_tag`,`mail_id`), KEY `msgs_idx_sid` (`sid`), KEY `msgs_idx_mess_id` (`message_id`), KEY `msgs_idx_time_num` (`time_num`), KEY `msgs_mail_id_idx` (`mail_id`), KEY `msgs_partition_tag_idx` (`partition_tag`), KEY `msgs_content_idx` (`content`), FULLTEXT KEY `ft_from_addr` (`from_addr`), FULLTEXT KEY `ft_subject` (`subject`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | | maddr | CREATE TABLE `maddr` ( `partition_tag` int(11) DEFAULT '0', `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `email` varbinary(255) NOT NULL, `domain` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `part_email` (`partition_tag`,`email`), KEY `maddr_email_idx` (`email`), KEY `maddr_partition_tag_idx` (`partition_tag`) ) ENGINE=InnoDB AUTO_INCREMENT=3373444 DEFAULT CHARSET=utf8 | </code></pre> This query with profile: <pre><code>mysql> SET PROFILING=1; SELECT Query OK, 0 rows affected (0.00 sec) -> count(1) -> FROM -> (((`msgrcpt` `mr` JOIN `msgs` `m` -> ON -> ( -> `m`.`partition_tag` = `mr`.`partition_tag` -> AND -> `m`.`mail_id` = `mr`.`mail_id` -> ) -> ) -> JOIN `maddr` maddr -> ON -> ( -> `mr`.`rid` = `maddr`.`id` -> ) -> )); SHOW PROFILE ALL; +----------+ | count(1) | +----------+ | 4279394 | +----------+ 1 row in set (23 min 56.61 sec) +----------------------+------------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+ | Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line | +----------------------+------------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+ | starting | 0.000161 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | | checking permissions | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | check_access | sql_parse.cc | 5043 | | checking permissions | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5043 | | checking permissions | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5043 | | Opening tables | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5014 | | System lock | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 304 | | init | 0.000040 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 1041 | | optimizing | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 138 | | statistics | 0.000063 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 358 | | preparing | 0.000032 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 470 | | executing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 137 | | Sending data | 999.999999 | 97.014251 | 10.376423 | 681167 | 25822 | 5157072 | 1951032 | 0 | 0 | 4 | 277 | 0 | execute | sql_executor.cc | 758 | | end | 0.000106 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 1071 | | query end | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4761 | | closing tables | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4809 | | freeing items | 0.000030 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5997 | | logging slow query | 0.000059 | 0.000000 | 0.000000 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | log_slow_statement | sql_parse.cc | 1720 | | cleaning up | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1654 | +----------------------+------------+-----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+ 18 rows in set (0.02 sec) </code></pre> Indexes on tables: <pre><code>mysql> show index from msgs; +-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | msgs | 0 | PRIMARY | 1 | partition_tag | A | 16 | NULL | NULL | | BTREE | | | | msgs | 0 | PRIMARY | 2 | mail_id | A | 4174440 | NULL | NULL | | BTREE | | | | msgs | 1 | msgs_idx_sid | 1 | sid | A | 2087220 | NULL | NULL | | BTREE | | | | msgs | 1 | msgs_idx_mess_id | 1 | message_id | A | 4174440 | NULL | NULL | YES | BTREE | | | | msgs | 1 | msgs_idx_time_num | 1 | time_num | A | 1391480 | NULL | NULL | | BTREE | | | | msgs | 1 | msgs_mail_id_idx | 1 | mail_id | A | 4174440 | NULL | NULL | | BTREE | | | | msgs | 1 | msgs_partition_tag_idx | 1 | partition_tag | A | 16 | NULL | NULL | | BTREE | | | | msgs | 1 | msgs_content_idx | 1 | content | A | 16 | NULL | NULL | YES | BTREE | | | | msgs | 1 | ft_from_addr | 1 | from_addr | NULL | 4174440 | NULL | NULL | YES | FULLTEXT | | | | msgs | 1 | ft_subject | 1 | subject | NULL | 4174440 | NULL | NULL | YES | FULLTEXT | | | +-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 10 rows in set (0.97 sec) </code></pre> MSGRCPT <pre><code>mysql> show index from msgrcpt; +---------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | msgrcpt | 0 | PRIMARY | 1 | partition_tag | A | 29 | NULL | NULL | | BTREE | | | | msgrcpt | 0 | PRIMARY | 2 | mail_id | A | 5218535 | NULL | NULL | | BTREE | | | | msgrcpt | 0 | PRIMARY | 3 | rseqnum | A | 5218535 | NULL | NULL | | BTREE | | | | msgrcpt | 1 | msgrcpt_idx_rid | 1 | rid | A | 347902 | NULL | NULL | | BTREE | | | | msgrcpt | 1 | msgrcpt_mail_id_idx | 1 | mail_id | A | 5218535 | NULL | NULL | | BTREE | | | | msgrcpt | 1 | msgrcpt_rs_idx | 1 | rs | A | 29 | NULL | NULL | | BTREE | | | | msgrcpt | 1 | msgrcpt_ds_idx | 1 | ds | A | 29 | NULL | NULL | | BTREE | | | | msgrcpt | 1 | msgrcpt_partition_tag_idx | 1 | partition_tag | A | 29 | NULL | NULL | | BTREE | | | +---------+------------+---------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 8 rows in set (0.70 sec) </code></pre> MADDR: <pre><code>mysql> show index from maddr; +-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | maddr | 0 | PRIMARY | 1 | id | A | 1653970 | NULL | NULL | | BTREE | | | | maddr | 0 | part_email | 1 | partition_tag | A | 19 | NULL | NULL | YES | BTREE | | | | maddr | 0 | part_email | 2 | email | A | 1653970 | NULL | NULL | | BTREE | | | | maddr | 1 | maddr_email_idx | 1 | email | A | 1653970 | NULL | NULL | | BTREE | | | | maddr | 1 | maddr_partition_tag_idx | 1 | partition_tag | A | 19 | NULL | NULL | YES | BTREE | | | +-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.41 sec) </code></pre> Inno db buffer size <pre><code>mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 2147483648 | +-------------------------+------------+ 1 row in set (0.02 sec) </code></pre>
Tags
<mysql><sql><query-optimization>
Title
MySql very slow count on query with joins
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USAldarund
UserOwnerUserId
1. USAldarund
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POMySql very slow count on query with joins
 UserUserId
 USManachi
 VoteTypeVoteTypeId
 VTFavorite
2. VO
 singulars
 PostPostId
 POMySql very slow count on query with joins
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POMySql very slow count on query with joins
 UserUserId
 USAldarund
 VoteTypeVoteTypeId
 VTBountyStart
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.