MySql Simple query with "Where In" does not recognize Index

Navigation
2

Good, I have a problem with the Mysql I have a very small table: 

CREATE TABLE 'Prueba' (
  'idTipoCliente' int(10) COLLATE utf8_general_mysql500_ci NOT NULL,
  'descripcion' varchar(20) COLLATE utf8_general_mysql500_ci NOT NULL,
  'observacion' varchar(255) COLLATE utf8_general_mysql500_ci NOT NULL,
  PRIMARY KEY ('idTipoCliente')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

with only these few rows 

INSERT INTO 'Prueba' VALUES ('1', 'Cliente1', 'datos1');
INSERT INTO 'Prueba' VALUES ('2', 'Cliente2', 'datos2');
INSERT INTO 'Prueba' VALUES ('3', 'Cliente3', 'datos3');

Then I'm trying to make a simple query like this: 

Explain SELECT * FROM 'Prueba' WHERE 'idTipoCliente' in (1,2,3)

and I get this result 

id  select_type table   type    possible_keys   key  key_len   ref  rows    Extra
1   SIMPLE      Prueba  ALL     PRIMARY         NULL NULL      NULL 3   Using where

That is not recognizing the KEY (PRIMARY), but if I make a query like this 

Explain SELECT * FROM 'Prueba' WHERE 'idTipoCliente'>=1;

Or this 

Explain SELECT * FROM 'Prueba' FORCE Index(PRIMARY) WHERE 'idTipoCliente' in (1,2,3);

if it recognizes the KEY

My problem is that the Key is lost when I use "where in", because I am wanting to use this table in a more complex query with several other tables and Inner Join, and the solution I see is to use FORCE INDEX, but I wanted to know if there is another solution.

    
asked by marjes 10.08.2017 в 19:52
source

1 answer

1

The answer is very simple: with three records the database engine considers not worth using the primary key index.

See what happens in the test code: ver demo aquí

  • When we use the original data, in effect, the index appears as NULL :

    • Código : 

    CREATE TABLE 'Prueba' (
  'idTipoCliente' INT(10)   NOT NULL AUTO_INCREMENT,
  'descripcion' varchar(20) COLLATE utf8_general_mysql500_ci NOT NULL,
  'observacion' varchar(255) COLLATE utf8_general_mysql500_ci NOT NULL
     ,  PRIMARY KEY ('idTipoCliente')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;


INSERT INTO 'Prueba' VALUES ('1', 'Cliente1', 'datos1');
INSERT INTO 'Prueba' VALUES ('2', 'Cliente2', 'datos2');
INSERT INTO 'Prueba' VALUES ('3', 'Cliente3', 'datos3');


EXPLAIN SELECT * FROM 'Prueba' WHERE 'idTipoCliente' in (1,2,3);

    Resultado : 

    id  select_type   table   partitions  type  possible_keys  key  key_len ref    rows filtered      Extra
1   SIMPLE        Prueba  NULL        ALL   PRIMARY        NULL NULL    NULL     3  100 Using where
  • Now let's add 1,000 rows to the table and let's see what happens.

    • Código : 

    -- Creamos otra tabla exactamente igual

CREATE TABLE 'PruebaMax' (
  'idTipoCliente' INT(10)   NOT NULL AUTO_INCREMENT,
  'descripcion' varchar(20) COLLATE utf8_general_mysql500_ci NOT NULL,
  'observacion' varchar(255) COLLATE utf8_general_mysql500_ci NOT NULL
     ,  PRIMARY KEY ('idTipoCliente')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;


-- Agregamos 1,000 registros de prueba

CREATE PROCEDURE testInsert()   
BEGIN
DECLARE i INT DEFAULT 1; 
WHILE (i <= 1000) DO
    INSERT INTO 'PruebaMax' (idTipoCliente, descripcion, observacion) values (i, '1', '1');
    SET i = i+1;
END WHILE;
END;

CALL testInsert(); 


EXPLAIN SELECT * FROM 'PruebaMax' WHERE 'idTipoCliente' in (1,2,3);

SELECT COUNT(*) FROM 'PruebaMax';

    Resultado : 

    id  select_type table       partitions  type    possible_keys   key      key_len    ref     rows    filtered    Extra
1   SIMPLE      PruebaMax   NULL        range   PRIMARY         PRIMARY  4          NULL    3       100         Using index condition

COUNT(*)
1000

    What do you think? When your table grows and grows larger, then the engine will effectively start using the primary key index. From how many rows? That could be an interesting question.

        
    answered by 11.08.2017 / 03:32
    source
    How do I get the selected data from a combo? Pass a unit test of views based on Django classes