CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    197

    MySql not using index with IN and subquery

    I have a Mysql database with 2 tables:
    clientes and code
    clientes have a field called celda (index called CG)
    and code have a field called CG (index called CG)

    they have the same data type

    I have indexes in both fields.

    EXPLAIN EXTENDED SELECT *
    FROM Clientes
    WHERE celda IN ('26022','26023','26024')

    that uses the indexes and gave me this EXPLAIN
    Code:
        id  select_type  table                    type    possible_keys  key     key_len  ref       rows  Extra        
    ------  -----------  -----------------------  ------  -------------  ------  -------  ------  ------  -------------
         1  SIMPLE       Clientes                 range         CG       CG     20       (NULL)     389  Using where

    SET @sitio='AAA';

    EXPLAIN EXTENDED SELECT *
    FROM Clientes
    WHERE celda IN (SELECT CG FROM CODE
    WHERE sitio=@sitio)

    the subquery gives as a result the same values im using directly in previuos query: '26022','26023','26024' but the explain is this:

    Code:
        id  select_type         table                    type            possible_keys  key     key_len  ref        rows  Extra        
    ------  ------------------  -----------------------  --------------  -------------  ------  -------  ------  -------  -------------
         1  PRIMARY             Clientes                 ALL             (NULL)         (NULL)  (NULL)   (NULL)  2689275  Using where  
         2  DEPENDENT SUBQUERY  CODE                     index_subquery  CG             CG      23       func          1  Using where
    Any idea why mysql is not using the index in the seccond query?
    thanks!

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: MySql not using index with IN and subquery

    How did you detect that "the subquery gives as a result the same values im using directly in previuos query: '26022','26023','26024' "?
    Victor Nijegorodov

  3. #3
    Join Date
    Jan 2006
    Posts
    197

    Re: MySql not using index with IN and subquery

    Maybe I didnt explain something right
    if I execute:

    SELECT CG FROM CODE
    WHERE sitio='AAA'

    the result is: '26022','26023','26024'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured