Оптимизация запросов Mysql

#mysql #sql #query-optimization #zipcode

#mysql #sql #оптимизация запросов #почтовый индекс

Вопрос:

Я должен отображать списки, которые находятся в определенном регионе, используя почтовый индекс. Я имею в виду, что если входной почтовый индекс равен L6M2V6, то я должен выбрать диапазон почтовых индексов около 5 миль, а затем отобразить результаты в порядке, ближайшем к первому. Вот упрощенный запрос для вашей справки.

 select
  TR.firstName,
  TR.LastName,
  TC.*
from
  tutor_register as TR,
  tutor_user_content as TC
where TR.Id=TC.tuotr_id
  and TC.status='Y'
  and (TC.ContentType='basic' or TC.ContentType='premium')
  and (TC.ZipCode like '%,L6M2V6,%' OR TC.ZipCode like '%,L6M2V5,%'
    OR TC.ZipCode like '%,L6M2E9,%' OR TC.ZipCode like '%,L6M2G1,%'
    OR TC.ZipCode like '%,L6M2E8,%' OR TC.ZipCode like '%,L6M2E7,%'
    OR TC.ZipCode like '%,L6M2E5,%' OR TC.ZipCode like '%,L6M2E6,%'
    OR TC.ZipCode like '%,L6M 4X8,%' OR TC.ZipCode like '%,L6M4X8,%'
    OR TC.ZipCode like '%,L6M1E7,%' OR TC.ZipCode like '%,L6M1A2,%'
    OR TC.ZipCode like '%,L6M1K4,%' OR TC.ZipCode like '%,L6M1H4,%'
    OR TC.ZipCode like '%,L6M0H4,%' OR TC.ZipCode like '%,L6M1C4,%'
    OR TC.ZipCode like '%,L6M2G2,%' OR TC.ZipCode like '%,L6M1G2,%'
    OR TC.ZipCode like '%,L6M1G5,%' OR TC.ZipCode like '%,L6M1G7,%'
    OR TC.ZipCode like '%,L6K2H4,%' OR TC.ZipCode like '%,L6M1E8,%'
    OR TC.ZipCode like '%,L6M1E5,%' OR TC.ZipCode like '%,L6M1G6,%'
    OR TC.ZipCode like '%,L6M1B1,%' OR TC.ZipCode like '%,L6L5B8,%'
    OR TC.ZipCode like '%,L6M1A8,%' OR TC.ZipCode like '%,L6L6R3,%'
    OR TC.ZipCode like '%,L6M1C5,%' OR TC.ZipCode like '%,L6M1N1,%'
    OR TC.ZipCode like '%,L6L6L7,%' OR TC.ZipCode like '%,L6M1B2,%'
    OR TC.ZipCode like '%,L6M1A9,%' OR TC.ZipCode like '%,L6M2G3,%'
    OR TC.ZipCode like '%,L6M1E1,%' OR TC.ZipCode like '%,L6M1C1,%'
    OR TC.ZipCode like '%,L6M1E4,%' OR TC.ZipCode like '%,L6M1A6,%'
    OR TC.ZipCode like '%,L6K2H1,%' OR TC.ZipCode like '%,L6M1B9,%'
    OR TC.ZipCode like '%,L6M1G1,%' OR TC.ZipCode like '%,L6M1E3,%'
    OR TC.ZipCode like '%,L6M1A1,%' OR TC.ZipCode like '%,L6M1G8,%'
    OR TC.ZipCode like '%,L6L5T7,%' OR TC.ZipCode like '%,L6M1E2,%'
    OR TC.ZipCode like '%,L6M1A5,%' OR TC.ZipCode like '%,L6M1M2,%'
    OR TC.ZipCode like '%,L6M1B4,%' OR TC.ZipCode like '%,L6M1B3,%'
    OR TC.ZipCode like '%,L6M3E3,%' OR TC.ZipCode like '%,L6M1A7,%'
    OR TC.ZipCode like '%,L6M3B6,%' OR TC.ZipCode like '%,L6M1M7,%'
    OR TC.ZipCode like '%,L6M1M8,%' OR TC.ZipCode like '%,L6L6K3,%'
    OR TC.ZipCode like '%,L6L6X7,%' OR TC.ZipCode like '%,L6L6R6,%'
    OR TC.ZipCode like '%,L6M1C2,%' OR TC.ZipCode like '%,L6M1C3,%'
    OR TC.ZipCode like '%,L6M1G3,%' OR TC.ZipCode like '%,L6M1S4,%'
    OR TC.ZipCode like '%,L6M3B5,%' OR TC.ZipCode like '%,L6M1G9,%'
    OR TC.ZipCode like '%,L6M1G4,%' OR TC.ZipCode like '%,L6M1E9,%'
    OR TC.ZipCode like '%,L6M1E6,%' OR TC.ZipCode like '%,L6M2V9,%'
    OR TC.ZipCode like '%,L6M2R7,%' OR TC.ZipCode like '%,L6M2E1,%'
    OR TC.ZipCode like '%,L6M1A4,%' OR TC.ZipCode like '%,L6M1C6,%'
    OR TC.ZipCode like '%,L6M2S1,%' OR TC.ZipCode like '%,L6M2S2,%'
    OR TC.ZipCode like '%,L6M2W1,%' OR TC.ZipCode like '%,L6L6J7,%'
    OR TC.ZipCode like '%,L6K2G7,%' OR TC.ZipCode like '%,L6M2R9,%'
    OR TC.ZipCode like '%,L6M1H1,%' OR TC.ZipCode like '%,L6M1C7,%'
    OR TC.ZipCode like '%,L6M1A3,%' OR TC.ZipCode like '%,L6L5B7,%'
    OR TC.ZipCode like '%,L6M1H3,%' OR TC.ZipCode like '%,L6M1K1,%'
    OR TC.ZipCode like '%,L6M1B6,%' OR TC.ZipCode like '%,L6M3B4,%'
    OR TC.ZipCode like '%,L6M1M9,%' OR TC.ZipCode like '%,L6M1J4,%'
    OR TC.ZipCode like '%,L6M1H8,%' OR TC.ZipCode like '%,L6M1B7,%'
    OR TC.ZipCode like '%,L6M1B5,%' OR TC.ZipCode like '%,L6L6N3,%'
    OR TC.ZipCode like '%,L6M2V8,%' OR TC.ZipCode like '%,L6M1H2,%'
    OR TC.ZipCode like '%,L6M2E2,%' OR TC.ZipCode like '%,L6M1P1,%'
    OR TC.ZipCode like '%,L6M2R8,%' OR TC.ZipCode like '%,L6M1C8,%'
    OR TC.ZipCode like '%,L6M1M6,%' OR TC.ZipCode like '%,L6M1H5,%'
    OR TC.ZipCode like '%,L6M1H6,%' OR TC.ZipCode like '%,L6M3Y4,%'
    OR TC.ZipCode like '%,L6M1C9,%' OR TC.ZipCode like '%,L6M1K5,%'
    OR TC.ZipCode like '%,L6M2E3,%' OR TC.ZipCode like '%,L6M1H7,%'
    OR TC.ZipCode like '%,L6M1B8,%' OR TC.ZipCode like '%,L6K3S1,%'
    OR TC.ZipCode like '%,L6L6K4,%' OR TC.ZipCode like '%,L6M1M5,%'
    OR TC.ZipCode like '%,L6L5B5,%' OR TC.ZipCode like '%,L6M2E4,%'
    OR TC.ZipCode like '%,L6M1R4,%' OR TC.ZipCode like '%,L6M3B7,%'
    OR TC.ZipCode like '%,L6M3B8,%' OR TC.ZipCode like '%,L6L5B4,%'
    OR TC.ZipCode like '%,L6M2C9,%' OR TC.ZipCode like '%,L6M3B3,%'
    OR TC.ZipCode like '%,L6M5C6,%' OR TC.ZipCode like '%,L6M 5C6,%'
    OR TC.ZipCode like '%,L6M3B9,%' OR TC.ZipCode like '%,L6M1R8,%'
    OR TC.ZipCode like '%,L6M2C7,%' OR TC.ZipCode like '%,L6M2C8,%'
    OR TC.ZipCode like '%,L6M1J3,%' OR TC.ZipCode like '%,L6M3E7,%'
    OR TC.ZipCode like '%,L6K2G4,%' OR TC.ZipCode like '%,L6M2C6,%'
    OR TC.ZipCode like '%,L6M1J2,%' OR TC.ZipCode like '%,L6L5B3,%'
    OR TC.ZipCode like '%,L6K2G3,%' OR TC.ZipCode like '%,L6L2X4,%'
    OR TC.ZipCode like '%,L6M3E4,%' OR TC.ZipCode like '%,L6M1J1,%'
    OR TC.ZipCode like '%,L6K2G2,%' OR TC.ZipCode like '%,L6L5B2,%'
    OR TC.ZipCode like '%,L6M1K2,%' OR TC.ZipCode like '%,L6M3H9,%'
    OR TC.ZipCode like '%,L6K3X5,%' OR TC.ZipCode like '%,L6M2C5,%'
    OR TC.ZipCode like '%,L6K3S9,%' OR TC.ZipCode like '%,L6M4V9,%'
    OR TC.ZipCode like '%,L6M3E6,%' OR TC.ZipCode like '%,L6M 4V8,%'
    OR TC.ZipCode like '%,L6M4V8,%' OR TC.ZipCode like '%,L6M 4V9,%'
    OR TC.ZipCode like '%,L6M1J5,%' OR TC.ZipCode like '%,L6L5W4,%'
    OR TC.ZipCode like '%,L6M3E5,%' OR TC.ZipCode like '%,L6M1J6,%'
    OR TC.ZipCode like '%,L6M3E8,%' OR TC.ZipCode like '%,L6M3Z5,%'
    OR TC.ZipCode like '%,L6M1J7,%' OR TC.ZipCode like '%,L6M 4X7,%'
    OR TC.ZipCode like '%,L6M4X7,%' OR TC.ZipCode like '%,L6K2H2,%'
    OR TC.ZipCode like '%,L6K3T2,%' OR TC.ZipCode like '%,L6M2Y5,%'
    OR TC.ZipCode like '%,L6M2L6,%' OR TC.ZipCode like '%,L6L0B1,%'
    OR TC.ZipCode like '%,L6M2C1,%' OR TC.ZipCode like '%,L6K3Y5,%'
    OR TC.ZipCode like '%,L6M1K3,%' OR TC.ZipCode like '%,L6K3W9,%'
    OR TC.ZipCode like '%,L6M1H9,%' OR TC.ZipCode like '%,L6M3G4,%'
    OR TC.ZipCode like '%,L6M3M3,%' OR TC.ZipCode like '%,L6M4G2,%'
    OR TC.ZipCode like '%,L6M1L2,%' OR TC.ZipCode like '%,L6M3E9,%'
    OR TC.ZipCode like '%,L6M 4X6,%' OR TC.ZipCode like '%,L6M2C2,%'
    OR TC.ZipCode like '%,L6M4X6,%' OR TC.ZipCode like '%,L6M1K9,%'
    OR TC.ZipCode like '%,L6M3G1,%' OR TC.ZipCode like '%,L6M1J8,%'
    OR TC.ZipCode like '%,L6M2N1,%' OR TC.ZipCode like '%,L6K3W7,%'
    OR TC.ZipCode like '%,L6M2B8,%' OR TC.ZipCode like '%,L6M3G5,%'
    OR TC.ZipCode like '%,L6M3H7,%' OR TC.ZipCode like '%,L6M1J9,%'
    OR TC.ZipCode like '%,L6K2C5,%' OR TC.ZipCode like '%,L6K3Y1,%'
    OR TC.ZipCode like '%,L6M1K6,%' OR TC.ZipCode like '%,L6K2C6,%'
    OR TC.ZipCode like '%,L6K3V3,%' OR TC.ZipCode like '%,L6M3Z4,%'
    OR TC.ZipCode like '%,L6M3R2,%' OR TC.ZipCode like '%,L6M 4V7,%'
    OR TC.ZipCode like '%,L6L2W3,%' OR TC.ZipCode like '%,L6L2W2,%'
    OR TC.ZipCode like '%,L6M4V7,%' OR TC.ZipCode like '%,L6M2M9,%'
    OR TC.ZipCode like '%,L6L5A7,%' OR TC.ZipCode like '%,L6K3V7,%'
    OR TC.ZipCode like '%,L6M2Y4,%' OR TC.ZipCode like '%,L6M2C4,%'
    OR TC.ZipCode like '%,L6K2C4,%' OR TC.ZipCode like '%,L6L2W4,%'
    OR TC.ZipCode like '%,L6M3G6,%' OR TC.ZipCode like '%,L6L5B1,%'
    OR TC.ZipCode like '%,L6M2C3,%' OR TC.ZipCode like '%,L6K3S4,%'
    OR TC.ZipCode like '%,L6K2L9,%' OR TC.ZipCode like '%,L6K2C2,%'
    OR TC.ZipCode like '%,L6K2C3,%' OR TC.ZipCode like '%,L6K3T7,%'
    OR TC.ZipCode like '%,L6K2C1,%' OR TC.ZipCode like '%,L6K2M1,%'
    OR TC.ZipCode like '%,L6K3W4,%' OR TC.ZipCode like '%,L6M2L7,%'
    OR TC.ZipCode like '%,L6K3S8,%' OR TC.ZipCode like '%,L6M2L9,%'
    OR TC.ZipCode like '%,L6M2Y3,%' OR TC.ZipCode like '%,L6K3S7,%'
    OR TC.ZipCode like '%,L6K2L4,%' OR TC.ZipCode like '%,L6M2A9,%'
    OR TC.ZipCode like '%,L6K2N1,%' OR TC.ZipCode like '%,L6M1V7,%'
    OR TC.ZipCode like '%,L6K1Y3,%' OR TC.ZipCode like '%,L6K2M7,%'
    OR TC.ZipCode like '%,L6M2B1,%' OR TC.ZipCode like '%,L6K3T5,%'
    OR TC.ZipCode like '%,L6K2B9,%' OR TC.ZipCode like '%,L6K3X9,%'
    OR TC.ZipCode like '%,L6K2M9,%' OR TC.ZipCode like '%,L6M1V8,%'
    OR TC.ZipCode like '%,L6M2M1,%' OR TC.ZipCode like '%,L6M2N6,%'
    OR TC.ZipCode like '%,L6K2B6,%' OR TC.ZipCode like '%,L6M2Y2,%'
    OR TC.ZipCode like '%,L6M2L8,%' OR TC.ZipCode like '%,L6L5A9,%'
    OR TC.ZipCode like '%,L6L2W5,%' OR TC.ZipCode like '%,L6M1L1,%'
    OR TC.ZipCode like '%,L6M2B9,%' OR TC.ZipCode like '%,L6K1Z9,%'
    OR TC.ZipCode like '%,L6K3W6,%' OR TC.ZipCode like '%,L6K2A1,%'
    OR TC.ZipCode like '%,L6K2B8,%' OR TC.ZipCode like '%,L6K2H3,%'
    OR TC.ZipCode like '%,L6K2M8,%' OR TC.ZipCode like '%,L6K1Y2,%'
    OR TC.ZipCode like '%,L6L2W6,%' OR TC.ZipCode like '%,L6M2B6,%'
    OR TC.ZipCode like '%,L6M1V3,%' OR TC.ZipCode like '%,L6M2W2,%'
    OR TC.ZipCode like '%,L6L4X8,%' OR TC.ZipCode like '%,L6M1V6,%'
    OR TC.ZipCode like '%,L6K2L3,%' OR TC.ZipCode like '%,L6M2Y1,%'
    OR TC.ZipCode like '%,L6K2T4,%' OR TC.ZipCode like '%,L6K2Y1,%'
    OR TC.ZipCode like '%,L6K3S3,%' OR TC.ZipCode like '%,L6K2T3,%'
    OR TC.ZipCode like '%,L6K1V5,%' OR TC.ZipCode like '%,L6K1V3,%'
    OR TC.ZipCode like '%,L6M1K8,%' OR TC.ZipCode like '%,L6K2L2,%'
    OR TC.ZipCode like '%,L6K2R7,%' OR TC.ZipCode like '%,L6M2N2,%'
    OR TC.ZipCode like '%,L6K3Y6,%' OR TC.ZipCode like '%,L6K1Z6,%'
    OR TC.ZipCode like '%,L6M2B7,%' OR TC.ZipCode like '%,L6M1K7,%'
    OR TC.ZipCode like '%,L6K1Z8,%' OR TC.ZipCode like '%,L6K1V4,%'
    OR TC.ZipCode like '%,L6M1V4,%' OR TC.ZipCode like '%,L6K2P8,%'
    OR TC.ZipCode like '%,L6K2M6,%' OR TC.ZipCode like '%,L6L5A8,%'
    OR TC.ZipCode like '%,L6K1V2,%' OR TC.ZipCode like '%,L6K1Z7,%'
    OR TC.ZipCode like '%,L6M2B2,%' OR TC.ZipCode like '%,L6M2B3,%'
    OR TC.ZipCode like '%,L6M2B4,%' OR TC.ZipCode like '%,L6M1V5,%'
    OR TC.ZipCode like '%,L6H0B4,%' OR TC.ZipCode like '%,L6M2B5,%'
    OR TC.ZipCode like '%,L6L2W7,%' OR TC.ZipCode like '%,L6K2P7,%'
    OR TC.ZipCode like '%,L6L5B6,%' OR TC.ZipCode like '%,L6K2M5,%'
    OR TC.ZipCode like '%,L6K3X7,%' OR TC.ZipCode like '%,L6K1V1,%'
    OR TC.ZipCode like '%,L6M2W8,%' OR TC.ZipCode like '%,L6K1T9,%'
    OR TC.ZipCode like '%,L6K2L1,%' OR TC.ZipCode like '%,L6M2Y6,%'
    OR TC.ZipCode like '%,L6K1T8,%' OR TC.ZipCode like '%,L6M2N3,%'
    OR TC.ZipCode like '%,L6M2A5,%' OR TC.ZipCode like '%,L6K2N8,%'
    OR TC.ZipCode like '%,L6K2N7,%' OR TC.ZipCode like '%,L6M2A4,%'
    OR TC.ZipCode like '%,L6L2W8,%' OR TC.ZipCode like '%,L6K2K8,%'
    OR TC.ZipCode like '%,L6L4W3,%' OR TC.ZipCode like '%,L6L4W2,%'
    OR TC.ZipCode like '%,L6M2N4,%' OR TC.ZipCode like '%,L6M2N5,%'
    OR TC.ZipCode like '%,L6L4W4,%' OR TC.ZipCode like '%,L6L4T8,%'
    OR TC.ZipCode like '%,L6L5N9,%' OR TC.ZipCode like '%,L6K1S9,%'
    OR TC.ZipCode like '%,L6H1E8,%' OR TC.ZipCode like '%,L6K1T1,%'
    OR TC.ZipCode like '%,L6H1R5,%' OR TC.ZipCode like '%,L6L2X5,%'
    OR TC.ZipCode like '%,L6K2M4,%' OR TC.ZipCode like '%,L6M2X9,%'
    OR TC.ZipCode like '%,L6M3B2,%' OR TC.ZipCode like '%,L6H1S2,%'
    OR TC.ZipCode like '%,L6K1P7,%' OR TC.ZipCode like '%,L6K1P6,%'
    OR TC.ZipCode like '%,L6L5T9,%' OR TC.ZipCode like '%,L6H1E9,%'
    OR TC.ZipCode like '%,L6L2W9,%' OR TC.ZipCode like '%,L6L4X5,%'
    OR TC.ZipCode like '%,L6L4X4,%' OR TC.ZipCode like '%,L6L4X3,%'
    OR TC.ZipCode like '%,L6M5C8,%' OR TC.ZipCode like '%,L6M 5C7,%'
    OR TC.ZipCode like '%,L6M 5C8,%' OR TC.ZipCode like '%,L6M 5C9,%'
    OR TC.ZipCode like '%,L6M 5E1,%' OR TC.ZipCode like '%,L6L0A6,%'
    OR TC.ZipCode like '%,L6H0B8,%' OR TC.ZipCode like '%,L6M5C7,%'
    OR TC.ZipCode like '%,L6L4X6,%' OR TC.ZipCode like '%,L6M5C9,%'
    OR TC.ZipCode like '%,L6M5E1,%' OR TC.ZipCode like '%,L6L4W1,%'
    OR TC.ZipCode like '%,L6M0B8,%' OR TC.ZipCode like '%,L6H1R3,%'
    OR TC.ZipCode like '%,L6H1S1,%' OR TC.ZipCode like '%,L6H7V8,%'
    OR TC.ZipCode like '%,L6H7V4,%' OR TC.ZipCode like '%,L6H7V3,%'
    OR TC.ZipCode like '%,L6M0B6,%' OR TC.ZipCode like '%,L6M2N9,%'
    OR TC.ZipCode like '%,L6K3V1,%' OR TC.ZipCode like '%,L6M3G7,%'
    OR TC.ZipCode like '%,L6L4Z1,%' OR TC.ZipCode like '%,L6L6K1,%'
    OR TC.ZipCode like '%,L6K2K9,%' OR TC.ZipCode like '%,L6K1P8,%'
    OR TC.ZipCode like '%,L6L4X7,%' OR TC.ZipCode like '%,L6L4T7,%'
    OR TC.ZipCode like '%,L6M3R3,%' OR TC.ZipCode like '%,L6H1S4,%'
    OR TC.ZipCode like '%,L6H1R4,%' OR TC.ZipCode like '%,L6H1S3,%'
    OR TC.ZipCode like '%,L6H1S7,%' OR TC.ZipCode like '%,L6H1S5,%'
    OR TC.ZipCode like '%,L6L4R6,%' OR TC.ZipCode like '%,L6L4R7,%'
    OR TC.ZipCode like '%,L6K1R6,%' OR TC.ZipCode like '%,L6L4Y9,%'
    OR TC.ZipCode like '%,L6K1R5,%' OR TC.ZipCode like '%,L6K2J6,%'
    OR TC.ZipCode like '%,L6K2J1,%' OR TC.ZipCode like '%,L6M2W4,%'
    OR TC.ZipCode like '%,L6L2X1,%'
  )
