Как сделать запрос ORDER BY в JOIN быстрее? Ничего я не пробовал, работал

У меня есть следующий запрос JOIN:

SELECT
    table1.*, 
    table2.*
FROM 
    Table1 AS table1 
LEFT JOIN 
    Table2 AS table2 
USING 
    (col1)
LEFT JOIN 
    Table3 as table3 
USING 
    (col1) 
WHERE 
    3963.191 * 
    ACOS(
    (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180)) 
    +
    (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
    ) <= 10 
AND 
    table1.col1 != '1' 
AND 
    table1.col2 LIKE 'A' 
AND 
    (table1.col3 LIKE 'X' OR table1.col3 LIKE 'X-Y') 
AND 
    (table2.col4 = 'Y' OR table2.col5 = 'Y') 


// Data Types of all columns in the query:
// col1: int(11)
// col2: char(1)
// col3: varchar(3)
// col4: char(1)
// col5: char(1)
// col6: int(11)
// latitude: varchar(25)
// longitude: varchar(25)

// All 3 tables (table1, table2, and table3) are `MyISAM`.

Выполняется в 0.15 секунд.

Однако, если я просто добавлю:

ORDER BY 
    table1.col6 DESC

Выполняется в течение 3 секунды.

Все столбцы в запросе индексируются, включая table1.col6, используемые в ORDER BY.

Ниже приведены результаты EXPLAIN EXTENDED БЕЗ ORDER BY:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  table1  ALL PRIMARY,col2,col3   NULL    NULL    NULL    140101  72.61   Using where
1   SIMPLE  table2  eq_ref  PRIMARY,col4,col5   PRIMARY 4   table1.col1 1   100 Using where
1   SIMPLE  table3  eq_ref  PRIMARY PRIMARY 4   table1.col1 1   100 Using where

И вот результаты EXPLAIN EXTENDED WITH ORDER BY:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  table1  ALL PRIMARY,col2,col3   NULL    NULL    NULL    140101  72.61   Using where; Using filesort
1   SIMPLE  table2  eq_ref  PRIMARY,col4,col5   PRIMARY 4   table1.col1 1   100 Using where
1   SIMPLE  table3  eq_ref  PRIMARY PRIMARY 4   table1.col1 1   100 Using where

Что странно, что я использую ORDER BY DESC в нескольких других запросах на этом сайте, и он не замедляет его нигде почти так же, как с этим конкретным запросом. Для этого запроса есть что-то конкретное, что приводит к значительному замедлению его с помощью ORDER BY.

Я также сделал ANALYZE TABLE на всех трех таблицах, и все они сообщили OK. Затем я заменил каждый LIKE в запросе на =, и он фактически сделал запрос БЕЗ ORDER BY перейти от 0,2 секунды к 3 секунды. Другими словами, заменяя LIKE на =, исходный запрос занимает столько же времени, сколько добавление ORDER BY! Как это возможно, учитывая, что LIKE работает больше, чем =? Возможно, в этом заключается ключ к тому, почему ORDER BY занимает так много времени?

ЗДЕСЬ, ЧТО Я ПОЛУЧИЛ ПОЛНОСТЬЮ (НЕУДАЧНО):

1) Вместо SELECT table1.*, table2.* я попробовал только SELECT table1.col1, и ему все равно потребовалось 3 секунды.

2) Я попытался добавить составной индекс на col1, col2, col3 и col6 в Table1, но это не улучшило скорость выполнения.

3) Я попробовал это решение сделать запрос в качестве подзапроса и затем обернуть ORDER BY вне его на но это не улучшило скорость выполнения.

4) Я попробовал следующую версию запроса, но ничего не улучшил и фактически сделал запрос более 3 секунды БЕЗ даже добавленного к нему ORDER BY (возможно, это обеспечивает еще один ключ):

SELECT STRAIGHT_JOIN
      T1.*, 
      T2.*
   FROM 
      Table1 AS T1
         JOIN Table2 AS T2
            ON T1.Col1 = T2.Col1
            AND ( T2.Col4 = 'Y' OR T2.Col5 = 'Y' )
         JOIN Table3 as T3
            ON T1.Col1 = T3.Col1
            AND 3963.191 
               * ACOS(  (SIN(PI() * $usersLatitude / 180) * SIN(PI() * T3.latitude / 180)) 
                                + (  COS(PI() * $usersLatitude / 180) * COS(PI() * T3.latitude / 180) 
                                   * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)
                        )   
                     ) <= 10 
   WHERE
          T1.Col2 LIKE 'A'
      AND ( T1.col3 LIKE 'X' OR T1.col3 LIKE 'X-Y') 
      AND T1.Col1 != '1'
   ORDER BY
      T1.Col6

