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

Q_BASIC

Хранитель порядка
Регистрация
30 Ноя 2013
Сообщения
516
Реакции
1.240
Приветствую,

Есть таблица 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

Как объединить два запроса, чтобы поменьше ресурсов ело?
 
Есть таблица proxy. Есть таблица proxy_errors.
Почему не показываете схему базы данных + немного данных? Это мотивирует отвечать.
Я использовал такие данные:
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 тысяч строк, чтобы сделать какие-то замеры, иначе это всего лишь теория.
 
Последнее редактирование:
Почему не показываете схему базы данных + немного данных? Это мотивирует отвечать.
Я использовал такие данные:
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 тысяч строк, чтобы сделать какие-то замеры, иначе это всего лишь теория.

тысяч строк пока нету, в разработке еще всё

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

Для просмотра ссылки Войди или Зарегистрируйся

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

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