order by
  TC.ContentType DESC,
  ( CASE
    when ',L6M2V6,' then -1 when ',L6M2V5,' then 0 when ',L6M2E9,' then 1
    when ',L6M2G1,' then 2 when ',L6M2E8,' then 3 when ',L6M2E7,' then 4
    when ',L6M2E5,' then 5 when ',L6M2E6,' then 6 when ',L6M 4X8,' then 7
    when ',L6M4X8,' then 8 when ',L6M1E7,' then 9 when ',L6M1A2,' then 10
    when ',L6M1K4,' then 11 when ',L6M1H4,' then 12 when ',L6M0H4,' then 13
    when ',L6M1C4,' then 14 when ',L6M2G2,' then 15 when ',L6M1G2,' then 16
    when ',L6M1G5,' then 17 when ',L6M1G7,' then 18 when ',L6K2H4,' then 19
    when ',L6M1E8,' then 20 when ',L6M1E5,' then 21 when ',L6M1G6,' then 22
    when ',L6M1B1,' then 23 when ',L6L5B8,' then 24 when ',L6M1A8,' then 25
    when ',L6L6R3,' then 26 when ',L6M1C5,' then 27 when ',L6M1N1,' then 28
    when ',L6L6L7,' then 29 when ',L6M1B2,' then 30 when ',L6M1A9,' then 31
    when ',L6M2G3,' then 32 when ',L6M1E1,' then 33 when ',L6M1C1,' then 34
    when ',L6M1E4,' then 35 when ',L6M1A6,' then 36 when ',L6K2H1,' then 37
    when ',L6M1B9,' then 38 when ',L6M1G1,' then 39 when ',L6M1E3,' then 40
    when ',L6M1A1,' then 41 when ',L6M1G8,' then 42 when ',L6L5T7,' then 43
    when ',L6M1E2,' then 44 when ',L6M1A5,' then 45 when ',L6M1M2,' then 46
    when ',L6M1B4,' then 47 when ',L6M1B3,' then 48 when ',L6M3E3,' then 49
    when ',L6M1A7,' then 50 when ',L6M3B6,' then 51 when ',L6M1M7,' then 52
    when ',L6M1M8,' then 53 when ',L6L6K3,' then 54 when ',L6L6X7,' then 55
    when ',L6L6R6,' then 56 when ',L6M1C2,' then 57 when ',L6M1C3,' then 58
    when ',L6M1G3,' then 59 when ',L6M1S4,' then 60 when ',L6M3B5,' then 61
    when ',L6M1G9,' then 62 when ',L6M1G4,' then 63 when ',L6M1E9,' then 64
    when ',L6M1E6,' then 65 when ',L6M2V9,' then 66 when ',L6M2R7,' then 67
    when ',L6M2E1,' then 68 when ',L6M1A4,' then 69 when ',L6M1C6,' then 70
    when ',L6M2S1,' then 71 when ',L6M2S2,' then 72 when ',L6M2W1,' then 73
    when ',L6L6J7,' then 74 when ',L6K2G7,' then 75 when ',L6M2R9,' then 76
    when ',L6M1H1,' then 77 when ',L6M1C7,' then 78 when ',L6M1A3,' then 79
    when ',L6L5B7,' then 80 when ',L6M1H3,' then 81 when ',L6M1K1,' then 82
    when ',L6M1B6,' then 83 when ',L6M3B4,' then 84 when ',L6M1M9,' then 85
    when ',L6M1J4,' then 86 when ',L6M1H8,' then 87 when ',L6M1B7,' then 88
    when ',L6M1B5,' then 89 when ',L6L6N3,' then 90 when ',L6M2V8,' then 91
    when ',L6M1H2,' then 92 when ',L6M2E2,' then 93 when ',L6M1P1,' then 94
    when ',L6M2R8,' then 95 when ',L6M1C8,' then 96 when ',L6M1M6,' then 97
    when ',L6M1H5,' then 98 when ',L6M1H6,' then 99 when ',L6M3Y4,' then 100
    when ',L6M1C9,' then 101 when ',L6M1K5,' then 102 when ',L6M2E3,' then 103
    when ',L6M1H7,' then 104 when ',L6M1B8,' then 105 when ',L6K3S1,' then 106
    when ',L6L6K4,' then 107 when ',L6M1M5,' then 108 when ',L6L5B5,' then 109
    when ',L6M2E4,' then 110 when ',L6M1R4,' then 111 when ',L6M3B7,' then 112
    when ',L6M3B8,' then 113 when ',L6L5B4,' then 114 when ',L6M2C9,' then 115
    when ',L6M3B3,' then 116 when ',L6M5C6,' then 117 when ',L6M 5C6,' then 118
    when ',L6M3B9,' then 119 when ',L6M1R8,' then 120 when ',L6M2C7,' then 121
    when ',L6M2C8,' then 122 when ',L6M1J3,' then 123 when ',L6M3E7,' then 124
    when ',L6K2G4,' then 125 when ',L6M2C6,' then 126 when ',L6M1J2,' then 127
    when ',L6L5B3,' then 128 when ',L6K2G3,' then 129 when ',L6L2X4,' then 130
    when ',L6M3E4,' then 131 when ',L6M1J1,' then 132 when ',L6K2G2,' then 133
    when ',L6L5B2,' then 134 when ',L6M1K2,' then 135 when ',L6M3H9,' then 136
    when ',L6K3X5,' then 137 when ',L6M2C5,' then 138 when ',L6K3S9,' then 139
    when ',L6M4V9,' then 140 when ',L6M3E6,' then 141 when ',L6M 4V8,' then 142
    when ',L6M4V8,' then 143 when ',L6M 4V9,' then 144 when ',L6M1J5,' then 145
    when ',L6L5W4,' then 146 when ',L6M3E5,' then 147 when ',L6M1J6,' then 148
    when ',L6M3E8,' then 149 when ',L6M3Z5,' then 150 when ',L6M1J7,' then 151
    when ',L6M 4X7,' then 152 when ',L6M4X7,' then 153 when ',L6K2H2,' then 154
    when ',L6K3T2,' then 155 when ',L6M2Y5,' then 156 when ',L6M2L6,' then 157
    when ',L6L0B1,' then 158 when ',L6M2C1,' then 159 when ',L6K3Y5,' then 160
    when ',L6M1K3,' then 161 when ',L6K3W9,' then 162 when ',L6M1H9,' then 163
    when ',L6M3G4,' then 164 when ',L6M3M3,' then 165 when ',L6M4G2,' then 166
    when ',L6M1L2,' then 167 when ',L6M3E9,' then 168 when ',L6M 4X6,' then 169
    when ',L6M2C2,' then 170 when ',L6M4X6,' then 171 when ',L6M1K9,' then 172
    when ',L6M3G1,' then 173 when ',L6M1J8,' then 174 when ',L6M2N1,' then 175
    when ',L6K3W7,' then 176 when ',L6M2B8,' then 177 when ',L6M3G5,' then 178
    when ',L6M3H7,' then 179 when ',L6M1J9,' then 180 when ',L6K2C5,' then 181
    when ',L6K3Y1,' then 182 when ',L6M1K6,' then 183 when ',L6K2C6,' then 184
    when ',L6K3V3,' then 185 when ',L6M3Z4,' then 186 when ',L6M3R2,' then 187
    when ',L6M 4V7,' then 188 when ',L6L2W3,' then 189 when ',L6L2W2,' then 190
    when ',L6M4V7,' then 191 when ',L6M2M9,' then 192 when ',L6L5A7,' then 193
    when ',L6K3V7,' then 194 when ',L6M2Y4,' then 195 when ',L6M2C4,' then 196
    when ',L6K2C4,' then 197 when ',L6L2W4,' then 198 when ',L6M3G6,' then 199
    when ',L6L5B1,' then 200 when ',L6M2C3,' then 201 when ',L6K3S4,' then 202
    when ',L6K2L9,' then 203 when ',L6K2C2,' then 204 when ',L6K2C3,' then 205
    when ',L6K3T7,' then 206 when ',L6K2C1,' then 207 when ',L6K2M1,' then 208
    when ',L6K3W4,' then 209 when ',L6M2L7,' then 210 when ',L6K3S8,' then 211
    when ',L6M2L9,' then 212 when ',L6M2Y3,' then 213 when ',L6K3S7,' then 214
    when ',L6K2L4,' then 215 when ',L6M2A9,' then 216 when ',L6K2N1,' then 217
    when ',L6M1V7,' then 218 when ',L6K1Y3,' then 219 when ',L6K2M7,' then 220
    when ',L6M2B1,' then 221 when ',L6K3T5,' then 222 when ',L6K2B9,' then 223
    when ',L6K3X9,' then 224 when ',L6K2M9,' then 225 when ',L6M1V8,' then 226
    when ',L6M2M1,' then 227 when ',L6M2N6,' then 228 when ',L6K2B6,' then 229
    when ',L6M2Y2,' then 230 when ',L6M2L8,' then 231 when ',L6L5A9,' then 232
    when ',L6L2W5,' then 233 when ',L6M1L1,' then 234 when ',L6M2B9,' then 235
    when ',L6K1Z9,' then 236 when ',L6K3W6,' then 237 when ',L6K2A1,' then 238
    when ',L6K2B8,' then 239 when ',L6K2H3,' then 240 when ',L6K2M8,' then 241
    when ',L6K1Y2,' then 242 when ',L6L2W6,' then 243 when ',L6M2B6,' then 244
    when ',L6M1V3,' then 245 when ',L6M2W2,' then 246 when ',L6L4X8,' then 247
    when ',L6M1V6,' then 248 when ',L6K2L3,' then 249 when ',L6M2Y1,' then 250
    when ',L6K2T4,' then 251 when ',L6K2Y1,' then 252 when ',L6K3S3,' then 253
    when ',L6K2T3,' then 254 when ',L6K1V5,' then 255 when ',L6K1V3,' then 256
    when ',L6M1K8,' then 257 when ',L6K2L2,' then 258 when ',L6K2R7,' then 259
    when ',L6M2N2,' then 260 when ',L6K3Y6,' then 261 when ',L6K1Z6,' then 262
    when ',L6M2B7,' then 263 when ',L6M1K7,' then 264 when ',L6K1Z8,' then 265
    when ',L6K1V4,' then 266 when ',L6M1V4,' then 267 when ',L6K2P8,' then 268
    when ',L6K2M6,' then 269 when ',L6L5A8,' then 270 when ',L6K1V2,' then 271
    when ',L6K1Z7,' then 272 when ',L6M2B2,' then 273 when ',L6M2B3,' then 274
    when ',L6M2B4,' then 275 when ',L6M1V5,' then 276 when ',L6H0B4,' then 277
    when ',L6M2B5,' then 278 when ',L6L2W7,' then 279 when ',L6K2P7,' then 280
    when ',L6L5B6,' then 281 when ',L6K2M5,' then 282 when ',L6K3X7,' then 283
    when ',L6K1V1,' then 284 when ',L6M2W8,' then 285 when ',L6K1T9,' then 286
    when ',L6K2L1,' then 287 when ',L6M2Y6,' then 288 when ',L6K1T8,' then 289
    when ',L6M2N3,' then 290 when ',L6M2A5,' then 291 when ',L6K2N8,' then 292
    when ',L6K2N7,' then 293 when ',L6M2A4,' then 294 when ',L6L2W8,' then 295
    when ',L6K2K8,' then 296 when ',L6L4W3,' then 297 when ',L6L4W2,' then 298
    when ',L6M2N4,' then 299 when ',L6M2N5,' then 300 when ',L6L4W4,' then 301
    when ',L6L4T8,' then 302 when ',L6L5N9,' then 303 when ',L6K1S9,' then 304
    when ',L6H1E8,' then 305 when ',L6K1T1,' then 306 when ',L6H1R5,' then 307
    when ',L6L2X5,' then 308 when ',L6K2M4,' then 309 when ',L6M2X9,' then 310
    when ',L6M3B2,' then 311 when ',L6H1S2,' then 312 when ',L6K1P7,' then 313
    when ',L6K1P6,' then 314 when ',L6L5T9,' then 315 when ',L6H1E9,' then 316
    when ',L6L2W9,' then 317 when ',L6L4X5,' then 318 when ',L6L4X4,' then 319
    when ',L6L4X3,' then 320 when ',L6M5C8,' then 321 when ',L6M 5C7,' then 322
    when ',L6M 5C8,' then 323 when ',L6M 5C9,' then 324 when ',L6M 5E1,' then 325
    when ',L6L0A6,' then 326 when ',L6H0B8,' then 327 when ',L6M5C7,' then 328
    when ',L6L4X6,' then 329 when ',L6M5C9,' then 330 when ',L6M5E1,' then 331
    when ',L6L4W1,' then 332 when ',L6M0B8,' then 333 when ',L6H1R3,' then 334
    when ',L6H1S1,' then 335 when ',L6H7V8,' then 336 when ',L6H7V4,' then 337
    when ',L6H7V3,' then 338 when ',L6M0B6,' then 339 when ',L6M2N9,' then 340
    when ',L6K3V1,' then 341 when ',L6M3G7,' then 342 when ',L6L4Z1,' then 343
    when ',L6L6K1,' then 344 when ',L6K2K9,' then 345 when ',L6K1P8,' then 346
    when ',L6L4X7,' then 347 when ',L6L4T7,' then 348 when ',L6M3R3,' then 349
    when ',L6H1S4,' then 350 when ',L6H1R4,' then 351 when ',L6H1S3,' then 352
    when ',L6H1S7,' then 353 when ',L6H1S5,' then 354 when ',L6L4R6,' then 355
    when ',L6L4R7,' then 356 when ',L6K1R6,' then 357 when ',L6L4Y9,' then 358
    when ',L6K1R5,' then 359 when ',L6K2J6,' then 360 when ',L6K2J1,' then 361
    when ',L6M2W4,' then 362 when ',L6L2X1,' then 363
  END ) DESC
