Форум пользователей MySQL

vepr85 · 21.01.2011 11:47:00

Всем добрый день. У меня в проекте есть 1 таблица, с которой я работаю. После изменений в логике, пришлось переделать 2 запроса, и они стали жутко долго выполняться.
Собственно лучше сразу в код:

сама таблица

CREATE TABLE talkidb.chataudit (

  id BIGINT(20) AUTO_INCREMENT,

  corrId BIGINT(20) DEFAULT NULL,

  contactFK BIGINT(20) DEFAULT NULL,

  domainFK BIGINT(20) DEFAULT NULL,

  subscriberFK BIGINT(20) DEFAULT NULL,

  operator VARCHAR(255) DEFAULT NULL,

  eventType VARCHAR(255) NOT NULL,

  `text` TEXT DEFAULT NULL,

  lastUpdate DATETIME NOT NULL,

  artGalleryFK BIGINT(20) DEFAULT NULL,

  sessionId BIGINT(20) DEFAULT NULL,

  PRIMARY KEY (id),

  INDEX chataudit_subscribercontact USING BTREE (contactFK),

  INDEX chataudit_domains USING BTREE (domainFK),

  INDEX chataudit_subscriber USING BTREE (subscriberFK),

  INDEX chataudit_chatartgallery USING BTREE (artGalleryFK),

  INDEX chataudit_event_type USING BTREE (eventType),

  INDEX chataudit_last_update USING BTREE (lastUpdate),

  INDEX index1 USING BTREE (corrId),

  INDEX index2 USING BTREE (sessionId),

  CONSTRAINT chataudit_chatartgallery FOREIGN KEY (artGalleryFK)

    REFERENCES talkidb.chatartgallery(id) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT chataudit_domains FOREIGN KEY (domainFK)

    REFERENCES talkidb.domain(id) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT chataudit_subscriber FOREIGN KEY (subscriberFK)

    REFERENCES talkidb.subscriber(id) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT chataudit_subscribercontact FOREIGN KEY (contactFK)

    REFERENCES talkidb.subscribercontact(id) ON DELETE CASCADE ON UPDATE CASCADE

)

ENGINE = INNODB

AUTO_INCREMENT = 5011910

AVG_ROW_LENGTH = 160

CHARACTER SET utf8 COLLATE utf8_general_ci;

1 запрос

select

         sum(timestampdiff(second ,CH1.lastUpdate,CH2.lastUpdate)) as session_time,

     sum(( select count(eventType)from chataudit as CA1 where

         CA1.eventType = 'OP_MESSAGE' AND CA1.operator='testop2' and

          CA1.lastUpdate > CH1.lastUpdate and CA1.lastUpdate < CH2.lastUpdate

       )) as sent_message_count,

        sum(( select count(eventType)

                from chataudit as CA2

                where

                        CA2.eventType = 'MSG_Q_SERVED' AND CA2.operator='testop2' and

                        CA2.lastUpdate > CH1.lastUpdate and CA2.lastUpdate < CH2.lastUpdate

          )) as recieved_message_count

from chataudit as CH1

       inner join chataudit as CH2 on    CH1.eventType='OP_LOGIN' and

       CH2.eventType='OP_LOGOUT' and

       CH2.lastUpdate = (select min(INCH.lastUpdate) from chataudit as INCH where INCH.eventType='OP_LOGOUT' and INCH.lastUpdate>CH1.lastUpdate)

where

      CH1.operator='norgeop23' and

      CH1.lastUpdate>'2011-01-19' and CH2.lastUpdate<'2011-01-21';

2 запрос

select ch1.id as id,ch1.operator as operator,

       ch1.lastUpdate as signInTime,ch2.lastUpdate as signOutTime,

       timestampdiff(second ,ch1.lastUpdate,ch2.lastUpdate) as session_time,

       (select count(*)

           from

              chataudit as inch

           where inch.eventType='OP_MESSAGE' and

                 inch.lastUpdate > ch1.lastUpdate and inch.lastUpdate < ch2.lastUpdate and

                 inch.operator like 'testop2'

        ) as total_sent_Msgs,

       (select count(*)

        from

            chataudit as inch

        where eventType='MSG_Q_SERVED' and

              inch.lastUpdate > ch1.lastUpdate and inch.lastUpdate < ch2.lastUpdate and

              inch.operator like 'testop2'

        ) as total_answ_Msgs

from chataudit ch1

     inner join chataudit as ch2 on ch1.eventType='OP_LOGIN' and ch2.eventType='OP_LOGOUT'and

       ch2.lastUpdate = (select min(INCH.lastUpdate)

                       from chataudit as INCH

                       where

                            INCH.eventType='OP_LOGOUT' and INCH.lastUpdate > ch1.lastUpdate)

where

      ch1.lastUpdate > '2011/01/20' and ch2.lastUpdate < '2011/01/21' and

      ch1.operator like 'testop2'

order by ch1.lastUpdate limit 0,20;

paulus · 22.01.2011 21:42:15

Я такие запросы в голове удержать не могу

EXPLAIN от них покажите, пожалуйста.

vepr85 · 23.01.2011 10:39:41

В архиве оба explain'a.
Меня смущают подзапросы. Из-за них скорее всего идёт основное замедление

vepr85 · 23.01.2011 10:45:19

bug

paulus · 23.01.2011 14:49:51

Не вижу ни архива, ни бага

vepr85 · 23.01.2011 16:47:58

не отправляется архив со скринами

Прикрепленные файлы:

Pictures.rar, Размер: 94,005 байт, Скачано: 484

paulus · 23.01.2011 23:44:33

Кошмаар Давайте итеративно, я всё равно в голове это не удержу.

Подзапросу нужно, чтобы Вы поменяли INDEX chataudit_event_type на
(eventType, operator, lastUpdate). Ну и для второго запроса нужен будет
(eventType, lastUpdate).

P.S. Да, и возьмите клиент, из которого Вы можете копировать текстом
результат работы. Грузить картинки в раре — грустно.

vepr85 · 24.01.2011 10:43:27

Выгрузил в html в архиве:

Прикрепленные файлы:

Explain_Queries_export_html.7z, Размер: 1,219 байт, Скачано: 448

vepr85 · 24.01.2011 12:44:06

Как отобрать сумму в подзапросе, чтобы не работать со всей таблицей(на это времени много уходит)?

я про вот эти 2 кусочка:

считается сумма, проходя по всем данным таблицы(там много записей ~ 2 500 000)

надо сделать так, чтобы эта сумма считалась только по выбранному набору данных, ограниченному условиями внешнего select

sum(( select count(eventType)from chataudit as CA1 where

         CA1.eventType = 'OP_MESSAGE' AND CA1.operator='testop2' and

          CA1.lastUpdate > CH1.lastUpdate and CA1.lastUpdate < CH2.lastUpdate

       )) as sent_message_count,

sum(( select count(eventType)

                from chataudit as CA2

                where

                        CA2.eventType = 'MSG_Q_SERVED' AND CA2.operator='testop2' and

                        CA2.lastUpdate > CH1.lastUpdate and CA2.lastUpdate < CH2.lastUpdate

       )) as recieved_message_count

paulus · 24.01.2011 12:46:44

Кошмар. Я имел в виду не сжатый HTML, а нормальный клиент. Ну, вот, например:

Код:

mysql> EXPLAIN SELECT 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Правда — это и читать просто, и на форум легко влезает. Стандартный консольный
клиент mysql это умеет.

Индексы то сделали?

vepr85 · 24.01.2011 13:08:58

Разобрался, только на экран не помещается
1:

+----+--------------------+-------+------+--------------------------------------------+-----------------------+---------+-------+--------+-------------+

| id | select_type        | table | type | possible_keys                              | key                   | key_len | ref   | rows   | Extra       |

+----+--------------------+-------+------+--------------------------------------------+-----------------------+---------+-------+--------+-------------+

