Mysql Подскажите по индексам, плиз

Активист · 7 Июл 2015

Собственно есть таблица, в ней есть поле accept_payment_moderator_id, на нем индекс. От эксплейна в шоке. В первом случае выдергивает все строки + создает временную таблицу, еще и file sort, во втором же случае - только 8-мь строк. Задача вытянуть все данные, но так, что бы индекс юзался (force index не вариант).

Код:

mysql> explain select * from `invoices` group by `accept_payment_moderator_id`;
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | invoices | ALL  | NULL          | NULL | NULL    | NULL | 4111 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

mysql> explain select `accept_payment_moderator_id` from `invoices` group by `accept_payment_moderator_id`;
+----+-------------+----------+-------+---------------+-----------------------------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key                         | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+-----------------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | invoices | range | NULL          | accept_payment_moderator_id | 5       | NULL |    8 | Using index for group-by |
+----+-------------+----------+-------+---------------+-----------------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Причем если делать вложенный запрос, дергает также 4k заказов.

Код:

explain select * from `app_moderators_user`  where `id` in ( select `accept_payment_moderator_id` from `invoices` group by `accept_payment_moderator_id` );
+----+--------------------+---------------------+-------+---------------+-----------------------------+---------+------+------+-------------+
| id | select_type        | table               | type  | possible_keys | key                         | key_len | ref  | rows | Extra       |
+----+--------------------+---------------------+-------+---------------+-----------------------------+---------+------+------+-------------+
|  1 | PRIMARY            | app_moderators_user | ALL   | NULL          | NULL                        | NULL    | NULL |   27 | Using where |
|  2 | DEPENDENT SUBQUERY | invoices            | index | NULL          | accept_payment_moderator_id | 5       | NULL | 4111 | Using index |
+----+--------------------+---------------------+-------+---------------+-----------------------------+---------+------+------+-------------+
2 rows in set (0.00 sec)

Hello · 7 Июл 2015

SHOW CREATE TABLE покажи

AnrDaemon · 7 Июл 2015

Индекс бесполезен, если частота появления уникального значения приближается к нулю.
Об этом не раз уже говорили в этом разделе.

MiksIr · 7 Июл 2015

Кривые запросы - кривые эксплейны

select * from `invoices` group by `accept_payment_moderator_id` - неверный запрос, нормальные базы пошлют и будут правы
select * from `app_moderators_user` where `id` in ( select ... ) - DEPENDENT SUBQUERY, угу... mysql 5.5- видимо

Напишите нормальный запрос, и будет нормальный план.

Активист · 7 Июл 2015

MiksIr написал(а):
Кривые запросы - кривые эксплейны
select * from `invoices` group by `accept_payment_moderator_id` - неверный запрос, нормальные базы пошлют и будут правы
select * from `app_moderators_user` where `id` in ( select ... ) - DEPENDENT SUBQUERY, угу... mysql 5.5- видимо
Напишите нормальный запрос, и будет нормальный план.

Ок. Надо выбрать пользователей, которые когда-либо фиксировали оплаты.

Код:

mysql> explain  select
    ->                     `app_moderators_user`.*
    ->             from
    ->                     `invoices`
    ->             left join
    ->                     `app_moderators_user` on `app_moderators_user`.`id` = `invoices`.`accept_payment_moderator_id`
    ->             group by
    ->                     `invoices`.`accept_payment_moderator_id`;
+----+-------------+---------------------+--------+---------------+-----------------------------+---------+-------------------------------------------------+------+-------------+
| id | select_type | table               | type   | possible_keys | key                         | key_len | ref                                             | rows | Extra       |
+----+-------------+---------------------+--------+---------------+-----------------------------+---------+-------------------------------------------------+------+-------------+
|  1 | SIMPLE      | invoices            | index  | NULL          | accept_payment_moderator_id | 5       | NULL                                            | 4829 | Using index |
|  1 | SIMPLE      | app_moderators_user | eq_ref | PRIMARY       | PRIMARY                     | 4       | avtovokzal.invoices.accept_payment_moderator_id |    1 |             |
+----+-------------+---------------------+--------+---------------+-----------------------------+---------+-------------------------------------------------+------+-------------+
2 rows in set (0.00 sec)

mysql>

Писать в отдельную таблицу?)

На компе да, Debian Sid с MySQL 5.5

Активист · 7 Июл 2015

Hello написал(а):
SHOW CREATE TABLE покажи

Да типичная структура

Код:

mysql> show create table `invoices`\G
*************************** 1. row ***************************
       Table: invoices
Create Table: CREATE TABLE `invoices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  ...
  `prepay` smallint(5) unsigned DEFAULT NULL,
  `prepay_sum` decimal(10,2) DEFAULT NULL,
  `final_payment_sum` decimal(10,2) DEFAULT NULL,
   ...
  `is_paid` smallint(6) DEFAULT NULL,
  `paid_datetime` datetime DEFAULT NULL,
  `paid_sum` decimal(10,2) DEFAULT NULL,
  `paid_payment_method_id` int(10) unsigned DEFAULT NULL,
  `accept_payment_moderator_id` int(10) unsigned DEFAULT '0',
  `refunded` smallint(6) DEFAULT '0',
  `refunded_sum` decimal(10,2) DEFAULT NULL,
  `refunded_moderator_id` int(10) unsigned DEFAULT '0',
  `status` int(10) unsigned NOT NULL DEFAULT '0',
  `payment_gate_order_id` varchar(64) DEFAULT NULL,
  `payment_gate_form_url` text,
  `payment_gate_created` datetime DEFAULT NULL,
  `payment_gate_action_code` int(11) DEFAULT NULL,
  `payment_gate_status` smallint(6) DEFAULT NULL,
  `payment_gate_last_status_detail` text,
  `payment_gate_attempt` int(10) unsigned DEFAULT NULL,
  `without_fee` smallint(6) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `created_datetime` (`created_datetime`),
  KEY `payment_gate_status` (`payment_gate_status`),
  KEY `schedule_id` (`schedule_id`),
  KEY `paid_payment_method_id` (`paid_payment_method_id`),
  KEY `accept_payment_moderator_id` (`accept_payment_moderator_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4830 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Активист · 7 Июл 2015

