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
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
|
SET SESSION STORAGE_ENGINE='InnoDB';
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='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 range Population,Name Population,Name 4,35 NULL 16 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 range Population,Name Population,Name 4,35 NULL 9 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,Population 35,4 NULL 79 Using sort_intersect(Name,Population); 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 range Population,Name Population,Name 4,35 NULL 1 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
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 range Population,Country,Name Population,Name,Country 4,35,3 NULL 2 Using sort_intersect(Population,Name,Country); Using where
EXPLAIN
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE City range Population,Country,Name Population,Country,Name 4,3,35 NULL 2 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,Country,Name Population,Name,Country 4,35,3 NULL 26 Using sort_intersect(Population,Name,Country); 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 'J' AND Population > 1000000 AND Country LIKE 'B%';
ID Name Country Population
217 Guarulhos BRA 1095874
218 Goiânia BRA 1056330
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%';
ID Name Country Population
217 Guarulhos BRA 1095874
218 Goiânia BRA 1056330
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 range PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL 11 Using sort_intersect(PRIMARY,Country,Population); Using where
EXPLAIN
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE City range PRIMARY,Population,Country PRIMARY,Population,Country 4,4,3 NULL 1 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 range PRIMARY,Population,Country PRIMARY,Country 4,3 NULL 7 Using sort_intersect(PRIMARY,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 range PRIMARY,Population,Country PRIMARY,Country,Population 4,3,4 NULL 2 Using sort_intersect(PRIMARY,Country,Population); 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,Population,Country 4,4,3 NULL 30 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 > 1000000 AND Country LIKE 'A%';
ID Name Country Population
1 Kabul AFG 1780000
126 Yerevan ARM 1248700
130 Sydney AUS 3276207
131 Melbourne AUS 2865329
132 Brisbane AUS 1291117
133 Perth AUS 1096829
144 Baku AZE 1787800
56 Luanda AGO 2022000
69 Buenos Aires ARG 2982146
70 La Matanza ARG 1266461
71 Córdoba ARG 1157507
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%';
ID Name Country Population
1 Kabul AFG 1780000
126 Yerevan ARM 1248700
130 Sydney AUS 3276207
131 Melbourne AUS 2865329
132 Brisbane AUS 1291117
133 Perth AUS 1096829
144 Baku AZE 1787800
56 Luanda AGO 2022000
69 Buenos Aires ARG 2982146
70 La Matanza ARG 1266461
71 Córdoba ARG 1157507
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 = 2048;
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 range Population,Name Population,Name 4,35 NULL 16 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 range Population,Name Population,Name 4,35 NULL 9 Using sort_intersect(Population,Name); Using where
EXPLAIN
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE City range Population,Country,Name Population,Country,Name 4,3,35 NULL 2 Using sort_intersect(Population,Country,Name); 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,Population,Country 35,4,3 NULL 7 Using sort_intersect(Name,Population,Country); Using where
EXPLAIN
SELECT * FROM City
WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE City range PRIMARY,Population,Country PRIMARY,Population,Country 4,4,3 NULL 1 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,Country,Population 4,3,4 NULL 30 Using sort_intersect(PRIMARY,Country,Population); 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
131 Melbourne AUS 2865329
653 Madrid ESP 2879052
766 Manila PHL 1581082
942 Medan IDN 1843919
1024 Mumbai (Bombay) IND 10500000
1381 Mashhad IRN 1887405
2259 Medellín COL 1861265
3520 Minsk BLR 1674000
3580 Moscow RUS 8389200
SELECT * FROM City
WHERE Name BETWEEN 'G' AND 'J' AND Population > 700000 AND Country LIKE 'B%';
ID Name Country Population
217 Guarulhos BRA 1095874
218 Goiânia BRA 1056330
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 > 1000000 AND Country LIKE 'A%';
ID Name Country Population
1 Kabul AFG 1780000
56 Luanda AGO 2022000
69 Buenos Aires ARG 2982146
70 La Matanza ARG 1266461
71 Córdoba ARG 1157507
126 Yerevan ARM 1248700
130 Sydney AUS 3276207
131 Melbourne AUS 2865329
132 Brisbane AUS 1291117
133 Perth AUS 1096829
144 Baku AZE 1787800
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 range Population,CountryID,CountryName Population,CountryID 4,3 NULL 16 Using sort_intersect(Population,CountryID); Using where
EXPLAIN
SELECT * FROM City
WHERE Country='CHN' AND Population > 1500000;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE City range Population,CountryID,CountryName Population,CountryID 4,3 NULL 11 Using sort_intersect(Population,CountryID); Using where
EXPLAIN
SELECT * FROM City
WHERE Country='CHN' AND Population > 1500000 AND Name LIKE 'C%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE City range Population,Name,CountryID,CountryName CountryName,Population 38,4 NULL 1 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='CHN' AND Population > 1500000;
ID Name Country Population
1890 Shanghai CHN 9696300
1891 Peking CHN 7472000
1892 Chongqing CHN 6351600
1893 Tianjin CHN 5286800
1894 Wuhan CHN 4344600
1895 Harbin CHN 4289800
1896 Shenyang CHN 4265200
1897 Kanton [Guangzhou] CHN 4256300
1898 Chengdu CHN 3361500
1899 Nanking [Nanjing] CHN 2870300
1900 Changchun CHN 2812000
1901 Xi´an CHN 2761400
1902 Dalian CHN 2697000
1903 Qingdao CHN 2596000
1904 Jinan CHN 2278100
1905 Hangzhou CHN 2190500
1906 Zhengzhou CHN 2107200
1907 Shijiazhuang CHN 2041500
1908 Taiyuan CHN 1968400
1909 Kunming CHN 1829500
1910 Changsha CHN 1809800
1911 Nanchang CHN 1691600
1912 Fuzhou CHN 1593800
1913 Lanzhou CHN 1565800
SELECT * FROM City
WHERE Country='CHN' AND Population > 1500000;
ID Name Country Population
1890 Shanghai CHN 9696300
1891 Peking CHN 7472000
1892 Chongqing CHN 6351600
1893 Tianjin CHN 5286800
1894 Wuhan CHN 4344600
1895 Harbin CHN 4289800
1896 Shenyang CHN 4265200
1897 Kanton [Guangzhou] CHN 4256300
1898 Chengdu CHN 3361500
1899 Nanking [Nanjing] CHN 2870300
1900 Changchun CHN 2812000
1901 Xi´an CHN 2761400
1902 Dalian CHN 2697000
1903 Qingdao CHN 2596000
1904 Jinan CHN 2278100
1905 Hangzhou CHN 2190500
1906 Zhengzhou CHN 2107200
1907 Shijiazhuang CHN 2041500
1908 Taiyuan CHN 1968400
1909 Kunming CHN 1829500
1910 Changsha CHN 1809800
1911 Nanchang CHN 1691600
1912 Fuzhou CHN 1593800
1913 Lanzhou CHN 1565800
SELECT * FROM City USE INDEX ()
WHERE Country='CHN' AND Population > 1500000 AND Name LIKE 'C%';
ID Name Country Population
1892 Chongqing CHN 6351600
1898 Chengdu CHN 3361500
1900 Changchun CHN 2812000
1910 Changsha CHN 1809800
SELECT * FROM City
WHERE Country='CHN' AND Population > 1500000 AND Name LIKE 'C%';
ID Name Country Population
1892 Chongqing CHN 6351600
1898 Chengdu CHN 3361500
1900 Changchun CHN 2812000
1910 Changsha CHN 1809800
DROP DATABASE world;
use test;
CREATE TABLE t1 (
f1 int,
f4 varchar(32),
f5 int,
PRIMARY KEY (f1),
KEY (f4)
) ENGINE=InnoDB;
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 range PRIMARY,f4 PRIMARY,f4 4,35 NULL 1 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 STORAGE_ENGINE=DEFAULT;
|