|  1 | PRIMARY            | CH1   | ref  | chataudit_event_type,chataudit_last_update | chataudit_event_type  | 767     | const |   8550 | Using where |

|  1 | PRIMARY            | CH2   | ref  | chataudit_event_type,chataudit_last_update | chataudit_last_update | 8       | func  |      1 | Using where |

|  4 | DEPENDENT SUBQUERY | INCH  | ref  | chataudit_event_type,chataudit_last_update | chataudit_event_type  | 767     | const |  10608 | Using where |

|  3 | DEPENDENT SUBQUERY | CA2   | ref  | chataudit_event_type,chataudit_last_update | chataudit_event_type  | 767     | const | 563124 | Using where |

|  2 | DEPENDENT SUBQUERY | CA1   | ref  | chataudit_event_type,chataudit_last_update | chataudit_event_type  | 767     | const | 169750 | Using where |

+----+--------------------+-------+------+--------------------------------------------+-----------------------+---------+-------+--------+-------------+

+----+--------------------+-------+------+--------------------------------------------+-----------------------+---------+-------+--------+-----------------------------+

| id | select_type        | table | type | possible_keys                              | key                   | key_len | ref   | rows   | Extra                       |

+----+--------------------+-------+------+--------------------------------------------+-----------------------+---------+-------+--------+-----------------------------+

|  1 | PRIMARY            | ch1   | ref  | chataudit_event_type,chataudit_last_update | chataudit_event_type  | 767     | const |   8790 | Using where; Using filesort |

|  1 | PRIMARY            | ch2   | ref  | chataudit_event_type,chataudit_last_update | chataudit_last_update | 8       | func  |      1 | Using where                 |

|  4 | DEPENDENT SUBQUERY | INCH  | ref  | chataudit_event_type,chataudit_last_update | chataudit_event_type  | 767     | const |  10642 | Using where                 |

|  3 | DEPENDENT SUBQUERY | inch  | ref  | chataudit_event_type,chataudit_last_update | chataudit_event_type  | 767     | const | 585168 | Using where                 |

|  2 | DEPENDENT SUBQUERY | inch  | ref  | chataudit_event_type,chataudit_last_update | chataudit_event_type  | 767     | const | 175084 | Using where                 |

+----+--------------------+-------+------+--------------------------------------------+-----------------------+---------+-------+--------+-----------------------------+

vepr85 · 24.01.2011 13:21:18

а с индексами что сделать? Выбрать другое поле для объединения?
Просто у меня проиндексированы почти все поля

paulus · 24.01.2011 15:16:47

http://sqlinfo.ru/forum/viewtopic.php?pid=21796#p21796
Нужно сделать то, что написано во втором абзаце.

vepr85 · 24.01.2011 18:23:17

у меня же объединяются через индексное поле. Проблема в том, что я через поле eventType и делаю объединение
Если не совсем понятно, я могу описать, что должны делать запросы

paulus · 24.01.2011 19:51:21

Представьте, что перед Вами — большой склад. На нем хранятся разнообразные
товары. Перед вами есть две большие книги, одна содержит соотношение имен
компаний с номерами полок на складе (например, по ней Вы можете быстро опре-
делить, что продукция компании procter-and-gamble содержится на полках с пяти-
сотой до пятисотсемнадцатой). Вторая книга содержит соотношение товаров на
полках (например, мыло лежит на 17, 19, ... полках). Вам нужно найти мыло ком-
пании procter-and-gamble. Да, не смотря на две книги, Вам прийдется идти и тол-
каться на полках, чтобы найти там мыло.

Я же предлагаю Вам сделать книгу, в которой будет написано «Procter-and-gamble:
зубные пасты — полка 500, мыло — полка 501, ...». Чувствуете величие двойного
индекса?

vepr85 · 24.01.2011 23:13:58

Да чувствую!!
Благодарю за наглядное объяснение
т.е. надо добавить в 1 индекс 2 поля и получится двойной индекс

vepr85 · 25.01.2011 13:38:37