limit 0,5

Объясните план введите описание изображения здесь

Теперь я прикрепил полный запрос

Вот структура таблицы. введите описание изображения здесь

Вот что я уже сделал: у меня есть код для получения почтовых индексов примерно в 50 милях от таблицы zip_code. Затем я сначала отображаю результаты из tutor_user_content в порядке ближайшего почтового индекса. Надеюсь, теперь я это ясно объяснил. Пожалуйста, помогите. Спасибо

1. У вас есть вопрос?

2. @Митч Уит, я думаю, проблема в производительности. Просто посмотрите на все это или-лайки…

3. Отображение упрощенного запроса ограничивает возможные варианты оптимизации.

4. Есть ли какая-либо причина, по которой у вас нет почтовых индексов с присвоенными номерами для сортировки в другой таблице? Я просто не могу поверить, что вы просто ввели 363×2 разных значения в один запрос, не получив их сначала из какой-нибудь другой таблицы.

5. @Vasanthan.R.P : «Вот упрощенный запрос для вашей справки». — если это упрощенный запрос, я не хочу видеть оригинал!

Ответ №1:

Используйте полнотекстовый поиск

Я верю, что это сработает. Не тестировал, хотя

 SELECT
  TR.firstName,TR.LastName,TC.* 
FROM
  tutor_register as TR,tutor_user_content as TC 
