Форум MySQL

Есть запрос:

<?php $sql = "   SELECT     certificates.*,     certificates.id_user AS oid_user,     users.id_user AS id_user,     users.user_type AS u_user_type,     o_users.sid_user AS o_sid_user,     o_users.user_type AS o_user_type,     TRIM(CONCAT_WS(' ', users.surname, users.name, users.patronymic)) AS u_name,     TRIM(CONCAT_WS(' ', o_users.surname, o_users.name, o_users.patronymic)) AS o_name   FROM certificates   LEFT JOIN users USING(id_certificate)   LEFT JOIN users AS o_users ON o_users.id_user = certificates.id_user   GROUP BY certificates.id_certificate   ORDER BY $order IS NULL OR $order = '' $sort, $order $sort   LIMIT $start, $numrec"; ?>

Индексы в таблицах:

users:   PRIMARY KEY (id_user),   INDEX email (email),   INDEX id_certificate (id_certificate),   INDEX pid_user (pid_user),   INDEX sid_user (sid_user) certificates:     PRIMARY KEY (id_certificate),   INDEX amount (amount),   INDEX id_user (id_user)

В таблице users 1500 записей. В таблице certificates - 1000. Сначала, небыло индекса в таблице users по полю id_certificate, и запрос выполнялся очень медленно. После добавления индекса проблема НА ЛОКАЛЬНОЙ МАШИНЕ решена, запрос выполняется быстро. Загрузил скрипт на сервер, синхронизировал таблицы, добавил индекс в БД на хостинг. Но скрипт на хостинге выполняется очень медленно.

Почему считаю что медленно? Потому что есть еще аналогичны запрос:

<?php $sql = "   SELECT     users.*,     DATE_FORMAT(users.birth_day, '%m%d') AS birth_md,     DATE_FORMAT(users.birth_day, '%m') AS birth_m,     users.user_type AS u_user_type,     d_users.user_type AS d_user_type,     p_users.user_type AS p_user_type,     TRIM(CONCAT_WS(' ', users.surname, users.name, users.patronymic)) AS u_name,     TRIM(CONCAT_WS(' ', d_users.surname, d_users.name, d_users.patronymic)) AS d_name,     TRIM(CONCAT_WS(' ', p_users.surname, p_users.name, p_users.patronymic)) AS p_name   FROM users AS users   LEFT JOIN users AS d_users ON users.sid_user = d_users.id_user   LEFT JOIN users AS p_users ON users.pid_user = p_users.id_user   GROUP BY users.id_user   ORDER BY $order IS NULL OR $order = '' ASC, $order $sort   LIMIT $start, $numrec"; ?>

Этот запрос, и на локальной машине, и на хостинге, с тем же кол-вом записей в таблице users, раз в восемь быстрей выполняется.

перед тем, как оптимизировать любой запрос, из него стоит убрать явные логические ошибки.
Я о GROUP BY и висящих в SELECT полях.

Что значит "висящих в SELECT полях"?

Некорректные запросы:
запрос с висящим b

SELECT a, b FROM t GROUP BY a

запрос с висящим c

SELECT a, MAX(b), c FROM t GROUP BY a

корректные запросы

SELECT a, MAX(b) FROM t GROUP BY a

SELECT a, b FROM t

SELECT a, MAX(b), MIN(c) FROM t GROUP BY a

SELECT a, MAX(b), c FROM t GROUP BY a, c

Не надо применять GROUP BY, если непонятно назначение этой конструкции.

Не понял. Почему в первом примере b стал висящим? Что вообще означает висящий?