А что может мешать построению индекса в таблице?(2 500 000 записей уже)
Выбрал время минимальной нагрузки, когда пытался построить - он строился у меня около часа - потом я отменил операцию. Пробую на тестовой таблице.

paulus · 25.01.2011 14:14:19

Большой объем данных требует большого времени построения
индекса — тут уж ничего не поделаешь. innodb_buffer_pool_size
достаточный у Вас стоит?

vepr85 · 25.01.2011 15:52:14

достаточный - это сколько
Его можно рассчитать?

paulus · 25.01.2011 16:28:08

Рассчитать — практически никак. Какая версия MySQL? В 5.1 + InnoDB Plugin
создание неуникальных индексов происходит гораздо быстрее (в десятки
раз) за счет того, что переделывается не вся таблица, а только этот индекс.

vepr85 · 25.01.2011 16:43:45

Версия 5.089

vepr85 · 25.01.2011 18:34:20

построился индекс и запросы пополз быстрее!!! ))

paulus · 25.01.2011 20:39:07

5.0 уже достаточно устарела, подумайте над тем, чтобы обновиться хотя бы
до 5.1. 5.5 вышел и правда еще не достаточно давно, чтобы кидаться на него,
но смотреть на него всё равно уже стоит.

vepr85 · 26.01.2011 12:46:41

Благодарю. Запросы выполняются терпимее. По синтаксису можно какие-то улучшения применить?
мы переезжаем на этой неделе на новый сервер с версией 5.5 ))!!
Думаю работать ещё быстрее будет.

и ещё:
не подскажете как использовать динамический запрос для курсора в хранимке?(есть идея выполнить запрос и записать во временную таблицу, только удалиться ли она при разрыве связи.). Или для этого новый топик открыть стоит.

paulus · 26.01.2011 17:12:25

Честно говоря, не понял вопроса.

Курсоры в MySQL работают только для чтения. Временные таблицы рабо-
тают нормально, удалится при разрыве соединения, да.

Форум пользователей MySQL

#1 21.01.2011 11:47:00

Помогите ускорить запрос

#2 22.01.2011 21:42:15

Re: Помогите ускорить запрос

#3 23.01.2011 10:39:41

Re: Помогите ускорить запрос

#4 23.01.2011 10:45:19

Re: Помогите ускорить запрос

#5 23.01.2011 14:49:51

Re: Помогите ускорить запрос

#6 23.01.2011 16:47:58

Re: Помогите ускорить запрос

#7 23.01.2011 23:44:33

Re: Помогите ускорить запрос

#8 24.01.2011 10:43:27

Re: Помогите ускорить запрос

#9 24.01.2011 12:44:06

Re: Помогите ускорить запрос

#10 24.01.2011 12:46:44

Re: Помогите ускорить запрос

Код:

#11 24.01.2011 13:08:58

Re: Помогите ускорить запрос

#12 24.01.2011 13:21:18

Re: Помогите ускорить запрос

#13 24.01.2011 15:16:47

Re: Помогите ускорить запрос

#14 24.01.2011 18:23:17

Re: Помогите ускорить запрос

#15 24.01.2011 19:51:21

Re: Помогите ускорить запрос

#16 24.01.2011 23:13:58

Re: Помогите ускорить запрос

#17 25.01.2011 13:38:37

Re: Помогите ускорить запрос

#18 25.01.2011 14:14:19

Re: Помогите ускорить запрос

#19 25.01.2011 15:52:14

Re: Помогите ускорить запрос

#20 25.01.2011 16:28:08

Re: Помогите ускорить запрос

#21 25.01.2011 16:43:45

Re: Помогите ускорить запрос

#22 25.01.2011 18:34:20

Re: Помогите ускорить запрос

#23 25.01.2011 20:39:07

Re: Помогите ускорить запрос

#24 26.01.2011 12:46:41

Re: Помогите ускорить запрос

#25 26.01.2011 17:12:25

Re: Помогите ускорить запрос

Board footer