MySql very slowly counts on a query with joins

Question

I have a view for mysql:

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` ) )) 

When I try to count for this view, it will take about 13 minutes for 2.5 million records. It is incredibly slow. All fields have indexes. If I count on each table, it takes no more than 20 seconds. Here is what mysql explains:

 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 | +----+-------------+-------+--------+-----------------------------------------------------------------------+-------------------------+---------+-----------------------------------------------------------+---------+-------------+ 

How can I optimize the query / view, so it won't take 13 minutes to count. What happened to the current request?

UPDATE If I make a selective account directly when choosing without presentation, he will still receive the same request for 14 minutes.

 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) 

And here is the result for counting when I do this in three separate queries:

 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) 

UPDATE 2.

All tables are InnoDB.

 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 | +------------------------+-------+ 

The msgs and msgrcpt tables have a composite primary key (mail_id, partation_tag for msgs and ( partition_tag , mail_id , rseqnum ) for msgrpt). UPDATE Explain for single tables:

 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) 

UPDATE Create a table for all tables:

 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 | 

This profile request:

 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) 

Table Indices:

 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) 

MSGRCPT

 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) 

MADDR:

 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) 

Inno db buffer size

 mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 2147483648 | +-------------------------+------------+ 1 row in set (0.02 sec) 

Answer 1

Have you tried using some STRAIGHT_JOIN instead of simple JOINs to reorder tables used in JOINs? Sometimes the query optimizer does not select the ideal order for a particular request. In other words, what kind of performance do you see with this?

 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` STRAIGHT_JOIN `msgs` `m` ON `m`.`partition_tag` = `mr`.`partition_tag` AND `m`.`mail_id` = `mr`.`mail_id` STRAIGHT_JOIN `maddr` maddr ON `mr`.`rid` = `maddr`.`id` 

