ORDER BY из другой таблицы

Тема в разделе "Базы данных", создана пользователем Q_BASIC, 29 дек 2017.

Модераторы: latteo
  1. Q_BASIC

    Q_BASIC

    Регистр.:
    30 ноя 2013
    Сообщения:
    446
    Симпатии:
    320
    Приветствую,

    Есть таблица proxy. Есть таблица proxy_errors.

    Надо получить прокси в порядке, где в самом начале будут те, у которых меньше всего записей об ошибках в таблице proxy_errors за последние три дня и с type_error = 1 AND time > time()-259200

    То есть запрос для второй таблица примерно такой:
    Код:
    SELECT proxy_id, COUNT(*) FROM proxy_errors WHERE type_error = 1 AND time > <?php time()-259200; ?> GROUP BY proxy_id
    А для первой:
    Код:
    SELECT * FROM proxy ORDER BY %count% ASC
    Как объединить два запроса, чтобы поменьше ресурсов ело?
     
  2. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    155
    Симпатии:
    101
    Почему не показываете схему базы данных + немного данных? Это мотивирует отвечать.
    Я использовал такие данные:
    CREATE TABLE proxy (
    id INT(11) UNSIGNED NOT NULL COMMENT 'ID прокси',
    name VARCHAR(255) NOT NULL COMMENT 'Название прокси',
    PRIMARY KEY(id)
    );
    INSERT INTO proxy VALUES
    (1, 'Прокси 1'),
    (2, 'Прокси 2'),
    (3, 'Прокси 3'),
    (4, 'Прокси 4 (очень стабильный)');
    CREATE TABLE proxy_errors (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Номер записи об ошибке',
    proxy_id INT(11) UNSIGNED NOT NULL COMMENT 'ID прокси',
    type_error INT(11) UNSIGNED NOT NULL COMMENT 'тип ошибки',
    time INT(11) UNSIGNED NOT NULL COMMENT 'время ошибки',
    PRIMARY KEY(id)
    );
    INSERT INTO proxy_errors VALUES
    (DEFAULT, 1, 1, UNIX_TIMESTAMP() - RAND() * 36000),
    (DEFAULT, 1, 1, UNIX_TIMESTAMP() - RAND() * 36000),
    (DEFAULT, 1, 1, UNIX_TIMESTAMP() - RAND() * 36000),
    (DEFAULT, 2, 1, UNIX_TIMESTAMP() - RAND() * 36000),
    (DEFAULT, 2, 1, UNIX_TIMESTAMP() - RAND() * 36000),
    (DEFAULT, 3, 1, UNIX_TIMESTAMP() - RAND() * 36000);

    1. Запрос

    Код:
    (
      SELECT proxy.*, 0 AS `err_cnt`
      FROM proxy
      LEFT JOIN proxy_errors AS errors ON (errors.proxy_id = proxy.id)
      WHERE
      (errors.id IS NULL)
      OR (errors.type_error = 1 AND time < (UNIX_TIMESTAMP() - 259200))
    )
    UNION ALL
    (
      SELECT proxy.*, COUNT(*) AS `err_cnt`
      FROM proxy
      LEFT JOIN proxy_errors AS errors ON (errors.proxy_id = proxy.id)
      WHERE
      errors.type_error = 1
      AND time > (UNIX_TIMESTAMP() - 259200)
      GROUP BY proxy.id
    )
    ORDER BY `err_cnt`
    LIMIT 10
    ;
    
    2. Про оптимизацию

    1) `id`, `proxy_id`, `type_error`, `time` должно быть NOT NULL, так индексы работают эффективнее.
    2) `id` и `proxy_id` должны быть объявлены полностью одинаково, например как INT(11) UNSIGNED NOT NULL, иначе будет тратиться время на преобразование типов.
    3) Добавить покрывающий индекс

    Код:
    ALTER TABLE `proxy_errors` ADD INDEX(`proxy_id`, `time`, `type_error`);
    
    или
    Код:
    ALTER TABLE `proxy_errors` ADD INDEX(`time`, `proxy_id`, `type_error`);
    
    Первый, думаю, будет эффективнее. Но лучше провести замеры на большом количестве данных. Сразу два индекса почти одинаковых индекса - зло. Мускул не может использовать более одно индекса в подзапросе.

    Жду реальных данных, например 10-100 тысяч строк, чтобы сделать какие-то замеры, иначе это всего лишь теория.
     
    Последнее редактирование: 30 дек 2017
    latteo и Q_BASIC нравится это.
  3. Q_BASIC

    Q_BASIC

    Регистр.:
    30 ноя 2013
    Сообщения:
    446
    Симпатии:
    320
    тысяч строк пока нету, в разработке еще всё

    Чуть чуть задача изменилась еще, тип ошибки 2, и надо в запрос еще proxy.work=1

    http://sqlfiddle.com/#!9/ee731a

    Тут у прокси с id 124687, 124688, 124689, 124690, 124691 ошибок с типом 2 вообще нет, а их не получает запрос

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