потому что операция группировки предписывает выдавать не все строки из таблицы, а объединить их в группы на базе совпадающих значений a, затем посчитать агрегатные функции (максимумы, минимумы, суммы, каунты и т.п. для остальных столбиков в пределах групп, так что на группу вычисляется лишь одно значение.
С a всё понятно - нужно взять то значение, которое определяет группу строк. В самой группе значения в строках совпадают.
С b всё понятно - нужно взять максимум в группе.
Какое значение взять для с? Наибольшее? Наименьшее? Сумму?
Поскольку неясно какое брать - с оказывается неоднозначным по группе, висящим в воздухе.

Собственно, напрямую с группировкой это не связано.
Посмотрите на такой запрос:

SELECT MAX(date) FROM tbl

и на такой:

SELECT name, MAX(date) FROM tbl

Как вычислять второй запрос?

Это примерно то, что Вы предлагаете сделать серверу. Ну и нам докучи.

Да, спасибо что пояснили. Теперь понял. Получается что в моем запросе, нет необходимости в GROUP BY. Убрал его, но все равно запрос выполняется медленно.

<?php  $sql = "    SELECT      certificates.*,      certificates.id_user AS oid_user,      users.id_user AS id_user,      users.user_type AS u_user_type,      o_users.sid_user AS o_sid_user,      o_users.user_type AS o_user_type,      TRIM(CONCAT_WS(' ', users.surname, users.name, users.patronymic)) AS u_name,      TRIM(CONCAT_WS(' ', o_users.surname, o_users.name, o_users.patronymic)) AS o_name    FROM certificates    LEFT JOIN users USING(id_certificate)    LEFT JOIN users AS o_users ON o_users.id_user = certificates.id_user    ORDER BY $order IS NULL OR $order = '' $sort, $order $sort    LIMIT $start, $numrec";  ?>

А что EXPLAIN говорит ?

Что это такое?

Почитал про EXPLAIN в интернете: http://www.mysql.ru/docs/man/EXPLAIN.html

В моем случае эта конструкция выдает следующее:

На хостинге:

id   select_type   table         type     possible_keys  key     key_len   ref                  rows   Extra 1    SIMPLE        certificates  ALL      NULL           NULL    NULL      NULL                 967    Using temporary; Using filesort 1    SIMPLE        users         ALL      id_certificate NULL    NULL      NULL                 1056 1    SIMPLE        o_users       eq_ref   PRIMARY        PRIMARY 4         certificates.id_user 1

На локальной машине:

id   select_type   table         type     possible_keys  key            key_len ref                         rows  Extra  1    SIMPLE        certificates  ALL      NULL           NULL           NULL    NULL                        4407  Using filesort  1    SIMPLE        users         ref      id_certificate id_certificate 4       certificates.id_certificate 258   1    SIMPLE        o_users       eq_ref   PRIMARY        PRIMARY        4       certificates.id_user        1

как бы заполнение таблиц сильно отличается. Нет?
И на обеих машинах зрезультат запроса в конечном итоге приходится сортировать после выполнения отдельным процессом внешней сортировки.
А аналогичный запрос по-другому идет?
Ключ users.id_certificate непервичный и неуникальный - это явное отличие.
Если Вы лишь хотите знать "почему не как тот".

>как бы заполнение таблиц сильно отличается. Нет?

Что имеется ввиду? Не понял.

>А аналогичный запрос по-другому идет?

Тоже не понял, какой аналогичный запрос? Как по другому?

>Ключ users.id_certificate непервичный и неуникальный - это явное отличие.

Да, не первичный, и не уникальный.
Явное отличие от чего? В обоих вышеприведенных случаях, и на сервере и на локальной машине users.id_certificate непервичный и неуникальный. Но на локальной машине, все работает быстрей, и EXPLAIN говорит что на локальной машине индекс id_certificate используется. А на хостинге он, почему-то игнорируется.

>Если Вы лишь хотите знать "почему не как тот".

Первое, что меня интересует - Как ускорить? Что сделать, чтобы быстрей работал на хостинге?

>>как бы заполнение таблиц сильно отличается. Нет?
>Что имеется ввиду? Не понял.
на хостинге и на локальной машине.
Вы же спрашиваете, почему эксплейны разные?
Потому что таблицы по разному заполнены.


>>А аналогичный запрос по-другому идет?
>Тоже не понял, какой аналогичный запрос?
В самом первом посте ваша фраза:
>>>>Почему считаю что медленно? Потому что есть еще аналогичны запрос:


>>Ключ users.id_certificate непервичный и неуникальный - это явное отличие.
>Да, не первичный, и не уникальный.
А user_id в users "аналогичного запроса" был первичным и уникальным.
Наверное оптимизатор SQL этим фактом воспользовался.

>>Если Вы лишь хотите знать "почему не как тот".
>Первое, что меня интересует - Как ускорить? Что сделать, чтобы быстрей работал?
Структуру БД представляет себе только автор.
Врядли кто еще сможет подсказать так в лоб.

Вообще, вопрос "как ускорить" задается вторым. Первым - "почему медленно работает".
Эксплейн Вам показал почему. Потому что индексами серверу не воспользоваться.
Вы соединяете таблицы так, что прямого единственного пути из некоторой записи одной таблицы в строго одну конкретную запись другой таблицы через её индекс нет. В результате копится дикое количество строк, которое потом перед вырезкой LIMIT приходится выгружать в файл и сортировать файлсортом. Это тяжело и медленно.

>Структуру БД представляет себе только автор.
>Врядли кто еще сможет подсказать так в лоб.

users

CREATE TABLE users(   id_user INT(11) NOT NULL AUTO_INCREMENT,   sid_user INT(11) NOT NULL DEFAULT 0,   pid_user INT(11) NOT NULL DEFAULT 0,   id_certificate INT(11) NOT NULL DEFAULT 0,   surname VARCHAR(50) NOT NULL,   name VARCHAR(50) NOT NULL,   patronymic VARCHAR(50) NOT NULL,   user_type ENUM('customer', 'partner', 'ds', 'student') NOT NULL DEFAULT 'customer',   customer_state ENUM('1', '2', '3', '4', '5', '6') NOT NULL,   customer ENUM('1', '0') NOT NULL DEFAULT '0',   student ENUM('0', '1') NOT NULL DEFAULT '0',   partner ENUM('1', '0') NOT NULL DEFAULT '0',   ds ENUM('1', '0') NOT NULL DEFAULT '0'   PRIMARY KEY (id_user),   INDEX email (email),   INDEX id_certificate (id_certificate),   INDEX pid_user (pid_user),   INDEX sid_user (sid_user) ) ENGINE = MYISAM AUTO_INCREMENT = 1135 CHARACTER SET cp1251 COLLATE cp1251_general_ci;

certificates

CREATE TABLE certificates(   id_certificate INT(11) NOT NULL AUTO_INCREMENT,   id_user INT(11) NOT NULL DEFAULT 0,   code CHAR(6) NOT NULL,   state ENUM('new', 'actuated', 'used', 'send', 'exp') NOT NULL DEFAULT 'new',   amount INT(11) NOT NULL,   date_create DATE DEFAULT NULL,   date_exp DATE DEFAULT NULL,   type ENUM('default', 'newyear', 'birthday', '8mar', '23feb') DEFAULT 'default',   reminder_date DATE DEFAULT NULL,   note VARCHAR(255) NOT NULL,   PRIMARY KEY (id_certificate),   INDEX amount (amount),   INDEX id_user (id_user) ) ENGINE = MYISAM AUTO_INCREMENT = 4461 CHARACTER SET cp1251 COLLATE cp1251_general_ci;

Если это поможет, и кто-то подскажет, что конкретно можно сделать, чтобы ускорить запрос, буду очень благодарен.

Дело в том что при разных запросах при неизменной структуре таблиц будут разные результаты выборки ,в том числе и скорость и количество операций

В этом и заключается мастерство грамотного проектирования структуры

Определитесь, сначала, какие основные запросы вам необходимы и оптимизируйте под них
попробуйте проставить индексы на полях и посмотреть результаты основных запросов

Инструмент анализа вы уже знаете

Может придётся создавать составные ключи, может надо будет менять саму структуру таблиц.

>Дело в том что при разных запросах при неизменной структуре таблиц будут разные результаты выборки ,в том числе и скорость и количество операций

Проблема в том, что запросы одинаковые. Разница только в положении - один на локальной машне, второй на хостинге.

>Определитесь, сначала, какие основные запросы вам необходимы и оптимизируйте под них
>попробуйте проставить индексы на полях и посмотреть результаты основных запросов
>Может придётся создавать составные ключи, может надо будет менять саму структуру таблиц.

Вот в этом-то и нужна помощь. Для меня программирование - хобии. Я не так силен во всех тонкостях. И, честно говоря, не очень сильно понимаю принцип работы самих индексов. Поэтому, собственно, и прошут здесь помощи, т.е. каких-то конкретных советов, что поментяь местами, что добавить, что убавить, или еще что, чтобы работало быстрей.

а на другом серваке так же медленно выполняются запросы?

На местно хостинге, где я размещал свои сайты ранее, было все хорошо. Запросы выполнялись быстро.