title: MySQL range query is slow
body: <p>I have read different links like <a href="http://goo.gl/1nr3s2" rel="nofollow noreferrer">http://goo.gl/1nr3s2</a>, <a href="http://goo.gl/gv4Vlc" rel="nofollow noreferrer">http://goo.gl/gv4Vlc</a> and other stackoverflow questions, but none of them help me with this problem.</p>
  
  <p>This problem interacts with multiple tables, but the EXPLAIN method help me identify range is the main problem with the query.</p>
  
  <p>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)</p>
  
  <pre><code>    +-------+----------+----------------+--------------+---------------+----------------+
      | 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          |
      +-------+----------+----------------+--------------+---------------+----------------+
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>I have another table that contains different years like this sample data:</p>
  
  <pre><code>+---------+----------------+
  | id_modelo | texto_modelo |
  +-----------+--------------+
  |      76   |      2014    |
  |      75   |      2013    |
  ............................
  |       1   |      1939    |
  +-----------+--------------+
  </code></pre>
  
  <p>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 have worked properly for me</p>
  
  <pre><code>SELECT DISTINCT M.texto_modelo
  FROM general G
  INNER JOIN parque_vehicular.modelo M ON  G.id_modelo_inicial &lt;= M.id_modelo AND G.id_modelo_final &gt;= 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;
  </code></pre>
  
  <p>And this query took a lot of seconds, so I use EXPLAIN and report is:</p>
  
  <p><img src="https://i.stack.imgur.com/Ks623.png" alt="enter image description here"></p>
  
  <p>This is another query I tried.</p>
  
  <pre><code>SELECT DISTINCT M.texto_modelo
  FROM general G
  INNER JOIN parque_vehicular_rigs.modelo M ON M.id_modelo BETWEEN G.id_modelo_inicial AND G.id_modelo_final
  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;
  </code></pre>