// With the following composite indexes:
// On Table 1, index on ( Col2, Col3, Col1, Col6 )
// On Table 2, index on ( Col1, Col4, Col5 )

// Remember, all individual columns are already indexed.

...

Как я могу получить этот упрямый запрос для быстрого запуска с помощью ORDER BY? Или это просто невозможно?

EDIT:

Результаты SHOW CREATE TABLE для всех трех таблиц:

CREATE TABLE `Table1` (
 `col1` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `col100` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
 `col101` varchar(60) COLLATE utf8_bin DEFAULT NULL,
 `col102` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
 `col103` varchar(10) COLLATE utf8_bin DEFAULT '00000000',
 `col104` date NOT NULL,
 `col105` int(3) DEFAULT NULL,
 `col106` varchar(25) COLLATE utf8_bin DEFAULT NULL,
 `col107` varchar(20) COLLATE utf8_bin DEFAULT 'Blah',
 `col108` varchar(2) COLLATE utf8_bin DEFAULT 'No',
 `col109` varchar(15) COLLATE utf8_bin DEFAULT 'Blah',
 `col2` enum('A','B') COLLATE utf8_bin DEFAULT NULL,
 `col3` enum('A','B','A-B') COLLATE utf8_bin DEFAULT NULL,
 `col110` decimal(10,7) NOT NULL DEFAULT '0.0000000',
 `col111` decimal(10,7) NOT NULL DEFAULT '0.0000000',
 `col112` char(1) COLLATE utf8_bin DEFAULT 'N',
 `col113` char(1) COLLATE utf8_bin DEFAULT 'N',
 `col114` int(11) DEFAULT NULL,
 `col115` varchar(15) COLLATE utf8_bin DEFAULT 'Blah',
 `col6` int(11) DEFAULT NULL,
 `col117` varchar(45) COLLATE utf8_bin DEFAULT NULL,
 `col118` varchar(2) COLLATE utf8_bin NOT NULL,
 `col119` tinyint(2) NOT NULL,
 `col120` int(6) NOT NULL,
 `col121` varchar(7) COLLATE utf8_bin NOT NULL,
 `col122` varchar(6) COLLATE utf8_bin NOT NULL,
 `col123` char(1) COLLATE utf8_bin NOT NULL DEFAULT 'A',
 `col124` varchar(200) COLLATE utf8_bin NOT NULL,
 `col125` tinyint(4) NOT NULL,
 `col126` tinyint(1) NOT NULL,
 `col127` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'A',
 `col128` tinyint(1) NOT NULL DEFAULT '0',
 `col129` smallint(5) unsigned NOT NULL,
 `col130` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'A',
 `col131` int(11) NOT NULL,
 `col132` tinyint(1) NOT NULL,
 `col133` tinyint(1) NOT NULL,
 `col134` varchar(1) COLLATE utf8_bin NOT NULL,
 `col135` varchar(200) COLLATE utf8_bin NOT NULL,
 `col136` int(11) NOT NULL,
 `col137` int(10) unsigned NOT NULL,
 `col138` int(11) NOT NULL,
 `col139` tinyint(1) NOT NULL,
 `col140` tinyint(1) NOT NULL,
 `col141` tinyint(4) NOT NULL,
 `col142` varchar(25) COLLATE utf8_bin NOT NULL,
 `col143` varchar(25) COLLATE utf8_bin NOT NULL,
 `col144` tinyint(1) unsigned NOT NULL,
 `col145` tinyint(4) NOT NULL,
 PRIMARY KEY (`col1`),
 KEY `col2` (`col2`),
 KEY `col3` (`col3`),
 KEY `CompositeIndex0` (`col1`,`col2`,`col3`,`col6`),
 KEY `CompositeIndex1` (`col2`,`col3`,`col1`,`col6`),
 KEY `idx01` (`col1`,`col2`,`col3`)
 [19 other indexes that do not involve col1, col2, col3, or col6...]
) ENGINE=MyISAM AUTO_INCREMENT=160640 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

