MySQL range query is slow

Vote count:

0

I have read different links like http://goo.gl/1nr3s2, http://goo.gl/gv4Vlc and other stackoverflow questions, but none of them help me with this problem.

This problem interacts with multiple tables, but the EXPLAIN method help me identify range is the main problem with the query.

First I need to explain that I have this table with this sample data (I will not use ids in any table to simplify the process)

    +-------+----------+----------------+--------------+---------------+----------------+
    | marca | submarca | modelo_inicial | modelo_final | motor         | texto_articulo |
    +-------+----------+----------------+--------------+---------------+----------------+
    | Buick | Century  |           1993 |         1996 | 4 Cil 2.2 Lts | BE1254AG4      |
    | Buick | Century  |           1993 |         1996 | 4 Cil 2.2 Lts | 854G4          |
    +-------+----------+----------------+--------------+---------------+----------------+

This table has more than 1.5 Million rows and I have created a index that integrates initial_year and end_year in one and also initial_year has an index and end_year has another index independently like this structure.

CREATE TABLE `general` (
  `id_general` int(11) NOT NULL AUTO_INCREMENT,
  `id_marca_submarca` int(11) NOT NULL,
  `id_modelo_inicial` int(11) NOT NULL,
  `id_modelo_final` int(11) NOT NULL,
  `id_motor` int(11) NOT NULL,
  `id_articulo` int(11) NOT NULL,
  PRIMARY KEY (`id_general`),
  KEY `fk_general_articulo` (`id_articulo`),
  KEY `modelo_inicial_final` (`id_modelo_inicial`,`id_modelo_final`),
  KEY `indice_motor` (`id_motor`),
  KEY `indice_marca_submarca` (`id_marca_submarca`),
  KEY `indice_modelo_inicial` (`id_modelo_inicial`),
  KEY `indice_modelo_final` (`id_modelo_final`),
  CONSTRAINT `fk_general_articulo` FOREIGN KEY (`id_articulo`) REFERENCES `articulo` (`id_articulo`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1191853 DEFAULT CHARSET=utf8 

I have another table that contains different years like this sample data:

+---------+----------------+
| id_modelo | texto_modelo |
+-----------+--------------+
|      76   |      2014    |
|      75   |      2013    |
............................
|       1   |      1939    |
+-----------+--------------+

I created a query that contains subquery to obtain specific data but took a lot of time. I will put some queries I have tried but none of them

SELECT DISTINCT M.texto_modelo
FROM general G
INNER JOIN parque_vehicular_rigs.modelo M ON  G.id_modelo_inicial <= M.id_modelo AND G.id_modelo_final >= M.id_modelo
WHERE EXISTS
(
    SELECT DISTINCT A.id_articulo
    ...subquery...
    WHERE A.id_articulo = G.id_articulo AND AD.id_distribuidor = 1
)
ORDER BY M.texto_modelo DESC;

And this query took a lot of seconds, so I use EXPLAIN and report is:

asked 1 min ago

technology_dreamer

124

MySQL range query is slow