Or, since you are using some kind of heavy-duty conversion and casting in the msgrcpt table, what happens if you make it be CONCLUDED last in the query, like this?

 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 `maddr` `maddr` STRAIGHT_JOIN `msgrcpt` `mr` ON `maddr`.`id` = `mr.`rid` STRAIGHT_JOIN `msgs` `m` ON `m`.`partition_tag` = `mr`.`partition_tag` AND `m`.`mail_id` = `mr`.`mail_id` 

You can play with more order, if you want, there may be a “magic order” that makes your request freeze. For example, another option might be:

 FROM `msgs` `m` STRAIGHT_JOIN `msgrcpt` `mr` ON `m`.`partition_tag` = `mr`.`partition_tag` AND `m`.`mail_id` = `mr`.`mail_id` STRAIGHT_JOIN `maddr` ON `maddr`.`id` = `mr`.`rid` 

... etc.

In addition, in your SELECT columns, all of these castings should kill your performance a bit, as this needs to be done for any number of rows that you select, and this can be a lot. Have you considered simply adding a column to the msgrcpt table, which is already designed so that it will not need to do this in the query? You can add a trigger to the database in order to efficiently and automatically update it using the calculated value, if you do not want to deceive the need to take into account an additional column in your code. Typically, using this kind of optimization is a bit overkill, but when executing a SELECT query over millions of rows, this might just do the trick.

EDIT: Here is what I would suggest to modify your msgrcpt table. This will cause row insertion to be slower with a very small amount, since it fires a trigger to calculate the new column value, but I think it will pay off by speeding up the query that you are trying to run quite a bit. The standard disclaimer is not to run this in your production database without first checking it on a test copy!

 -- The UPDATE command will probably take some time to run since it updating -- millions of rows. Be patient! ALTER TABLE `msgrcpt` ADD COLUMN `friendly_id` TEXT NULL AFTER `rs`; UPDATE `msgrcpt` SET `friendly_id` = CONCAT(CAST(`mail_id` AS CHAR(255) CHARSET utf8), CAST(`partition_tag` AS CHAR(255) CHARSET utf8)); DELIMITER $$ CREATE TRIGGER `trig_calc_id` BEFORE INSERT ON `msgrcpt` FOR EACH ROW BEGIN SET NEW.`friendly_id` = CONCAT(CAST(NEW.`mail_id` AS CHAR(255) CHARSET utf8), CAST(NEW.`partition_tag` AS CHAR(255) CHARSET utf8)); END $$ CREATE TRIGGER `trig_update_id` BEFORE UPDATE ON `msgrcpt` FOR EACH ROW BEGIN SET NEW.`friendly_id` = CONCAT(CAST(NEW.`mail_id` AS CHAR(255) CHARSET utf8), CAST(NEW.`partition_tag` AS CHAR(255) CHARSET utf8)); END $$ DELIMITER ; 

Now, instead of selecting this garbled mess from the CONCATED CASTed content, simply select mr.friendly_id (or whatever you name in the column). Your performance should be much better.

Hope this helps, and let me know how this happens!

Answer 2

There are several methods on how databases perform table joins. MySQL only uses Nested-loops to join, as indicated in the section on EXPLAIN output . The way Nested-loops works is the key to understanding why this request is time consuming.

According to your EXPLAIN output, MySQL first scans the maddr table, so you have a loop of 1 016 497 executions. Inside each run database, it scans the index on tiny tables twice. Even if the cost:

• initiating a new index scan;
• search for entries in the index;
• scan completion

really small (since the data is likely to be cached using many executions), in any case, it takes a few milliseconds. Then you multiply by 2 , since msgrcpt and msgs scanned in the inside. And then multiply by the number of executions of the outer loop, which gives you enough time.

Your definition definition does not have filters on the returned data, so all tables must be scanned and joined. Given how Nested loops work, it’s clear that the option where the outer query contains the least number of loops is the most efficient. Taking into account the size of all tables, index scanning (all 3 tables are scanned by indexes in accordance with EXPLAIN output):

• msgrcpt : log ₂ (2587307) + 1 = 22 pages,
• msgs : log ₂ (2421710) + 1 = 22 pages and
• maddr : log ₂ (994880) + 1 = 21 pages for each scan

at worst. This shows that the table scan time is comparable and very significant, even with index scans. However, maddr smaller, and for this very reason MySQL decided to scan it first.

As I see it, the plan chosen by the database is the best in this situation. And the fact that MySQL supports only the union of nested loops does not allow optimization, since the outer loop is already the smallest. Hash join may have been used here for better performance.

---

So how to do things in this situation?

I recommend using only query viewing with filters, which will give MySQL more chances to optimize the query and quickly return data.

To get the number of rows, I would go for an estimate (if that was acceptable) by calculating count(*) for each individual table and accepting the largest.

Another way is to reverse engineer the circuit by making one of the tables main so that the number of rows in the view always matches the number of rows in the main table. I'm not sure how to do this with this design, sorry.

If none of the options fits, unfortunately, I do not see other ways to optimize this query.

Answer 3

I agree with King Skippus regarding the two elements ... STRAIGHT_JOIN to help overthrow the optimizer execution plan ... And the triggers to save the pre-aggregates are for you. HOWEVER, if you ever want to use the WHERE clause to restrict, you are stuck.

In addition, if all you care about is COUNT, then vyegorov commenting to throw away all the columns would be better. Due to the fact that all other columns force data to be retrieved from all actual data pages.

I would make my request as

 SELECT STRAIGHT_JOIN COUNT(*) FROM msgrcpt mr JOIN msgs m ON mr.mail_id = m.mail_id AND mr.partition_tag = m.partition_tag JOIN maddr ON mr.rid = maddr.id 

and I know that you mentioned indexes, but I would index the MsgRcpt table through (mail_id, partition_tag) the index of the Msgs table through the same (mail_id, partition_tag) MAddr index on (id)

Now, why is one way against another by index? I would try to do based on the smallest aggregate in the first index field. Not knowing exactly what your volume basis is (mail id, section tag), but take a sample customer ordering system. You can have 10,000 people who order something in one day, but for a given person, one or two orders on a certain day. It would be easier to find a person in the first place and this individual date compared to one day and up to one person. This MAY affect query performance by trying to first view the LARGE list and then small or small / small.

Performing only COUNT (*), you get exactly this number. Additional columns from the raw table are not needed, and each page is loaded, since only indexed components need indexed components to qualify the records in the results.

If you need all the data, then I would do the same indicative considerations, but you have a query like ...

 CREATE VIEW loggingquarantine_quarantine ( id, mail_id, partition_tag, content, rs, subject, sender, TIME, spam_level, size, sid, email ) AS SELECT STRAIGHT_JOIN concat(CAST(mr.mail_id AS CHAR(255) charset utf8), CAST(mr.partition_tag AS CHAR(255) charset utf8)) AS `id`, mr.mail_id, mr.partition_tag, mr.content, mr.rs, m.subject, m.from_addr AS sender, m.time_num AS `TIME`, m.spam_level, m.size, m.sid, maddr.email FROM msgrcpt mr JOIN msgs m ON mr.mail_id = m.mail_id AND mr.partition_tag = m.partition_tag JOIN maddr ON mr.rid = maddr.id 

Answer 4

Performance may be affected by a request to view itselt, as well as using the view in your code. For more information, read the article - MySQL VIEW as a Performance Impactor .

Workarounds:

• Perform a simple query, do not use the view.
• If you do not want to use this view in other SELECT statements as a dataset, you can try to create a stored procedure instead of a view.

Answer 5

Try using COUNT (*) instured COUNT (1) because the MySQL optimizer is pretty buggy http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

Answer 6

Sorry: I see that your maddr table has the partition_tag identifier, but this partition tag is not used in the last JOIN making up VIEW. Is this how it should be?

In any case, it seems to me that your request can benefit from the maddr index, which contains both id and email. This should allow retrieving emails with fewer I / O directly from the index (it would probably be useful to evaluate the performance of various indexes - it seems to me that the index only on the email field, for example, was rarely used, but it depends on the workflow, which you know, but I do not, so I could very well be mistaken.

Answer 7

Write the primary key column in count() to make it faster. count(id)

Answer 8

Good :), so, on the one hand, you said that we have 2.5 million records, on the other hand (in your opinion) count returns 4279394 (which is slightly higher :)). A possible reason (sorry, almost 3am :)) - you did not specify the full primary key msgrcpt (rseqnum is missing), so if the optimizer selects maddr or msgs as the primary table, you can end up writing msgrcpt. Also, based on the difference in counting time for msgrcpt and msgs, I highly recommend defragmenting msgrcpt. I believe that after defragmenting and using (already recommended) a direct connection, your problem will disappear (if not - maybe you can find a way to calculate on the fly - all you need to calculate for rseqnum / spam_level, OR, if spam_level is the same for the full message - perhaps you could limit the selection at the very beginning by providing a hard-coded rseqnum value.

Answer 9

The connection requires a lot of mysql load. In my experience, create a temporary table and select data from it, increase the speed of db queries.

this is my code, code 2 shows the speed 100 times faster. you can change the request code, for example, code 2. for a large and complex db request, use the temporary speed of the db request request.

• make temporary tables, make the query simple and reduce the load of mysql db.
• selecting data from a temporary table also requires less db load.

code 1

  $sql = " select distinct wr_parent from $write_table where $sql_search "; $result = sql_query($sql); $total_count = mysql_num_rows($result); 

code 2

  $sql = " select wr_parent from $write_table where $sql_search "; $sql_tmp = " create TEMPORARY table list_tmp_count as $sql "; $sql_ord = " select distinct wr_parent from list_tmp_count "; @mysql_query($sql_tmp) or die("<p>$sql_tmp<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]"); $result = @mysql_query($sql_ord) or die("<p>$sql_ord<p>" . mysql_errno() . " : " . mysql_error() . "<p>error file : $_SERVER[PHP_SELF]"); $total_count = mysql_num_rows($result); 

mysql temporary table

and if the temporary location of the table in tempfs (memory area) also doubles.