//*******************************************************//

CREATE TABLE `Table2` (
 `col1` int(11) unsigned NOT NULL DEFAULT '0',
 `col201` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'Blah',
 `col202` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'Blah',
 `col203` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col204` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col205` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col206` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col207` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col208` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col209` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col210` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col211` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col212` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col213` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col214` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col215` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col216` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col217` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col218` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col219` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
 `col220` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
 `col221` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
 `col222` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
 `col223` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
 `col224` varchar(45) COLLATE utf8_bin DEFAULT ‘Blah’,
 `col225` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 `col4` char(1) COLLATE utf8_bin DEFAULT 'A',
 `col226` char(1) COLLATE utf8_bin DEFAULT 'A',
 `col227` varchar(5) COLLATE utf8_bin DEFAULT 'Blah',
 `col228` char(1) COLLATE utf8_bin NOT NULL,
 `col229` text COLLATE utf8_bin,
 `col5` char(1) COLLATE utf8_bin DEFAULT 'A',
 `col230` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
 `col231` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 `col232` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 `col233` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 PRIMARY KEY (`col1`),
 KEY `col4` (`col4`),
 KEY `col5` (`col5`),
 KEY `CompositeIndex1` (`col1`,`col4`,`col5`),
 [4 other indexes not involving col1, col4, col5...]
 FULLTEXT KEY `col220` (`col220`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

//*******************************************************//

CREATE TABLE `Table3` (
 `col1` int(11) unsigned NOT NULL DEFAULT '0',
 `col300` varchar(255) COLLATE utf8_bin DEFAULT NULL,
 `latitude` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '0',
 `longitude` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '0',
 `col301` int(11) DEFAULT NULL,
 `static2` float(18,16) DEFAULT '0.0000000000000000',
 `static3` float(18,16) DEFAULT '0.0000000000000000',
 PRIMARY KEY (`col1`),
 KEY `latitude` (`latitude`),
 KEY `longitude` (`longitude`),
 KEY `static2` (`static2`),
 KEY `static3` (`static3`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

ИЗМЕНИТЬ 2:

Ниже представлен мой файл конфигурации MySQL. Среди прочего, обратите внимание, как sort-buffer-size установлен на 1M. Согласно this, он не должен быть установлен выше 256K, или он может фактически замедлить работу с помощью "37x". Может ли быть частью проблемы?

# The MySQL database server configuration file.

[mysqld]

open-files-limit                = 20000

thread-cache-size               = 16
table-open-cache                = 2048
table-definition-cache          = 512

query-cache-type                = 1
query-cache-size                = 32M
query-cache-limit               = 1M

sort-buffer-size                = 1M
read-buffer-size                = 1M
read-rnd-buffer-size            = 8M
join-buffer-size                = 1M

tmp-table-size                  = 64M 
max-heap-table-size             = 64M

back-log                        = 100
max-connections                 = 200
max-connect-errors              = 10000
max-allowed-packet              = 16M
interactive-timeout             = 600
wait-timeout                    = 180
net_read_timeout        = 30
net_write_timeout       = 30

back_log            = 128

myisam-sort-buffer-size         = 128M

innodb-buffer-pool-size         = 320M
innodb-log-buffer-size          = 4M

innodb-log-file-size           = 128M
innodb-log-files-in-group      = 2

innodb-file-per-table           = 1

[mysqldump]
max-allowed-packet      = 16M

По другому вопросу, вот РЕЗУЛЬТАТЫ EXPLAIN EXTENDED ПОСЛЕДНИЕ ВОПРОСЫ ИЗ ИВАНА:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  T1  ref PRIMARY,col2,col3,col1,CompositeIndex1,idx01    CompositeIndex1 2   const   92333   Using where; Using filesort
1   SIMPLE  T3  eq_ref  PRIMARY PRIMARY 4   T1.col1 1   Using where
1   SIMPLE  T2  eq_ref  PRIMARY,CompositeIndex1,idx_static1 PRIMARY 4   T1.col1 1   Using where

По другому вопросу, вот что-то ОЧЕНЬ странное. Следующая версия запроса WITH ORDER BY завершается всего за 0,2 секунды:

SELECT STRAIGHT_JOIN T1 . * , T2 . * 
FROM Table3 AS T3
JOIN Table2 AS T2 ON T3.col1 = T2.col1
AND (
T2.col4 = 'Y'
OR T2.col5 = 'Y'
)
JOIN Table1 AS T1 ON T3.col1 = T1.col1
AND 3963.191 * ACOS( (
SIN( PI( ) * - 87.8819594 /180 ) * SIN( PI( ) * T3.latitude /180 ) ) + ( COS( PI( ) * - 87.8819594 /180 ) * COS( PI( ) * T3.latitude /180 ) * COS( PI( ) * T3.longitude /180 - PI( )* 37.1092162 /180 ) )
) <=10
WHERE T1.col2 LIKE 'A'
AND (
T1.col3 LIKE 'X'
OR T1.col3 LIKE 'X-Y'
)
AND T1.col1 != '1'
ORDER BY T1.col6 DESC

В основном, эта версия запроса содержит таблицы FROM Table3 AS T3 и JOIN таблицы 1 и 2, тогда как исходный запрос имеет FROM Table1 AS T1 и JOIN таблицы 2 и 3.

Ниже приведено EXPLAIN EXTENDED для запроса:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  T3  ALL PRIMARY NULL    NULL    NULL    141923  100 Using where; Using temporary; Using filesort
1   SIMPLE  T2  eq_ref  PRIMARY,col4,col5,CompositeIndex1   PRIMARY 4   T3.col1 1   100 Using where
1   SIMPLE  T1  eq_ref  PRIMARY,col2,col3,col1,CompositeIndex1,idx01    PRIMARY 4   T2.col1 1   100 Using where

Обратите внимание, как этот запрос фактически выполняет ОБА filesort и temporary вместо простого filesort в исходных и новых запросах от Ivan. Как это может быть на 10 раз быстрее?

Даже незнакомец, переключая порядок JOIN, похоже, не улучшает ни первоначальный запрос, ни более новые запросы от Ивана. Почему это?

Ответ 1

После долгих проб и ошибок я наконец нашел решение своего вопроса.

Если мы поместим все предложение WHERE - , кроме, которое вычисляет радиус - вне исходного запроса, тогда мы получаем очень быстрый запрос, который делает не используйте temporary как изменение порядка JOIN:

SELECT * FROM
{
    SELECT
        col1, col2, col3, col4, col5, col6
    FROM 
        Table1 AS table1 
    LEFT JOIN 
        Table2 AS table2 
    USING 
        (col1)
    LEFT JOIN 
        Table3 as table3 
    USING 
        (col1) 
    WHERE 
        3963.191 * 
        ACOS(
        (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180)) 
        +
        (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
        ) <= 10 
) AS sub
WHERE
    col1 != '1' 
AND 
    col2 LIKE 'A' 
AND 
    (col3 LIKE 'X' OR col3 LIKE 'X-Y') 
AND 
    (col4 = 'Y' OR col5 = 'Y') 
ORDER BY 
    col6 DESC

По существу, этот запрос сначала получает результаты JOIN всех трех таблиц на основе радиуса и только затем применяет остальные фильтры для получения нужных нам результатов. Эта версия запроса возвращает точные те же результаты, что и мой исходный запрос, но выполняется только в 0,2 секунды против 3 секунды для моего оригинала запрос.

Вот EXPLAIN EXTENDED для него:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    43  100 Using where; Using filesort
2   DERIVED T3  ALL PRIMARY NULL    NULL    NULL    143153  100 Using where
2   DERIVED users   eq_ref  PRIMARY,col1,idx01  PRIMARY 4   T3.col1 1   100 
2   DERIVED userProfile eq_ref  PRIMARY,CompositeIndex1 PRIMARY 4   users.col1  1   100

Я хотел поблагодарить Иван Буттинони за отличную работу над этим. Он нашел несколько умных способов сделать этот запрос еще быстрее.

Мораль истории: это не просто предложение ORDER BY, которое может быть сделано быстрее помещая его вне основного запроса, вы также можете получить более быстрый запрос, поместив часть предложения WHERE вне его также в таких ситуациях, как этот.

Ответ 2

Ну, Я предлагаю вам несколько повторений запроса:

введите, где условия не связаны, см. второй запрос:

И (T1.col3 LIKE 'X' ИЛИ T1.col3 LIKE 'X-Y')
избегать ИЛИ использовать IN
избегать как использовать =

И T1.col3 IN ('X', 'X-Y')
избегать вычислений, в которых

создайте несколько новых столбцов для хранения:

SIN(PI() * T3.latitude / 180)
COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)
COS(PI() * T3.latitude / 180)

предварительно оценить

SIN (PI() * $usersLatitude/180) COS (PI() * $usersLatitude/180)
если все эти "трюки" не могут избежать принудительного сортировки файлов, индексы

подсказка индекса запросов mysql

ДАЛЬНЕЙШЕЕ ДОБАВЛЕНИЕ

чтобы удалить:

( T2.Col4 = 'Y' OR T2.Col5 = 'Y' )

в этом случае вы не можете использовать IN, поэтому создайте новый столбец, который является результатом этого выражения.

alter table table2 add static1 bit default 0;
alter table add index idx_static1(static1);
update table2 t2 set static1=1 where ( T2.Col4 = 'Y' OR T2.Col5 = 'Y' );

alter table table3 add static2 float(18,16) default 0;
update table3 set static2=SIN(PI() * T3.latitude / 180) where 1

alter table table3 add static3 float(18,16) default 0;
update table3 set static3 = COS(PI() * T3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)   where 1

Если table1.col2 имеет несколько значений

alter table table1 change col2 col2 enum('A','B','C');

Если table1.col3 имеет несколько значений

alter table table1 change col3 col3 enum('X','Y','X-Y');

Создайте уникальный индекс для всех столбцов, в которых alter table добавить индекс idx01 (col1, col2, col3)

SELECT STRAIGHT_JOIN
      T1.*, 
      T2.*
   FROM 
      Table1 AS T1
         JOIN Table2 AS T2 ON T1.Col1 = T2.Col1
         JOIN Table3 as T3 ON T1.Col1 = T3.Col1

   WHERE static1=1 AND
          T1.Col2 = 'A'
      AND T1.col3 IN ( 'X', 'X-Y') 
      AND T1.Col1 != 1
      AND ACOS(  
                 ( 
                   $usersLatitude_sin_pi_fract180  * t3.static2 
                   + $usersLatitude_cos_pi_fract180  * t3.static3 
                 )   
               ) <= 0,00252321929476 -- this 10/3963.191
      ORDER BY T1.Col6

Ваш комментарий подсказывает мне, что у вас другая сортировка в запросе (col1 - latin1_swedish, а col2 - utf8), или ваше соединение использует другую сортировку (ваше соединение - utf-8, и вы запрашиваете столбец latin1_german), поэтому, когда вы запрос:

t1.col2 = 'A'

Mysql должен преобразовать из utf-8 в latin1 каждое значение.

См. также раздел сортировки документации mysql.

Быстрый способ конвертировать все (столбец, таблица, сервер, соединение, клиент) в один и тот же набор синтаксических байтов будет лучше, если вам не нужен utf-8.

Будьте осторожны с ошибкой моего типа или синтаксической ошибкой, которую я мог бы сделать.

ДАЛЬШЕ ДОБАВИТЬ 2

Я воссоздал таблицы в тестовой БД, и я исправил эти столбцы: t1.col2, t2.col3 не может быть нулевым, t1.col1 является первичным и не может быть null.

Индекс "t1.CompositeIndex1" должен индексироваться только: col2, col3, col1; Индексировать столбец "порядок" не полезен или худший.

Я создаю static1, и я создаю индекс на t2.col1 и t2.static1, но с моими 6 строками в БД не используется (см. объяснение позже). t2.static1 также не должен иметь значение NULL.

Я также адаптирую запрос к сортировке столбцов:

SELECT  T1.*, T2.* 
FROM Table1 AS T1
         JOIN Table2 AS T2   ON ( T1.Col1 = T2.Col1   )
         JOIN Table3 as T3 ON T1.Col1 = T3.Col1
   WHERE  
         (  T1.Col2 =    'A'   collate utf8_bin  AND T1.col3 IN  ( 'X' collate utf8_bin , 'X-Y'  collate utf8_bin )   AND T1.Col1 != 1 )
and T2.static1=1
      AND ACOS(  (   2.3  * T3.static2  + 1.2 * T3.static3  ) ) <= 0.00252321929476 
      ORDER BY T1.Col6

Здесь следует объяснение, расширенное

+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
| id | select_type | table | type   | possible_keys                     | key             | key_len | ref            | rows | filtered | Extra                       |
+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
|  1 | SIMPLE      | T1    | ref    | PRIMARY,col2,col3,CompositeIndex1 | CompositeIndex1 | 1       | const          |    1 |   100.00 | Using where; Using filesort |
|  1 | SIMPLE      | T2    | eq_ref | PRIMARY,CompositeIndex1           | PRIMARY         | 4       | testdb.T1.col1 |    1 |   100.00 | Using where                 |
|  1 | SIMPLE      | T3    | eq_ref | PRIMARY                           | PRIMARY         | 4       | testdb.T1.col1 |    1 |   100.00 | Using where                 |
+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+

Является ли это тем же для colums: select_type, table, type, key, ref, filter, Extra?

Мои цели оптимизации: - соответствовать условиям, в которых есть несколько индексов - избегать расчетов - избежать преобразования сортировки - избегать ИЛИ - избегать NULL, где условие

Теперь плохая новость Похоже, что в таблицах, которые вы используете ~ 140K записей, а запрос с использованием порядка может подразумевать использование подхода fileort, если запрос включает в себя много строк, поэтому окончательный ответ может увеличить буфер memsort, как подсказывает @mavroprovato.

ДАЛЬШЕ ДОБАВИТЬ 3

Чтобы оценить адекватность key_buffer_size, см. http://dba.stackexchange.com

ДАЛЬШЕ ДОБАВИТЬ 4

Я думаю, что только кто-то из Oracle может точно сказать, что происходит, но у меня есть идея.

Я думаю, что этот запрос свойственен:

все таблицы (t1, t2, t3) объединяются с помощью первичного ключа
другие условия зависят только от calcs (t3.colX)
некоторые условия зависят только от индекса (t1.colX)

Из-за 1 from_table_rows >= join1_table_rows >= join2_table_rows, так что меньше строк вернет из таблицы быстрее, будут 2 других JOINs

Оптимизатор для оценки усилия рассчитает аналогичное уравнение:

effort = num_rows*key_size/index_cardinality

(index_cardinality отображается по phpmyadmin рядом с каждым индексом)

Из-за 2 усилий >= num_rows

Мой запрос из-за 3 таблица1 (из таблицы) возвращает 92333 строк, таблица3 (join1_table) уменьшает до 1 (!) строки, таблица2 сохраняет 1 строку (усилие ~ 3).

Ваш запрос из-за 2 у вас должно получиться усилие = 140000, но, к счастью, для вас результат calc возвращает только 1 результат, чтобы ваш запрос был экстремально быстрым.

Demostration

В вашем запросе, изменяющемся от "< = 10" (в состоянии соединения) до "< = 1000" или более, вы увидите экспоненциальное снижение производительности.

В моем запросе, изменяющемся от "< = 10" (в состоянии соединения) до "< = 1000" или более, вы увидите линейное/логарифмическое снижение производительности.

ДАЛЬШЕ ДОБАВИТЬ 5

ответ на вопрос: слишком большой размер буфера сортировки?

стоя на статью, да, попробуйте какую-нибудь мелодию, возможно, вы сможете решить проблему.

ответ на вопрос: невозможно выполнить быстрый запрос?

IMHO нет, это возможно (даже если размер сортировочного буфера не устраняется).

Моя идея довольно проста, и она может возобновиться в этом mot: "cirlce хорош, но квадрат лучше".

В настоящий момент наибольшая мощность определяется координатами в таблице 3, но из-за формулы не применяется индекс. Таким образом, вместо поиска всех точек внутри радиуса вы можете искать все точки внутри "квадрата"

FROM table3
...
WHERE (t3.latitude-0.15) < $usersLatitude AND  $usersLatitude < t3.latitude+0.15  
AND t3.longitue - 0.15 < $usersLongitude AND   $usersLongitude < t3.longitue + 0.15

чтобы вы могли создать индекс в (t3.latitude, t3.longitue).

0,15 градуса должно быть 10 миль. Конечно, вы должны исправить вычеты вблизи меридиана смены дня и рядом с полюсами

Если вам нужен строго радиус, вы можете повторно подключиться к таблице 3 с помощью формулы радиуса (см. пример ниже) или , если возможно выполнить (/разработать) формулу, пока вы не сможете сравнить непосредственно значения с столбцами.

FROM table3 t3
JOIN table3 t3bis ON t3.id=t3bis.id
...
WHERE (t3.latitude-0.15) < $usersLatitude AND  $usersLatitude < t3.latitude+0.15  
AND t3.longitue - 0.15 < $usersLongitude AND   $usersLongitude < t3.longitue + 0.15
AND 
3963.191 
* ACOS(  (SIN(PI() * $usersLatitude / 180) * SIN(PI() * t3bis.latitude / 180)) 
+ (  COS(PI() * $usersLatitude / 180) * COS(PI() * t3bis.latitude / 180) 
* COS(PI() * t3bis.longitude / 180 - PI() * 37.1092162 / 180)
)   
) <= 10

ДАЛЕЕ ДОБАВИТЬ 6

тема: скомпилированные функции делают это лучше

использование функции RADIANS()

degree * PI / 180 == radians(degree)

Использование расширения GIS для mysql

Смотрите статью о расширении GIS MySql

Ответ 3

Попробуйте выполнить первый запрос:

...
FROM 
    Table1 AS table1 USE INDEX (col6)
LEFT JOIN 
    Table2 AS table2   
...

Ответ 4

Каков тип table1.col6? Каков его диаметр поля (максимальная длина)?

Предварительно вычислить значения BTW, которые не зависят от значений полей, как предположил Иван Буттинони. Это не поможет при заказе, но быстрее сделает запрос

Ответ 5

Есть три (3) вещи, которые я вижу, вы можете сделать:

1) Рефакторинг запроса

2) Примените ORDER BY в таблице 1 ранее в запросе

3) Таблица индексов1 для поддержки рефакторинга

