Тест-задание от TiBiDaBo

иллюстрация к моему посту выше

Код (Text):

1. CREATE TABLE `lions` (
2.   `id` int(10) NOT NULL AUTO_INCREMENT,
3.   `sex` tinyint(1) NOT NULL DEFAULT '1',
4.   `wool` smallint(3) NOT NULL DEFAULT '1',
5.   `color` smallint(3) NOT NULL DEFAULT '1',
6.   PRIMARY KEY (`id`),
7.   KEY `ix_lions_2` (`wool`,`color`)
8. ) ENGINE=InnoDB

нагенерил 1млн строк как

Код (PHP):

1. $stmt = $db->prepare("INSERT INTO `lions`(`sex`, `wool`, `color`) VALUES(?, ?, ?)");
2. mt_srand(1);
3. for ($i = 0; $i < 1000000; ++$i) {
4.     $stmt->execute([
5.         1 + $i % 2,
6.         mt_rand(1, 100),
7.         mt_rand(1, 100)
8.     ]);
9. }
10.  

у нас 4 возможных сочетания признаков wool и color, два из которых имеют равный приоритет.

запрос #1 (макс. приоритет)

Код (Text):

1.     SELECT SQL_NO_CACHE m.`id` AS m_id, f.`id` AS f_id
2.     FROM `lions` AS m, `lions` AS f
3.     WHERE (m.wool = f.wool) AND (m.color = f.color) AND (m.`sex` = 1) AND (f.`sex` = 2)

терпения не хватает дождаться завершения запроса
но если добавить LIMIT 50, то time 0.002s

mt_srand() для повторяемости результата
SQL_NO_CACHE чтобы не одурачить себя

теперь четырежды повторяю этот запрос (c limit) но с вариантами =/<> у wool и color и склеиваю через UNION ALL
(... = AND =)
U.A.
(... = AND <>)
U.A.
(... <> AND =)
U.A.
(... <> AND <>)
получаю за 0.036s из пула в миллион записей!
порядок строк сохранится в порядке расстановки запросов, т.е. приоритетом 4х вариантов сочетаний я рулю как мне захочется!

Добавлено спустя 31 минуту 39 секунд:
Как это улучшить чтобы добиться уникальности самца и самки я еще не придумал )))

Добавлено спустя 2 минуты 20 секунд:
вот так можно:

Код (Text):

1.     SELECT SQL_NO_CACHE m.`id` AS m_id, MIN(f.`id`) AS f_id
2.     FROM `lions` AS m, `lions` AS f
3.     WHERE (m.wool = f.wool) AND (m.color = f.color) AND (m.`sex` = 1) AND (f.`sex` = 2)
4.   GROUP BY m.`id`
5.   LIMIT 50

time 0.021s вполне приемлемо

Добавлено спустя 12 минут 59 секунд:
--------------------------------------------
У меня в голове есть и абсолютно другой вариант оптимизации
У нас ограниченное число сочетаний параметров wool, color и sex. 100*100*2 = 20000.
По условию задачи, при прочих равных нас интересуют те записи, у которых id меньше.
Отсюда следует, что будь у нас хоть миллион, хоть 100 миллионов львов, реально в наших выборках будут присутствовать <= 20000 кандидатов.

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

Код (Text):

1. SELECT MIN(id), wool, color, sex
2. FROM lions
3. GROUP BY wool, color, sex

Мысль понятна?

 

Maputo, ты не о том говоришь. При чем здесь гомо/гетеро?! Математически 20000 вариантов пол+длина+цвет. Заказчик указал все возможные вариации в задании.

Откуда ты взял про короткую шерсть? У нас условные сферические львы. Не надо додумывать типа "из жизни".

 

По невнимательности я пропустил тот факт, что значения wool и color начинаются с 0, а не с 1. То есть 101 вариант.
Но сути это не меняет, запросы остаются те же. Просто область определения шире.

Добавлено спустя 27 минут 8 секунд:
Chushkin, вот с этим я не согласен:

Код (Text):

1. (abs(t1.wool - t2.wool) + abs(t1.color - t2.color)) rating

разве было указание, что "расстояние" между параметрами имеет значение? Я так понял задание, что важно совпадение или несовпадение. То есть если вычислять рейтинг как число то

Код (Text):

1. ((t1.wool = t2.wool) + (t1.color = t2.color)) rating

Другой повод для несовпадения результатов — указание на то, что "признаки wool и color имеют одинковый вес". То есть записи, где только один из признаков совпадает, могут стоять в любом порядке относительно друг друга, это не будет нарушением. То есть возможны разные правильные варианты ответа.

 

Соглашусь, что формулировка допускает толкование, но я остаюсь при мнении, что: "максимальные различия" это случай когда оба признака не равны. А 100% совпадение это когда оба признака равны.
Не вижу указания расчитывать разницу в длине или цвете.

 

Я называю разницей математическую операцию "-" ))) В тексте задания слово "разница" не встречается.
А различием называю простой факт неравенства. Думаю, что заказчик имеет в виду то же.

Не будем ходить вокруг да около. Кто-то должен спросить автора, ящитаю.

 

ok, это достойная т.з.

в задании есть еще место для разночтений:

"Если для одного льва находится несколько подходящих в равной степени львиц, то берем ту, у которой меньший номер.
Аналогично должно работать и с множеством львов у львицы." (выделено мной)

Могут ли в выборке присутствовать пары с одним и тем же львом, но с разной степенью подходящести и разными львицами?
То есть такие записи:
rate, m_id, f_id
1, 14, 20
1, 17, 88
1, 78, 1020
2, 14, 99
2, 101, 104

Это неочевидно.

Добавлено спустя 6 минут 44 секунды:
P.S. Я выложил на sqlfiddle версию с группирующей вьюшкой. Львы и львицы, подходящие в равной степени, отскаются на этапе create view — остаются только наименьшие id. Потом по этой выборке делаются запросы в порядке убывания приоритета.
Случаи пар с разными рейтингами, где один из партнеров повторяется, возможны!
http://sqlfiddle.com/#!9/4c991/1

По понятным причинам, миллион записей на sqlfiddle не загонишь. Демо с 500 львами. У меня на миллионной таблице запрос без кеша выполняется за 4 секунды.

P.P.S. Я не считаю этот вариант правильным и окончательным. Буду благодарен за подсказки.

P.P.P.S. Chushkin, насколько я вижу, у вас не отбрасываются повторы львов и львиц даже в пределах одного рейтинга. Так что ваш вариант на эталон тоже не тянет.

 

http://sqlfiddle.com/#!9/4c991/5