MySql very slow count on 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 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:

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

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 result for count when I do it 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 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 query with profile:

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)

Indexes on tables:

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)

King Skippus · Accepted Answer

Have you tried using some STRAIGHT_JOINs instead of just plain JOINs to vary the order of the tables used in the JOIN? Sometimes the query optimizer doesn't pick the ideal order for a particular query. 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're using some heavy-duty conversion and casting on the msgrcpt table, what happens if you force it to be JOINed 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 around with the order more if you want, there might be a "magic order" that causes your query to zip. For example, another variation 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`

...and so on.

Also, in your SELECT columns, all of that casting is bound to kill your performance a bit, as it's having to do it for however many rows you're selecting, which could be a lot. Have you considered just adding a column to the msgrcpt table that is already calculated so that it won't have to do it in the query? You could add a trigger to the database to update it efficiently and automatically with the calculated value if you don't want to fool with having to account for the extra column in your code. Normally, using that kind of optimization is a bit of an overkill, but when doing a SELECT query on millions of rows, it might just do the trick.

EDIT: Here is what I would suggest in order to alter your msgrcpt table. It will make inserting rows slower by a very tiny amount since it's running a trigger to calculate the new column value, but I think it would pay off in speeding up the query you are trying to run by quite a bit. Standard disclaimer applies--do not run this on your production database without thoroughly testing it on a test copy first!

-- The UPDATE command will probably take some time to run since it's 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 that mangled mess of CONCATed CASTed stuff, just select mr.friendly_id (or whatever you choose to name the column). Your performance should be much better.

Hope this helps, and let me know how it goes!

OpenCode · Answer

Join needs BIG mysql load. In my expreience, create temporary table and select data from it, boost db query speed.

this is my code, code 2 shows 100 times fast speed. you can change your query code like code 2 style. for large and complex db query, use temporary table boost db query speed.

make temporary tables make query simple and reduce mysql db load.
select data from temporary table also needs 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 you temporary table's location in tempfs(memory area) speed also doubled.

MySql very slow count on query with joins

Tags:

sql

mysql

query-optimization

Aldarund

2 Answers

King Skippus

OpenCode

Recent Activity

Donate For Us

MySql very slow count on query with joins

Tags:

sql

mysql

query-optimization

Aldarund

2 Answers

King Skippus

OpenCode

Related questions

Recent Activity

Donate For Us