summaryrefslogtreecommitdiff
path: root/mysql-test/main/index_intersect_innodb.result
blob: 44407dbcd306543d4ba48a56d09a0f2e038b5307 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB';
set @innodb_stats_persistent_save= @@innodb_stats_persistent;
set @innodb_stats_persistent_sample_pages_save=
@@innodb_stats_persistent_sample_pages;
set global innodb_stats_persistent= 1;
set global innodb_stats_persistent_sample_pages=100;
DROP TABLE IF EXISTS t1,t2,t3,t4;
DROP DATABASE IF EXISTS world;
set names utf8;
CREATE DATABASE world;
use world;
CREATE TABLE Country (
Code char(3) NOT NULL default '',
Name char(52) NOT NULL default '',
SurfaceArea float(10,2) NOT NULL default '0.00',
Population int(11) NOT NULL default '0',
Capital int(11) default NULL,
PRIMARY KEY  (Code),
UNIQUE INDEX (Name)
);
CREATE TABLE City (
ID int(11) NOT NULL auto_increment,
Name char(35) NOT NULL default '',
Country char(3) NOT NULL default '',
Population int(11) NOT NULL default '0',
PRIMARY KEY  (ID),
INDEX (Population),
INDEX (Country) 
);
CREATE TABLE CountryLanguage (
Country char(3) NOT NULL default '',
Language char(30) NOT NULL default '',
Percentage float(3,1) NOT NULL default '0.0',
PRIMARY KEY  (Country, Language),
INDEX (Percentage)
);
SELECT COUNT(*) FROM Country;
COUNT(*)
239
SELECT COUNT(*) FROM City;
COUNT(*)
4079
SELECT COUNT(*) FROM CountryLanguage;
COUNT(*)
984
CREATE INDEX Name ON City(Name);
SET SESSION optimizer_switch='rowid_filter=off';
SET SESSION optimizer_switch='index_merge_sort_intersection=on';
SELECT COUNT(*) FROM City;
COUNT(*)
4079
SELECT COUNT(*) FROM City WHERE Name LIKE 'C%';
COUNT(*)
281
SELECT COUNT(*) FROM City WHERE Name LIKE 'M%';
COUNT(*)
301
SELECT COUNT(*) FROM City WHERE Population > 1000000;
COUNT(*)
237
SELECT COUNT(*) FROM City WHERE Population > 1500000;
COUNT(*)
129
SELECT COUNT(*) FROM City WHERE Population > 300000;
COUNT(*)
1062
SELECT COUNT(*) FROM City WHERE Population > 7000000;
COUNT(*)
14
EXPLAIN
SELECT * FROM City WHERE
Name LIKE 'C%' AND Population > 1000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
EXPLAIN
SELECT * FROM City WHERE
Name LIKE 'M%' AND Population > 1500000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
EXPLAIN
SELECT * FROM City 
WHERE Name LIKE 'M%' AND Population > 300000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	range	Population,Name	Name	35	NULL	#	Using index condition; Using where
EXPLAIN
SELECT * FROM City
WHERE Name LIKE 'M%' AND Population > 7000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
SELECT * FROM City USE INDEX ()
WHERE Name LIKE 'C%' AND Population > 1000000;
ID	Name	Country	Population
1026	Calcutta [Kolkata]	IND	4399819
1027	Chennai (Madras)	IND	3841396
151	Chittagong	BGD	1392860
1892	Chongqing	CHN	6351600
1898	Chengdu	CHN	3361500
1900	Changchun	CHN	2812000
1910	Changsha	CHN	1809800
212	Curitiba	BRA	1584232
2258	Cali	COL	2077386
2485	Casablanca	MAR	2940623
2515	Ciudad de México	MEX	8591309
3539	Caracas	VEN	1975294
3795	Chicago	USA	2896016
608	Cairo	EGY	6789479
71	Córdoba	ARG	1157507
712	Cape Town	ZAF	2352121
926	Conakry	GIN	1090610
SELECT * FROM City
WHERE Name LIKE 'C%' AND Population > 1000000;
ID	Name	Country	Population
1026	Calcutta [Kolkata]	IND	4399819
1027	Chennai (Madras)	IND	3841396
151	Chittagong	BGD	1392860
1892	Chongqing	CHN	6351600
1898	Chengdu	CHN	3361500
1900	Changchun	CHN	2812000
1910	Changsha	CHN	1809800
212	Curitiba	BRA	1584232
2258	Cali	COL	2077386
2485	Casablanca	MAR	2940623
2515	Ciudad de México	MEX	8591309
3539	Caracas	VEN	1975294
3795	Chicago	USA	2896016
608	Cairo	EGY	6789479
71	Córdoba	ARG	1157507
712	Cape Town	ZAF	2352121
926	Conakry	GIN	1090610
SELECT * FROM City USE INDEX ()
WHERE Name LIKE 'M%' AND Population > 1500000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
131	Melbourne	AUS	2865329
1381	Mashhad	IRN	1887405
2259	Medellín	COL	1861265
3520	Minsk	BLR	1674000
3580	Moscow	RUS	8389200
653	Madrid	ESP	2879052
766	Manila	PHL	1581082
942	Medan	IDN	1843919
SELECT * FROM City 
WHERE Name LIKE 'M%' AND Population > 1500000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
131	Melbourne	AUS	2865329
1381	Mashhad	IRN	1887405
2259	Medellín	COL	1861265
3520	Minsk	BLR	1674000
3580	Moscow	RUS	8389200
653	Madrid	ESP	2879052
766	Manila	PHL	1581082
942	Medan	IDN	1843919
SELECT * FROM City USE INDEX ()
WHERE Name LIKE 'M%' AND Population > 300000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
1042	Madurai	IND	977856
1051	Meerut	IND	753778
1074	Mysore	IND	480692
1081	Moradabad	IND	429214
1098	Malegaon	IND	342595
131	Melbourne	AUS	2865329
1366	Mosul	IRQ	879000
1381	Mashhad	IRN	1887405
1465	Milano	ITA	1300977
1559	Matsuyama	JPN	466133
1560	Matsudo	JPN	461126
1578	Machida	JPN	364197
1595	Miyazaki	JPN	303784
1810	Montréal	CAN	1016376
1816	Mississauga	CAN	608072
1882	Mombasa	KEN	461753
1945	Mudanjiang	CHN	570000
2005	Ma´anshan	CHN	305421
215	Manaus	BRA	1255049
223	Maceió	BRA	786288
2259	Medellín	COL	1861265
2267	Manizales	COL	337580
2300	Mbuji-Mayi	COD	806475
2348	Masan	KOR	441242
2440	Monrovia	LBR	850000
2454	Macao	MAC	437500
2487	Marrakech	MAR	621914
2491	Meknès	MAR	460000
250	Mauá	BRA	375055
2523	Monterrey	MEX	1108499
2526	Mexicali	MEX	764902
2530	Mérida	MEX	703324
2537	Morelia	MEX	619958
2554	Matamoros	MEX	416428
2557	Mazatlán	MEX	380265
256	Moji das Cruzes	BRA	339194
2698	Maputo	MOZ	1018938
2699	Matola	MOZ	424662
2711	Mandalay	MMR	885300
2712	Moulmein (Mawlamyine)	MMR	307900
2734	Managua	NIC	959000
2756	Mushin	NGA	333200
2757	Maiduguri	NGA	320000
2826	Multan	PAK	1182441
2975	Marseille	FRA	798430
3070	Munich [München]	DEU	1194560
3086	Mannheim	DEU	307730
3175	Mekka	SAU	965700
3176	Medina	SAU	608300
3214	Mogadishu	SOM	997000
3364	Mersin (Içel)	TUR	587212
3371	Malatya	TUR	330312
3434	Mykolajiv	UKR	508000
3435	Mariupol	UKR	490000
3438	Makijivka	UKR	384000
3492	Montevideo	URY	1236000
3520	Minsk	BLR	1674000
3522	Mogiljov	BLR	356000
3540	Maracaíbo	VEN	1304776
3545	Maracay	VEN	444443
3547	Maturín	VEN	319726
3580	Moscow	RUS	8389200
3622	Magnitogorsk	RUS	427900
3625	Murmansk	RUS	376300
3636	Mahat?kala	RUS	332800
3810	Memphis	USA	650100
3811	Milwaukee	USA	596974
3834	Mesa	USA	396375
3837	Minneapolis	USA	382618
3839	Miami	USA	362470
462	Manchester	GBR	430000
653	Madrid	ESP	2879052
658	Málaga	ESP	530553
661	Murcia	ESP	353504
766	Manila	PHL	1581082
77	Mar del Plata	ARG	512880
778	Makati	PHL	444867
781	Marikina	PHL	391170
783	Muntinlupa	PHL	379310
786	Malabon	PHL	338855
80	Merlo	ARG	463846
83	Moreno	ARG	356993
87	Morón	ARG	349246
942	Medan	IDN	1843919
947	Malang	IDN	716862
962	Manado	IDN	332288
963	Mataram	IDN	306600
SELECT * FROM City 
WHERE Name LIKE 'M%' AND Population > 300000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
1042	Madurai	IND	977856
1051	Meerut	IND	753778
1074	Mysore	IND	480692
1081	Moradabad	IND	429214
1098	Malegaon	IND	342595
131	Melbourne	AUS	2865329
1366	Mosul	IRQ	879000
1381	Mashhad	IRN	1887405
1465	Milano	ITA	1300977
1559	Matsuyama	JPN	466133
1560	Matsudo	JPN	461126
1578	Machida	JPN	364197
1595	Miyazaki	JPN	303784
1810	Montréal	CAN	1016376
1816	Mississauga	CAN	608072
1882	Mombasa	KEN	461753
1945	Mudanjiang	CHN	570000
2005	Ma´anshan	CHN	305421
215	Manaus	BRA	1255049
223	Maceió	BRA	786288
2259	Medellín	COL	1861265
2267	Manizales	COL	337580
2300	Mbuji-Mayi	COD	806475
2348	Masan	KOR	441242
2440	Monrovia	LBR	850000
2454	Macao	MAC	437500
2487	Marrakech	MAR	621914
2491	Meknès	MAR	460000
250	Mauá	BRA	375055
2523	Monterrey	MEX	1108499
2526	Mexicali	MEX	764902
2530	Mérida	MEX	703324
2537	Morelia	MEX	619958
2554	Matamoros	MEX	416428
2557	Mazatlán	MEX	380265
256	Moji das Cruzes	BRA	339194
2698	Maputo	MOZ	1018938
2699	Matola	MOZ	424662
2711	Mandalay	MMR	885300
2712	Moulmein (Mawlamyine)	MMR	307900
2734	Managua	NIC	959000
2756	Mushin	NGA	333200
2757	Maiduguri	NGA	320000
2826	Multan	PAK	1182441
2975	Marseille	FRA	798430
3070	Munich [München]	DEU	1194560
3086	Mannheim	DEU	307730
3175	Mekka	SAU	965700
3176	Medina	SAU	608300
3214	Mogadishu	SOM	997000
3364	Mersin (Içel)	TUR	587212
3371	Malatya	TUR	330312
3434	Mykolajiv	UKR	508000
3435	Mariupol	UKR	490000
3438	Makijivka	UKR	384000
3492	Montevideo	URY	1236000
3520	Minsk	BLR	1674000
3522	Mogiljov	BLR	356000
3540	Maracaíbo	VEN	1304776
3545	Maracay	VEN	444443
3547	Maturín	VEN	319726
3580	Moscow	RUS	8389200
3622	Magnitogorsk	RUS	427900
3625	Murmansk	RUS	376300
3636	Mahat?kala	RUS	332800
3810	Memphis	USA	650100
3811	Milwaukee	USA	596974
3834	Mesa	USA	396375
3837	Minneapolis	USA	382618
3839	Miami	USA	362470
462	Manchester	GBR	430000
653	Madrid	ESP	2879052
658	Málaga	ESP	530553
661	Murcia	ESP	353504
766	Manila	PHL	1581082
77	Mar del Plata	ARG	512880
778	Makati	PHL	444867
781	Marikina	PHL	391170
783	Muntinlupa	PHL	379310
786	Malabon	PHL	338855
80	Merlo	ARG	463846
83	Moreno	ARG	356993
87	Morón	ARG	349246
942	Medan	IDN	1843919
947	Malang	IDN	716862
962	Manado	IDN	332288
963	Mataram	IDN	306600
SELECT * FROM City USE INDEX ()
WHERE Name LIKE 'M%' AND Population > 7000000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
3580	Moscow	RUS	8389200
SELECT * FROM City
WHERE Name LIKE 'M%' AND Population > 7000000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
3580	Moscow	RUS	8389200
SELECT COUNT(*) FROM City WHERE Name BETWEEN 'M' AND 'N';
COUNT(*)
301
SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'J';
COUNT(*)
408
SELECT COUNT(*) FROM City WHERE Name BETWEEN 'G' AND 'K';
COUNT(*)
512
SELECT COUNT(*) FROM City WHERE Population > 1000000;
COUNT(*)
237
SELECT COUNT(*) FROM City WHERE Population > 500000;
COUNT(*)
539
SELECT COUNT(*) FROM City WHERE Country LIKE 'C%';
COUNT(*)
551
SELECT COUNT(*) FROM City WHERE Country LIKE 'B%';
COUNT(*)
339
SELECT COUNT(*) FROM City WHERE Country LIKE 'J%';
COUNT(*)
256
EXPLAIN
SELECT * FROM City
WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Country,Name	Population,Name,Country	4,35,3	NULL	#	Using sort_intersect(Population,Name,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Country,Name	Population,Country,Name	4,3,35	NULL	#	Using sort_intersect(Population,Country,Name); Using where
EXPLAIN
SELECT * FROM City 
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	range	Population,Name,Country	Name	#	NULL	#	Using index condition; Using where
SELECT * FROM City USE INDEX ()
WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
ID	Name	Country	Population
1810	Montréal	CAN	1016376
2259	Medellín	COL	1861265
SELECT * FROM City
WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%';
ID	Name	Country	Population
1810	Montréal	CAN	1016376
2259	Medellín	COL	1861265
SELECT * FROM City USE INDEX ()
WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%';
ID	Name	Country	Population
1533	Jokohama [Yokohama]	JPN	3339594
1541	Hiroshima	JPN	1119117
SELECT * FROM City 
WHERE Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%';
ID	Name	Country	Population
1533	Jokohama [Yokohama]	JPN	3339594
1541	Hiroshima	JPN	1119117
SELECT * FROM City USE INDEX ()
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
ID	Name	Country	Population
1895	Harbin	CHN	4289800
1904	Jinan	CHN	2278100
1905	Hangzhou	CHN	2190500
1914	Guiyang	CHN	1465200
1916	Hefei	CHN	1369100
1923	Jilin	CHN	1040000
1927	Hohhot	CHN	916700
1928	Handan	CHN	840000
1937	Huainan	CHN	700000
1938	Jixi	CHN	683885
1944	Jinzhou	CHN	570000
1950	Hegang	CHN	520000
SELECT * FROM City 
WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%';
ID	Name	Country	Population
1895	Harbin	CHN	4289800
1904	Jinan	CHN	2278100
1905	Hangzhou	CHN	2190500
1914	Guiyang	CHN	1465200
1916	Hefei	CHN	1369100
1923	Jilin	CHN	1040000
1927	Hohhot	CHN	916700
1928	Handan	CHN	840000
1937	Huainan	CHN	700000
1938	Jixi	CHN	683885
1944	Jinzhou	CHN	570000
1950	Hegang	CHN	520000
SELECT COUNT(*) FROM City WHERE ID BETWEEN 501 AND 1000;
COUNT(*)
500
SELECT COUNT(*) FROM City WHERE ID BETWEEN 1 AND 500;
COUNT(*)
500
SELECT COUNT(*) FROM City WHERE ID BETWEEN 2001 AND 2500;
COUNT(*)
500
SELECT COUNT(*) FROM City WHERE ID BETWEEN 3701 AND 4000;
COUNT(*)
300
SELECT COUNT(*) FROM City WHERE Population > 700000;
COUNT(*)
358
SELECT COUNT(*) FROM City WHERE Population > 1000000;
COUNT(*)
237
SELECT COUNT(*) FROM City WHERE Population > 300000;
COUNT(*)
1062
SELECT COUNT(*) FROM City WHERE Population > 600000;
COUNT(*)
428
SELECT COUNT(*) FROM City WHERE Country LIKE 'C%';
COUNT(*)
551
SELECT COUNT(*) FROM City WHERE Country LIKE 'A%';
COUNT(*)
107
SELECT COUNT(*) FROM City WHERE Country LIKE 'H%';
COUNT(*)
22
SELECT COUNT(*) FROM City WHERE Country BETWEEN 'S' AND 'Z';
COUNT(*)
682
EXPLAIN
SELECT * FROM City
WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,7	NULL	#	Using sort_intersect(PRIMARY,Population,Country); Using where
EXPLAIN
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,7	NULL	#	Using sort_intersect(PRIMARY,Population,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,7	NULL	#	Using sort_intersect(PRIMARY,Population,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000
AND Country BETWEEN 'S' AND 'Z';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,7	NULL	#	Using sort_intersect(PRIMARY,Population,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z' ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,7	NULL	#	Using sort_intersect(PRIMARY,Population,Country); Using where
SELECT * FROM City USE INDEX ()
WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%';
ID	Name	Country	Population
554	Santiago de Chile	CHL	4703954
SELECT * FROM City
WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%';
ID	Name	Country	Population
554	Santiago de Chile	CHL	4703954
SELECT * FROM City USE INDEX ()
WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%';
ID	Name	Country	Population
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%';
ID	Name	Country	Population
SELECT * FROM City USE INDEX ()
WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%';
ID	Name	Country	Population
2409	Zagreb	HRV	706770
SELECT * FROM City 
WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%';
ID	Name	Country	Population
2409	Zagreb	HRV	706770
SELECT * FROM City USE INDEX ()
WHERE ID BETWEEN 3701 AND 4000 AND Population > 700000
AND Country BETWEEN 'S' AND 'Z';
ID	Name	Country	Population
3769	Ho Chi Minh City	VNM	3980000
3770	Hanoi	VNM	1410000
3771	Haiphong	VNM	783133
3793	New York	USA	8008278
3794	Los Angeles	USA	3694820
3795	Chicago	USA	2896016
3796	Houston	USA	1953631
3797	Philadelphia	USA	1517550
3798	Phoenix	USA	1321045
3799	San Diego	USA	1223400
3800	Dallas	USA	1188580
3801	San Antonio	USA	1144646
3802	Detroit	USA	951270
3803	San Jose	USA	894943
3804	Indianapolis	USA	791926
3805	San Francisco	USA	776733
3806	Jacksonville	USA	735167
3807	Columbus	USA	711470
SELECT * FROM City 
WHERE ID BETWEEN 3701 AND 4000 AND Population > 700000
AND Country BETWEEN 'S' AND 'Z';
ID	Name	Country	Population
3769	Ho Chi Minh City	VNM	3980000
3770	Hanoi	VNM	1410000
3771	Haiphong	VNM	783133
3793	New York	USA	8008278
3794	Los Angeles	USA	3694820
3795	Chicago	USA	2896016
3796	Houston	USA	1953631
3797	Philadelphia	USA	1517550
3798	Phoenix	USA	1321045
3799	San Diego	USA	1223400
3800	Dallas	USA	1188580
3801	San Antonio	USA	1144646
3802	Detroit	USA	951270
3803	San Jose	USA	894943
3804	Indianapolis	USA	791926
3805	San Francisco	USA	776733
3806	Jacksonville	USA	735167
3807	Columbus	USA	711470
SELECT * FROM City USE INDEX ()
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z' ;
ID	Name	Country	Population
3048	Stockholm	SWE	750348
3173	Riyadh	SAU	3324000
3174	Jedda	SAU	2046300
3175	Mekka	SAU	965700
3176	Medina	SAU	608300
3197	Pikine	SEN	855287
3198	Dakar	SEN	785071
3207	Freetown	SLE	850000
3208	Singapore	SGP	4017733
3214	Mogadishu	SOM	997000
3224	Omdurman	SDN	1271403
3225	Khartum	SDN	947483
3226	Sharq al-Nil	SDN	700887
3250	Damascus	SYR	1347000
3251	Aleppo	SYR	1261983
3263	Taipei	TWN	2641312
3264	Kaohsiung	TWN	1475505
3265	Taichung	TWN	940589
3266	Tainan	TWN	728060
3305	Dar es Salaam	TZA	1747000
3320	Bangkok	THA	6320174
3349	Tunis	TUN	690600
3357	Istanbul	TUR	8787958
3358	Ankara	TUR	3038159
3359	Izmir	TUR	2130359
3360	Adana	TUR	1131198
3361	Bursa	TUR	1095842
3362	Gaziantep	TUR	789056
3363	Konya	TUR	628364
3425	Kampala	UGA	890800
3426	Kyiv	UKR	2624000
3427	Harkova [Harkiv]	UKR	1500000
3428	Dnipropetrovsk	UKR	1103000
3429	Donetsk	UKR	1050000
3430	Odesa	UKR	1011000
3431	Zaporizzja	UKR	848000
3432	Lviv	UKR	788000
3433	Kryvyi Rig	UKR	703000
3492	Montevideo	URY	1236000
3503	Toskent	UZB	2117500
3539	Caracas	VEN	1975294
3540	Maracaíbo	VEN	1304776
3541	Barquisimeto	VEN	877239
3542	Valencia	VEN	794246
3543	Ciudad Guayana	VEN	663713
3769	Ho Chi Minh City	VNM	3980000
3770	Hanoi	VNM	1410000
3771	Haiphong	VNM	783133
3793	New York	USA	8008278
3794	Los Angeles	USA	3694820
3795	Chicago	USA	2896016
3796	Houston	USA	1953631
3797	Philadelphia	USA	1517550
3798	Phoenix	USA	1321045
3799	San Diego	USA	1223400
3800	Dallas	USA	1188580
3801	San Antonio	USA	1144646
3802	Detroit	USA	951270
3803	San Jose	USA	894943
3804	Indianapolis	USA	791926
3805	San Francisco	USA	776733
3806	Jacksonville	USA	735167
3807	Columbus	USA	711470
3808	Austin	USA	656562
3809	Baltimore	USA	651154
3810	Memphis	USA	650100
SELECT * FROM City 
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z' ;
ID	Name	Country	Population
3048	Stockholm	SWE	750348
3173	Riyadh	SAU	3324000
3174	Jedda	SAU	2046300
3175	Mekka	SAU	965700
3176	Medina	SAU	608300
3197	Pikine	SEN	855287
3198	Dakar	SEN	785071
3207	Freetown	SLE	850000
3208	Singapore	SGP	4017733
3214	Mogadishu	SOM	997000
3224	Omdurman	SDN	1271403
3225	Khartum	SDN	947483
3226	Sharq al-Nil	SDN	700887
3250	Damascus	SYR	1347000
3251	Aleppo	SYR	1261983
3263	Taipei	TWN	2641312
3264	Kaohsiung	TWN	1475505
3265	Taichung	TWN	940589
3266	Tainan	TWN	728060
3305	Dar es Salaam	TZA	1747000
3320	Bangkok	THA	6320174
3349	Tunis	TUN	690600
3357	Istanbul	TUR	8787958
3358	Ankara	TUR	3038159
3359	Izmir	TUR	2130359
3360	Adana	TUR	1131198
3361	Bursa	TUR	1095842
3362	Gaziantep	TUR	789056
3363	Konya	TUR	628364
3425	Kampala	UGA	890800
3426	Kyiv	UKR	2624000
3427	Harkova [Harkiv]	UKR	1500000
3428	Dnipropetrovsk	UKR	1103000
3429	Donetsk	UKR	1050000
3430	Odesa	UKR	1011000
3431	Zaporizzja	UKR	848000
3432	Lviv	UKR	788000
3433	Kryvyi Rig	UKR	703000
3492	Montevideo	URY	1236000
3503	Toskent	UZB	2117500
3539	Caracas	VEN	1975294
3540	Maracaíbo	VEN	1304776
3541	Barquisimeto	VEN	877239
3542	Valencia	VEN	794246
3543	Ciudad Guayana	VEN	663713
3769	Ho Chi Minh City	VNM	3980000
3770	Hanoi	VNM	1410000
3771	Haiphong	VNM	783133
3793	New York	USA	8008278
3794	Los Angeles	USA	3694820
3795	Chicago	USA	2896016
3796	Houston	USA	1953631
3797	Philadelphia	USA	1517550
3798	Phoenix	USA	1321045
3799	San Diego	USA	1223400
3800	Dallas	USA	1188580
3801	San Antonio	USA	1144646
3802	Detroit	USA	951270
3803	San Jose	USA	894943
3804	Indianapolis	USA	791926
3805	San Francisco	USA	776733
3806	Jacksonville	USA	735167
3807	Columbus	USA	711470
3808	Austin	USA	656562
3809	Baltimore	USA	651154
3810	Memphis	USA	650100
SET SESSION sort_buffer_size = IF(@@version_compile_machine like '%64%', 2048, 1536);
EXPLAIN
SELECT * FROM City WHERE
Name LIKE 'C%' AND Population > 1000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
EXPLAIN
SELECT * FROM City WHERE
Name LIKE 'M%' AND Population > 1500000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
EXPLAIN
SELECT * FROM City 
WHERE  Name BETWEEN 'G' AND 'K' AND Population > 1000000 AND Country LIKE 'J%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Country,Name	Population,Country	4,3	NULL	#	Using sort_intersect(Population,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE  Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	range	Population,Country,Name	Name	35	NULL	#	Using index condition; Using where
EXPLAIN
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	PRIMARY,Population,Country	PRIMARY,Population,Country	4,4,7	NULL	#	Using sort_intersect(PRIMARY,Population,Country); Using where
EXPLAIN
SELECT * FROM City 
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	range	PRIMARY,Population,Country	PRIMARY	4	NULL	#	Using where
SELECT * FROM City WHERE
Name LIKE 'C%' AND Population > 1000000;
ID	Name	Country	Population
1026	Calcutta [Kolkata]	IND	4399819
1027	Chennai (Madras)	IND	3841396
151	Chittagong	BGD	1392860
1892	Chongqing	CHN	6351600
1898	Chengdu	CHN	3361500
1900	Changchun	CHN	2812000
1910	Changsha	CHN	1809800
212	Curitiba	BRA	1584232
2258	Cali	COL	2077386
2485	Casablanca	MAR	2940623
2515	Ciudad de México	MEX	8591309
3539	Caracas	VEN	1975294
3795	Chicago	USA	2896016
608	Cairo	EGY	6789479
71	Córdoba	ARG	1157507
712	Cape Town	ZAF	2352121
926	Conakry	GIN	1090610
SELECT * FROM City WHERE
Name LIKE 'M%' AND Population > 1500000;
ID	Name	Country	Population
1024	Mumbai (Bombay)	IND	10500000
131	Melbourne	AUS	2865329
1381	Mashhad	IRN	1887405
2259	Medellín	COL	1861265
3520	Minsk	BLR	1674000
3580	Moscow	RUS	8389200
653	Madrid	ESP	2879052
766	Manila	PHL	1581082
942	Medan	IDN	1843919
SELECT * FROM City 
WHERE  Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'J%';
ID	Name	Country	Population
1541	Hiroshima	JPN	1119117
SELECT * FROM City 
WHERE  Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%';
ID	Name	Country	Population
1895	Harbin	CHN	4289800
1905	Hangzhou	CHN	2190500
1914	Guiyang	CHN	1465200
1916	Hefei	CHN	1369100
1927	Hohhot	CHN	916700
1928	Handan	CHN	840000
1937	Huainan	CHN	700000
1950	Hegang	CHN	520000
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 700000 AND Country LIKE 'C%';
ID	Name	Country	Population
SELECT * FROM City 
WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000
AND Country BETWEEN 'S' AND 'Z';
ID	Name	Country	Population
3048	Stockholm	SWE	750348
3173	Riyadh	SAU	3324000
3174	Jedda	SAU	2046300
3175	Mekka	SAU	965700
3176	Medina	SAU	608300
3197	Pikine	SEN	855287
3198	Dakar	SEN	785071
3207	Freetown	SLE	850000
3208	Singapore	SGP	4017733
3214	Mogadishu	SOM	997000
3224	Omdurman	SDN	1271403
3225	Khartum	SDN	947483
3226	Sharq al-Nil	SDN	700887
3250	Damascus	SYR	1347000
3251	Aleppo	SYR	1261983
3263	Taipei	TWN	2641312
3264	Kaohsiung	TWN	1475505
3265	Taichung	TWN	940589
3266	Tainan	TWN	728060
3305	Dar es Salaam	TZA	1747000
3320	Bangkok	THA	6320174
3349	Tunis	TUN	690600
3357	Istanbul	TUR	8787958
3358	Ankara	TUR	3038159
3359	Izmir	TUR	2130359
3360	Adana	TUR	1131198
3361	Bursa	TUR	1095842
3362	Gaziantep	TUR	789056
3363	Konya	TUR	628364
3425	Kampala	UGA	890800
3426	Kyiv	UKR	2624000
3427	Harkova [Harkiv]	UKR	1500000
3428	Dnipropetrovsk	UKR	1103000
3429	Donetsk	UKR	1050000
3430	Odesa	UKR	1011000
3431	Zaporizzja	UKR	848000
3432	Lviv	UKR	788000
3433	Kryvyi Rig	UKR	703000
3492	Montevideo	URY	1236000
3503	Toskent	UZB	2117500
3539	Caracas	VEN	1975294
3540	Maracaíbo	VEN	1304776
3541	Barquisimeto	VEN	877239
3542	Valencia	VEN	794246
3543	Ciudad Guayana	VEN	663713
3769	Ho Chi Minh City	VNM	3980000
3770	Hanoi	VNM	1410000
3771	Haiphong	VNM	783133
3793	New York	USA	8008278
3794	Los Angeles	USA	3694820
3795	Chicago	USA	2896016
3796	Houston	USA	1953631
3797	Philadelphia	USA	1517550
3798	Phoenix	USA	1321045
3799	San Diego	USA	1223400
3800	Dallas	USA	1188580
3801	San Antonio	USA	1144646
3802	Detroit	USA	951270
3803	San Jose	USA	894943
3804	Indianapolis	USA	791926
3805	San Francisco	USA	776733
3806	Jacksonville	USA	735167
3807	Columbus	USA	711470
3808	Austin	USA	656562
3809	Baltimore	USA	651154
3810	Memphis	USA	650100
SET SESSION sort_buffer_size = default;
DROP INDEX Country ON City;
CREATE INDEX CountryID ON City(Country,ID);
CREATE INDEX CountryName ON City(Country,Name);
EXPLAIN
SELECT * FROM City 
WHERE Country LIKE 'M%' AND Population > 1000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,CountryID,CountryName	Population,CountryID	4,3	NULL	#	Using sort_intersect(Population,CountryID); Using where
EXPLAIN
SELECT * FROM City 
WHERE Country='USA' AND Population > 1000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,CountryID,CountryName	Population,CountryID	4,3	NULL	#	Using sort_intersect(Population,CountryID); Using where
EXPLAIN
SELECT * FROM City 
WHERE Country='USA' AND Population > 1500000 AND Name LIKE 'C%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name,CountryID,CountryName	CountryName,Population	38,4	NULL	#	Using sort_intersect(CountryName,Population); Using where
SELECT * FROM City USE INDEX ()
WHERE Country LIKE 'M%' AND Population > 1000000;
ID	Name	Country	Population
2464	Kuala Lumpur	MYS	1297526
2485	Casablanca	MAR	2940623
2515	Ciudad de México	MEX	8591309
2516	Guadalajara	MEX	1647720
2517	Ecatepec de Morelos	MEX	1620303
2518	Puebla	MEX	1346176
2519	Nezahualcóyotl	MEX	1224924
2520	Juárez	MEX	1217818
2521	Tijuana	MEX	1212232
2522	León	MEX	1133576
2523	Monterrey	MEX	1108499
2524	Zapopan	MEX	1002239
2698	Maputo	MOZ	1018938
2710	Rangoon (Yangon)	MMR	3361700
SELECT * FROM City 
WHERE Country LIKE 'M%' AND Population > 1000000;
ID	Name	Country	Population
2464	Kuala Lumpur	MYS	1297526
2485	Casablanca	MAR	2940623
2515	Ciudad de México	MEX	8591309
2516	Guadalajara	MEX	1647720
2517	Ecatepec de Morelos	MEX	1620303
2518	Puebla	MEX	1346176
2519	Nezahualcóyotl	MEX	1224924
2520	Juárez	MEX	1217818
2521	Tijuana	MEX	1212232
2522	León	MEX	1133576
2523	Monterrey	MEX	1108499
2524	Zapopan	MEX	1002239
2698	Maputo	MOZ	1018938
2710	Rangoon (Yangon)	MMR	3361700
SELECT * FROM City USE INDEX ()
WHERE Country='USA' AND Population > 1000000;
ID	Name	Country	Population
3793	New York	USA	8008278
3794	Los Angeles	USA	3694820
3795	Chicago	USA	2896016
3796	Houston	USA	1953631
3797	Philadelphia	USA	1517550
3798	Phoenix	USA	1321045
3799	San Diego	USA	1223400
3800	Dallas	USA	1188580
3801	San Antonio	USA	1144646
SELECT * FROM City 
WHERE Country='USA' AND Population > 1000000;
ID	Name	Country	Population
3793	New York	USA	8008278
3794	Los Angeles	USA	3694820
3795	Chicago	USA	2896016
3796	Houston	USA	1953631
3797	Philadelphia	USA	1517550
3798	Phoenix	USA	1321045
3799	San Diego	USA	1223400
3800	Dallas	USA	1188580
3801	San Antonio	USA	1144646
SELECT * FROM City USE INDEX ()
WHERE Country='USA' AND Population > 1500000 AND Name LIKE 'C%';
ID	Name	Country	Population
3795	Chicago	USA	2896016
SELECT * FROM City 
WHERE Country='USA' AND Population > 1500000 AND Name LIKE 'C%';
ID	Name	Country	Population
3795	Chicago	USA	2896016
EXPLAIN 
SELECT * FROM City, Country 
WHERE City.Name LIKE 'C%' AND City.Population > 1000000 AND
Country.Code=City.Country;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	index_merge	Population,Name,CountryID,CountryName	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	#	
DROP DATABASE world;
use test;
CREATE TABLE t1 (
f1 int,
f4 varchar(32),
f5 int,
PRIMARY KEY (f1),
KEY (f4)
);
INSERT INTO t1 VALUES 
(5,'H',1), (9,'g',0), (527,'i',0), (528,'y',1), (529,'S',6),
(530,'m',7), (531,'b',2), (532,'N',1), (533,'V',NULL), (534,'l',1),
(535,'M',0), (536,'w',1), (537,'j',5), (538,'l',0), (539,'n',2),
(540,'m',2), (541,'r',2), (542,'l',2), (543,'h',3),(544,'o',0),
(956,'h',0), (957,'g',0), (958,'W',5), (959,'s',3), (960,'w',0),
(961,'q',0), (962,'e',NULL), (963,'u',7), (964,'q',1), (965,'N',NULL),
(966,'e',0), (967,'t',3), (968,'e',6), (969,'f',NULL), (970,'j',0),
(971,'s',3), (972,'I',0), (973,'h',4), (974,'g',1), (975,'s',0),
(976,'r',3), (977,'x',1), (978,'v',8), (979,'j',NULL), (980,'z',7),
(981,'t',9), (982,'j',5), (983,'u',NULL), (984,'g',6), (985,'w',1),
(986,'h',1), (987,'v',0), (988,'v',0), (989,'c',2), (990,'b',7),
(991,'z',0), (992,'M',1), (993,'u',2), (994,'r',2), (995,'b',4),
(996,'A',2), (997,'u',0), (998,'a',0), (999,'j',2), (1,'I',2);
EXPLAIN
SELECT * FROM t1
WHERE (f1 < 535  OR  f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	PRIMARY,f4	PRIMARY,f4	4,39	NULL	#	Using sort_intersect(PRIMARY,f4); Using where
SELECT * FROM t1
WHERE (f1 < 535  OR  f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ;
f1	f4	f5
994	r	2
996	A	2
998	a	0
DROP TABLE t1;
SET SESSION optimizer_switch='index_merge_sort_intersection=on';
SET SESSION optimizer_switch='rowid_filter=default';
set global innodb_stats_persistent= @innodb_stats_persistent_save;
set global innodb_stats_persistent_sample_pages=
@innodb_stats_persistent_sample_pages_save;
SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT;