mysql - rapida - ¿Cómo hacer ORDER BY en JOIN consulta más rápido? Nada de lo que he probado ha funcionado.
optimizar consultas sql oracle (5)
Tengo la siguiente consulta de 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`.
Se ejecuta en menos de 0.15 segundos .
Sin embargo, si simplemente agrego:
ORDER BY
table1.col6 DESC
Se ejecuta en más de 3 segundos .
Todas las columnas de la consulta están indexadas , incluido el table1.col6 utilizado en ORDER BY .
Aquí están los resultados de EXPLAIN EXTENDED SIN 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
Y aquí están los resultados de EXPLAIN EXTENDED AND 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
Lo que es extraño es que uso ORDER BY DESC en varias otras consultas en este sitio, y no lo ralentiza en ninguna parte tanto como con esta consulta en particular. Hay algo específico a esta consulta que está causando que se ralentice significativamente con el ORDER BY .
También hice una ANALYZE TABLE en las 3 tablas, y todas informaron que estaban OK . Luego reemplacé cada LIKE en la consulta con = y en realidad hice que la consulta SIN ORDER BY pasara de 0.2 segundos a 3 segundos . En otras palabras, reemplazar LIKE con = hace que la consulta original tome el mismo tiempo que agregar ORDER BY ! ¿Cómo es eso posible, considerando que LIKE hace más trabajo que = ? Tal vez ahí reside la clave de por qué el ORDER BY tarda tanto?
AQUÍ LO QUE HE TRATADO (SIN ÉXITO):
1) En lugar de SELECT table1.*, table2.* , Solo probé SELECT table1.col1 y aún SELECT table1.col1 más de 3 segundos en completarse.
2) Intenté agregar un índice compuesto en col1 , col2 , col6 y col6 en la Table1 , pero no mejoró la velocidad de ejecución.
3) Intenté esta solución de realizar la consulta como una subconsulta y luego envolver el ORDER BY fuera de él al final, pero no mejoró la velocidad de ejecución.
4) Probé la siguiente versión de la consulta, pero NO mejoró nada, y en realidad hice que la consulta tomara más de 3 segundos SIN que se agregara ORDER BY (quizás eso proporcione otra pista):
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.
...
¿Cómo puedo hacer que esta consulta obstinada se ejecute rápidamente CON ORDER BY ? ¿O es que simplemente no es posible?
EDITAR:
Resultados de SHOW CREATE TABLE para las 3 tablas:
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
EDIT 2:
A continuación se muestra mi archivo de configuración de MySQL . Entre otras cosas, tenga en cuenta cómo el sort-buffer-size se establece en 1M . De acuerdo con this , no debe establecerse por encima de 256K o puede ralentizar las cosas en "37x". Podría ser eso parte del problema?
# 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
En una cuestión diferente, aquí están los RESULTADOS DE EXPLAIN EXTENDED EN LA ÚLTIMA PREGUNTA DE IVAN:
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
En un asunto diferente, aquí hay algo MUY extraño. La siguiente versión de la consulta CON ORDER BY completa en solo 0.2 segundos :
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
Básicamente, esta versión de la consulta realiza las tablas 1 y 2 de FROM Table3 AS T3 y JOIN mientras que la consulta original realiza las tablas FROM Table1 AS T1 y JOIN .
Aquí está EXPLAIN EXTENDED para la consulta anterior:
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
Observe cómo esta consulta realmente hace AMBOS un filesort y un temporary frente a solo un filesort en las consultas originales y nuevas de Ivan. ¿Cómo puede ser 10 veces más rápido?
Aún más extraño, cambiar el orden de JOIN no parece mejorar ni la consulta original ni las nuevas consultas de Ivan. ¿Porqué es eso?
¿Cuál es el tipo de table1.col6? ¿Cuál es su diámetro de campo (longitud del valor máximo)?
BTW precalcula los valores, que no dependen de los valores de campo, como sugirió Ivan Buttinoni. No ayudará con el pedido, pero hará que la consulta sea más rápida
Bueno, te sugiero algunos estilos de la consulta:
poner en donde las condiciones no se unen relacionadas, ver segunda consulta:
Y (T1.col3 ME GUSTA "X" O T1.col3 ME GUSTA "X-Y")
evitar O utilizar EN
evitar como uso =
Y T1.col3 IN (''X'', ''X-Y'')
evitar el cálculo en donde
Crea algunas nuevas columnas para almacenar:
SIN(PI() * T3.latitude / 180)
COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)
COS(PI() * T3.latitude / 180)
preevaluar
SIN (PI () * $ usersLatitude / 180) COS (PI () * $ usersLatitude / 180)
Si todos estos "trucos" no pueden evitar la ordenación de archivos, forzar los índices
Sugerencia de índice de consultas mysql
ADEMÁS AÑADIR
para eliminar:
( T2.Col4 = ''Y'' OR T2.Col5 = ''Y'' )
en este caso, no puede usar IN , así que cree una nueva columna que sea el resultado de esta expresión.
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
Si table1.col2 tiene pocos valores
alter table table1 change col2 col2 enum(''A'',''B'',''C'');
Si table1.col3 tiene pocos valores
alter table table1 change col3 col3 enum(''X'',''Y'',''X-Y'');
Cree un índice único para todas las columnas involucradas en donde alterar tabla agregue el índice 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''s 10/3963.191
ORDER BY T1.Col6
Tu comentario me sugiere que tengas una intercalación diferente en la consulta (col1 es latin1_swedish y col2 es utf8) o tu conexión usa una intercalación diferente (tu conexión es utf-8 y consultas una columna latin1_german), así que cuando consultes:
t1.col2 = ''A''
Mysql tiene que convertir de utf-8 a latin1 cada valor.
Vea también la sección de cotejo de la documentación de mysql.
Una forma rápida es convertir todos (columna, tabla, servidor, conexión, cliente) a la misma intercalación. El byte de singel será mejor si no necesita utf-8.
Tenga cuidado con mi error de tipo o error de sintaxis que podría hacer.
ADEMÁS AÑADIR 2
Reconstruí las tablas en una base de datos de prueba y arreglé estas columnas: t1.col2, t2.col3 no debe ser anulable , t1.col1 es principal y no puede ser nulo.
El índice "t1.CompositeIndex1" solo debe indexar: col2, col3, col1; Índice de una columna "orden por" es inútil o peor.
Creo static1 y creo un índice en t2.col1 y t2.static1 pero con mis 6 filas en DB no se usa (ver explicación más adelante). t2.static1 no debe ser anulable también.
También adapto la consulta al cotejo de las columnas:
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
Aquí sigue la explicación extendida.
+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
| 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 |
+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
¿Es lo mismo para las columnas: tipo de selección, tabla, tipo, clave, ref, filtrado, Extra?
Mis objetivos de optimización son: - ajustar las condiciones donde se encuentran en pocos índices - evitar cálculos - evitar la conversión de clasificación - evitar O - evitar NULL en donde condición
Ahora las malas noticias Parece que en las tablas tienes ~ 140K registros, y la consulta mediante el orden puede implicar el uso del enfoque del conjunto de archivos si la consulta involucra muchas filas, por lo que la respuesta final puede aumentar el búfer memsort como se sugiere por @mavroprovato.
ADEMÁS AÑADIR 3
Para evaluar la adecuación de key_buffer_size, visite http://dba.stackexchange.com
ADEMÁS AÑADIR 4
Creo que solo alguien en Oracle puede decir exactamente lo que sucede, pero tengo mi idea.
Creo que esta consulta es peculiar:
- Todas las tablas (t1, t2, t3) están unidas por clave principal
- Otras condiciones dependen solo de calcs (t3.colX)
- Algunas condiciones dependen solo por el índice (t1.colX)
Debido a 1 from_table_rows> = join1_table_rows> = join2_table_rows, por lo que menos filas devuelven de la tabla más rápido serán los 2 otros JOINs
El optimizador para evaluar el esfuerzo calculará una ecuación similar:
effort = num_rows*key_size/index_cardinality
(index_cardinality se muestra por phpmyadmin al lado de cada índice)
Debido a 2 esfuerzo es> = num_rows
Mi consulta debido a 3 la tabla1 (de la tabla) devuelve 92333 filas, tabla3 (join1_table) reducida a 1 (!) Fila, tabla2 mantiene 1 fila (esfuerzo ~ 3).
Tu consulta debido a 2 deberías tener un esfuerzo = 140000, pero por suerte para ti, el cálculo devuelve solo 1 resultados, por lo que tu consulta es extremadamente rápida
Demostración
En su consulta, al cambiar de "<= 10" (en condición de unión) a "<= 1000" o más, verá una disminución exponencial de los rendimientos.
En mi consulta, al cambiar de "<= 10" (en condición de unión) a "<= 1000" o más, verá una disminución lineal / logarítmica de los rendimientos.
ADEMÁS AÑADIR 5
Responda a la pregunta: ¿es demasiado grande el tamaño del búfer de clasificación?
pararse en el artículo, sí, pruebe un poco de tono, puede ser que pueda resolver el problema
Responde a la pregunta: ¿es imposible hacer una consulta rápida?
En mi humilde opinión no, es posible (incluso si sort-buffer-size no se resuelve).
Mi idea es bastante simple, y puede resumirse en esta mot: "Cirlce es agradable pero cuadrada es mejor".
En este momento, la mayor cardinalidad se encuentra en las coordenadas de la tabla 3, pero debido a la fórmula no es aplicable ningún índice. Entonces, en lugar de buscar todos los puntos dentro de un radio, puedes buscar todos los puntos dentro de un "cuadrado"
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
para que pueda crear un índice en (t3.latitude, t3.longitue).
0.15 grados deben ser de 10 millas. Por supuesto, deberías arreglar los cálculos cerca del meridiano del cambio diurno y al lado de los polos.
Si necesita estrictamente un radio, puede volver a unir table3 con la fórmula de radio (vea el ejemplo a continuación) o, si es posible, ejecute (/ elabore) la fórmula hasta que pueda comparar directamente valores con columnas.
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
ADEMÁS AÑADIR 6
tema: las funciones compiladas lo hacen mejor
uso de la función RADIANES ()
degree * PI / 180 == radians(degree)
Uso de la extensión GIS de mysql.
Después de mucho ensayo y error, finalmente encontré la solución a mi pregunta.
Si colocamos la cláusula WHERE completa, excepto la parte que calcula el radio, fuera de la consulta original, obtendremos una consulta muy rápida que no usa temporary como cambiar el orden de la 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
Esencialmente, esta consulta primero obtiene los resultados de JOIN de las 3 tablas en función del radio, y solo luego aplica el resto de los filtros para obtener los resultados que necesitamos. Esta versión de la consulta devuelve exactamente los mismos resultados que mi consulta original, pero se ejecuta en solo 0.2 segundos en lugar de 3 segundos para mi consulta original.
Aquí está la EXPLAIN EXTENDED para ello:
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
Quería agradecer a Ivan Buttinoni por su excelente trabajo en esto. Encontró varias formas inteligentes de hacer esta consulta aún más rápido.
Moraleja de la historia: no es solo la cláusula ORDER BY que puede hacerse más rápido colocándola fuera de la consulta principal , también puede obtener una consulta más rápida colocando parte de la cláusula WHERE fuera de ella también en situaciones como esta.
Hay tres (3) cosas que veo que puedes hacer:
1) Refactorizar la consulta
2) Aplique ORDER BY en Table1 anteriormente en la consulta
3) Índice Table1 para apoyar refactor
Tal vez este ...
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
;
Aquí hay otra variación que intenta refactorizar la consulta para que solo las claves se recopilen primero (creando tablas temporales mucho más pequeñas) y luego se apliquen las UNIONES:
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
;
Darle una oportunidad !!!
Prueba la primera consulta:
...
FROM
Table1 AS table1 USE INDEX (col6)
LEFT JOIN
Table2 AS table2
...