Возможно, это...

ALTER TABLE Table1 ADD INDEX col2_col6_ndx (col2,col6);
SELECT
    table1.*, 
    table2.*
FROM 
    (
        SELECT * FROM Table1
        WHERE col2='A' AND 
        ORDER BY col6 DESC
    ) AS table1
LEFT JOIN 
    (
        SELECT * FROM Table2
        WHERE (col4='Y' OR col5='Y')
    ) AS table2 
USING 
    (col1)
LEFT JOIN 
    Table3 as table3 
USING 
    (col1) 
WHERE 
    table1.col1 != '1' AND
    table1.col3 IN ('X','X-Y') AND
    3963.191 * 
    ACOS(
    (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180)) 
    +
    (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
    ) <= 10 
;

Вот еще один вариант, который пытается реорганизовать запрос так, чтобы сначала собирались только ключи (создавая значительно меньшие временные таблицы), а затем применялись JOINS:

ALTER TABLE Table1 ADD INDEX col2613_ndx (col2,col6,col1,col3);
ALTER TABLE Table2 ADD INDEX col4_col1_ndx (col4,col1);
ALTER TABLE Table2 ADD INDEX col5_col1_ndx (col5,col1);
SELECT 
    table1.*, 
    table2.*
FROM
(
    SELECT table1.col1,table3.latitude,table3.longitude 
    FROM 
        (
            SELECT col1 FROM Table1 WHERE col2='A' AND
            AND col3 IN ('X','X-Y') ORDER BY col6 DESC
        ) AS table1
    LEFT JOIN 
        (
            SELECT col1 FROM Table2 WHERE col4='Y' UNION
            SELECT col1 FROM Table2 WHERE col5='Y'
        ) AS table2 
    USING (col1)
    LEFT JOIN Table3 as table3 USING (col1)
) col1_keys
LEFT JOIN Table1 table1 USING (col1)
LEFT JOIN Table2 table2 USING (col1)
WHERE 
    3963.191 * 
    ACOS(
    (SIN(PI() * $usersLatitude / 180) * SIN(PI() * col1_keys.latitude / 180)) 
    +
    (COS(PI() * $usersLatitude / 180) * COS(PI() * col1_keys.latitude / 180)
    * COS(PI() * col1_keys.longitude / 180 - PI() * 37.1092162 / 180))
    ) <= 10 
;

Дайте ему попробовать!!!