Understanding how index works

Federico Coco 0
Viewed 59

Table definition 

CREATE TABLE `prospectos` (
  `provincia` tinyint(3) unsigned NOT NULL,
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(60) COLLATE utf8_bin NOT NULL,
  `telefono_fijo` varchar(15) COLLATE utf8_bin NOT NULL,
  `telefono_movil` varchar(15) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`,`provincia`),
  KEY `nombre_idx` (`nombre`),
  KEY `tel_fijo_idx` (`provincia`,`telefono_fijo`) USING BTREE,
  KEY `tel_movil_idx` (`provincia`,`telefono_movil`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=35142 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
 PARTITION BY RANGE (`provincia`)
(PARTITION `p01` VALUES LESS THAN (2) ENGINE = InnoDB,
..
..
..
PARTITION `p24` VALUES LESS THAN (25) ENGINE = InnoDB,
PARTITION `p99` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

doing this search is using both fields in primary key

explain format=json select * from prospectos where provincia = 20 and id = 23;

query_block": {
    "select_id": 1,
    "table": {
      "table_name": "prospectos",
      "partitions": ["p20"],
      "access_type": "const",
      "possible_keys": ["PRIMARY", "tel_fijo_idx", "tel_movil_idx"],
      "key": "PRIMARY",
      "key_length": "5",
      "used_key_parts": ["id", "provincia"],
      "ref": ["const", "const"],
      "rows": 1,
      "filtered": 100

Now doing another select to use a different key why is not using both fields?

explain format=json select * from prospectos where provincia = 20 and telefono_fijo = 3424527000;

"query_block": {
    "select_id": 1,
    "table": {
      "table_name": "prospectos",
      "partitions": ["p20"],
      "access_type": "ref",
      "possible_keys": ["tel_fijo_idx", "tel_movil_idx"],
      "key": "tel_fijo_idx",
      "key_length": "1",
      "used_key_parts": ["provincia"],
      "ref": ["const"],
      "rows": 16042,
      "filtered": 100,
      "attached_condition": "prospectos.telefono_fijo = 3424527000"

It use only provincia on tel_fijo_idx and not telefono_fijo for searching.

Why this search is not using both fields???

mariadb mysql
1 Answers

Because telefono_fijo in the query is unquoted its treated by as a number.

Because of type conversion. "if one argument is string and the other argument is integer, they are compared as decimals" (from MariaDB-10.3.36 (and other releases from last month)). On older versions they would be compared as as a floating point real number.

So with your query, MariaDB needs to convert every telefono_fijo to decimal (or float if earlier) and because conversion takes place, this aspect of the index isn't used.

One solution is to quote telefono_fijo in the query to be treated as a string. This is good if the phone number can contain leading 0 or other non-numerical characters. Even in this case using utf8 is probably excessive and an ascii/latin/binary character set is sufficient.

explain format=json select * from prospectos where provincia = 20 and telefono_fijo = "3424527000"

{
"query_block": {
"select_id": 1,
"table": {
"table_name": "prospectos",
"access_type": "ref",
"possible_keys": ["tel_fijo_idx", "tel_movil_idx"],
"key": "tel_fijo_idx",
"key_length": "48",
"used_key_parts": ["provincia", "telefono_fijo"],
"ref": ["const", "const"],
"rows": 1,
"filtered": 100,
"index_condition": "prospectos.telefono_fijo = '3424527000'"
}
}
}

The other solution for the (10.3.36 and other versions released on/after 15 Aug 2022 (release notes for date)) is to use the decimal type for telefono_finjo on the assumption that numbers without leading 0s occur is the dataset. (alter table prospectos modify telefono_fijo decimal(15) NOT NULL). When this is done, the index will be used (note used_key_parts in explain) regardless if the constant filter for telefono_fijo is quoted like a string, or unquoted like a number.

explain format=json select * from prospectos where provincia = 20 and telefono_fijo = 3424527000

{
"query_block": {
"select_id": 1,
"table": {
"table_name": "prospectos",
"access_type": "ref",
"possible_keys": ["tel_fijo_idx", "tel_movil_idx"],
"key": "tel_fijo_idx",
"key_length": "8",
"used_key_parts": ["provincia", "telefono_fijo"],
"ref": ["const", "const"],
"rows": 1,
"filtered": 100
}
}
}

ref: fiddle

danblack0
Related