WHERE
  TR.Id=TC.tuotr_id and TC.status='Y' 
AND
  (TC.ContentType='basic' OR TC.ContentType='premium') 
AND
  match(TC.ZipCode) 
  AGAINST 
    ('"L6M2V6" "L6M2V5" "L6M2E9" "L6M2G1" "L6M2E8" "L6M2E7" "L6M2E5" "L6M2E6" "L6M 4X8" "L6M4X8"' IN NATURAL LANGUAGE MODE)
ORDER BY 
   TC.ContentType DESC , 
   ( 
     CASE 
        WHEN 'L6M2V6' THEN -1 
        WHEN 'L6M2V5' THEN 0 
        WHEN 'L6M2E9' THEN 1 
        WHEN 'L6M2G1' THEN 2 
        WHEN 'L6M2E8' THEN 3 
        WHEN 'L6M2E7' THEN 4 
        WHEN 'L6M2E5' THEN 5 
        WHEN 'L6M2E6' THEN 6 
        WHEN 'L6M 4X8' THEN 7 
        WHEN 'L6M4X8' THEN 8 
     END ) DESC 
 LIMIT 0,5

Также все ваши

( CASE when 'L6M2V6' then -1 when 'L6M2V5' then 0 when 'L6M2E9' then 1 when 'L6M2G1' then 2 when 'L6M2E8' then 3 when 'L6M2E7' then 4 when 'L6M2E5' then 5 when 'L6M2E6' then 6 when 'L6M 4X8' then 7 when 'L6M4X8' then 8 END )

часть пугает меня до чертиков.

1. Спасибо за понимание моей проблемы. Да, проблема в скорости. Запрос представляет собой всего лишь пример с 10 почтовыми индексами. Но в моем фактическом результате поиска у меня 10000 почтовых индексов. В полнотекстовом поиске я не знаю, как упорядочить результаты. Пожалуйста, помогите. Спасибо

2. Я обновил свой вопрос. Пожалуйста, посмотрите это и сообщите мне свои предложения. Спасибо

Ответ №2:

Конечно, ваши лайки не нужны. Разве почтовые индексы не должны храниться в поле, чтобы вы могли сравнивать их напрямую ( TC.ZipCode like '%,L6M3Z5,%' становится TC.ZipCode = 'L6M3Z5' )?

И у вас должен быть индекс на TC (ContentType, почтовый индекс)

1. На самом деле в моем поле почтового индекса я сохранил почтовые индексы через запятую, например, zipcode1, zipcode2, zipcode3, поэтому я не могу использовать «=». Пожалуйста, дайте совет. Спасибо