AkinaMasterZivсейчас некогда писать, но кратко - все домыслы...
Ох ты ж блин же ж... уж сразу написал бы "все знают, что это домыслы" - и спору конец.
t4 = таблица с 999 интегерами
т1 -- таблица с милионом записей (точнее 998001)
1.
2.
3.
insert into t1
select b1.y + b2.y*1000 a, MD5(b1.y + b2.y*1000) b
from t4 b1, t4 b2 -- картезиан 1000*1000
т2 -- вторая таблица в изначальной задаче.
по условию rand() > 0.х заполняется часть милиона.
1.
2.
3.
4.
insert into t2
select b1.y + b2.y*1000 a, MD5(b1.y + b2.y*1000) b
from t4 b1, t4 b2
where rand() > 0.9 -- 0.7,0.5,0.3,0.1
два тестовых СКЛ-а, первый НОТ ИН, второй ЛЕФТ АНТИ ЖОИНТ.
Ниже везде порядок сохраняется:
1.
2.
3.
4.
5.
6.
select count(1) from (
select t1.*
from t1
where t1.id not in
(select t2.idt2
from t2)) g
1.
2.
3.
4.
5.
select count(1) from (
select t1.*
from t1
left join t2 on t1.id = t2.idt2
where t2.idt2 is null) g
t2 = 8168 rows
r1 = 0.469, 0.453,
1 PRIMARY t1 index PRIMARY 4 994548 100.00 Using where; Using index3 SUBQUERY t2 index PRIMARY PRIMARY 4 10424 100.00 Using index
r2 = 1.343, 1.328
1 SIMPLE t1 index PRIMARY 4 994548 100.00 Using index1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 Using where; Not exists; Using index
t2 = 96843
r1 = 0.547, 0.547
1 PRIMARY t1 index PRIMARY 4 994548 100.00 Using where; Using index3 SUBQUERY t2 index PRIMARY PRIMARY 4 91839 100.00 Using index
r2 = 1.375. 1.391
1 SIMPLE t1 index PRIMARY 4 994548 100.00 Using index1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 Using where; Not exists; Using index
t2 = 245979,
r1 = 0.687, 0.688
1 PRIMARY t1 index PRIMARY 4 994548 100.00 Using where; Using index3 SUBQUERY t2 index PRIMARY PRIMARY 4 245002 100.00 Using index
r2 = 1.422, 1.438
1 SIMPLE t1 index PRIMARY 4 994548 100.00 Using index1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 Using where; Not exists; Using index
t2 = 482868
r1 = 2.125, 1.937
1 PRIMARY t1 index PRIMARY 4 994548 100.00 Using where; Using index3 SUBQUERY t2 index PRIMARY PRIMARY 4 486675 100.00 Using index
r2 = 1.484, 1.485,
1 SIMPLE t1 index PRIMARY 4 994548 100.00 Using index1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 Using where; Not exists; Using index
t2 = 806773
run1 = 2.375, 2.390
1 PRIMARY t1 index PRIMARY 4 994548 100.00 Using where; Using index3 SUBQUERY t2 index PRIMARY PRIMARY 4 805950 100.00 Using index
run2 = 1.516, 1.516
1 SIMPLE t1 index PRIMARY 4 994548 100.00 Using index1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.id 1 100.00 Using where; Not exists; Using index