AnrDaemon написал(а):
Индекс бесполезен, если частота появления уникального значения приближается к нулю.
Об этом не раз уже говорили в этом разделе.

Второй explain вытянул 8 rows же.

Тугай · 8 Июл 2015

Активист написал(а):
Ок. Надо выбрать пользователей, которые когда-либо фиксировали оплаты.

Ну так и надо писать запрос:

Код:

select  `app_moderators_user`.*
from  `app_moderators_user` 
where exists (select * from `invoices` where `app_moderators_user`.`id` = `invoices`.`accept_payment_moderator_id`)
;

group by это для SUM(), COUNT(), AVG() и т.п.
Если нужно без повторений пишется DISTINCT. Пользователей меньше, чем заказов от них, в какую сторону объединять тоже имеет значение.

grigori · 10 Июл 2015

Активист, прочти свой запрос по-русски.
Выбрать данные по модераторам из таблицы инвойсов, если есть соответствующие записи в таблице модераторов, сгруппировать по ID модератора и вывести случайное значение из группы. Звучит как бред.
Если инвойсы есть, а модераторов нет, будут строки с null.

Составь запрос о том, что тебе надо, ты инженер, а не журналист, не надо пытаться обмануть базу.

Активист · 16 Июл 2015

Подсказали в привате, @S.Chushkin

Код:

mysql> explain select * from (   select accept_payment_moderator_id   from invoices   group by accept_payment_moderator_id ) m left join app_moderators_user u on u.id = m.accept_payment_moderator_id;
+----+-------------+------------+--------+-----------------------------+-----------------------------+---------+-------------------------------+------+--------------------------+
| id | select_type | table      | type   | possible_keys               | key                         | key_len | ref                           | rows | Extra                    |
+----+-------------+------------+--------+-----------------------------+-----------------------------+---------+-------------------------------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                        | NULL                        | NULL    | NULL                          |   10 | NULL                     |
|  1 | PRIMARY     | u          | eq_ref | PRIMARY                     | PRIMARY                     | 4       | m.accept_payment_moderator_id |    1 | NULL                     |
|  2 | DERIVED     | invoices   | range  | accept_payment_moderator_id | accept_payment_moderator_id | 5       | NULL                          |   10 | Using index for group-by |
+----+-------------+------------+--------+-----------------------------+-----------------------------+---------+-------------------------------+------+--------------------------+

Активист · 16 Июл 2015

Даже вот так, возвращает без NULL значений

Код:

mysql> explain select * from (   select accept_payment_moderator_id   from invoices   group by accept_payment_moderator_id ) m join app_moderators_user u on u.id = m.accept_payment_moderator_id;
+----+-------------+------------+--------+-----------------------------+-----------------------------+---------+-------------------------------+------+--------------------------+
| id | select_type | table      | type   | possible_keys               | key                         | key_len | ref                           | rows | Extra                    |
+----+-------------+------------+--------+-----------------------------+-----------------------------+---------+-------------------------------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                        | NULL                        | NULL    | NULL                          |   10 | Using where              |
|  1 | PRIMARY     | u          | eq_ref | PRIMARY                     | PRIMARY                     | 4       | m.accept_payment_moderator_id |    1 | NULL                     |
|  2 | DERIVED     | invoices   | range  | accept_payment_moderator_id | accept_payment_moderator_id | 5       | NULL                          |   10 | Using index for group-by |
+----+-------------+------------+--------+-----------------------------+-----------------------------+---------+-------------------------------+------+--------------------------+
3 rows in set (0,00 sec)

mysql>

grigori · 16 Июл 2015

да, это уже осмысленный, понятный запрос

AnrDaemon · 16 Июл 2015

А почему не

Код:

SELECT DISTINCT accept_payment_moderator_id   FROM invoices

?

WMix · 16 Июл 2015

distinct это тотже group by

AnrDaemon · 16 Июл 2015

Понятно, что тот же, просто читается внятнее.

grigori · 16 Июл 2015

почему { на той же строке, а не на следующей, да?

WMix · 16 Июл 2015

почему без шапки?

Mysql Подскажите по индексам, плиз

Активист

Активист

Hello

Новичок

AnrDaemon

Продвинутый новичок

MiksIr

miksir@home:~$

Активист

Активист

Активист

Активист

Активист

Активист

Тугай

Новичок

grigori

( ͡° ͜ʖ ͡°)

Активист

Активист

Активист

Активист

grigori

( ͡° ͜ʖ ͡°)

AnrDaemon

Продвинутый новичок

WMix

герр M:)ller

AnrDaemon

Продвинутый новичок

grigori

( ͡° ͜ʖ ͡°)

WMix

герр M:)ller