mysql-where-int-vs-varchar.sql

-- query existing row with int (gets worse with amount of rows)
CREATE TABLE `identity` (
  `provider_name` varchar(256) NOT NULL,
  `provider_user_id` varchar(256) NOT NULL,
  `uuid` char(32) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`provider_name`,`provider_user_id`),
  KEY `uuid` (`uuid`),
  CONSTRAINT `identity_ibfk_1` FOREIGN KEY (`uuid`) REFERENCES `global_user` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

explain SELECT 
    identity.provider_name AS identity_provider_name,
    identity.provider_user_id AS identity_provider_user_id,
    identity.uuid AS identity_uuid,
    identity.updated_at AS identity_updated_at,
    identity.created_at AS identity_created_at
FROM
    identity
WHERE
    identity.provider_name = 'provider_02'
        AND identity.provider_user_id = 100242;

-- + ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ------------- + ---------- +
-- | id      | select_type      | table      | type      | possible_keys      | key      | key_len      | ref      | rows      | filtered      | Extra      |
-- + ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ------------- + ---------- +
-- | 1       | SIMPLE           | identity   | ref       | PRIMARY            | PRIMARY  | 258          | const    | 29792      | 100.00        | Using where |
-- + ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ------------- + ---------- +

-- query existing row with string
explain SELECT 
    identity.provider_name AS identity_provider_name,
    identity.provider_user_id AS identity_provider_user_id,
    identity.uuid AS identity_uuid,
    identity.updated_at AS identity_updated_at,
    identity.created_at AS identity_created_at
FROM
    identity
WHERE
    identity.provider_name = 'provider_02'
        AND identity.provider_user_id = '100242';

-- + ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ------------- + ---------- +
-- | id      | select_type      | table      | type      | possible_keys      | key      | key_len      | ref      | rows      | filtered      | Extra      |
-- + ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ------------- + ---------- +
-- | 1       | SIMPLE           | identity   | const     | PRIMARY            | PRIMARY  | 516          | const,const | 1         | 100.00        |            |
-- + ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ------------- + ---------- +

-- query for a missing row with string
explain SELECT 
    identity.provider_name AS identity_provider_name,
    identity.provider_user_id AS identity_provider_user_id,
    identity.uuid AS identity_uuid,
    identity.updated_at AS identity_updated_at,
    identity.created_at AS identity_created_at
FROM
    identity
WHERE
    identity.provider_name = 'provider_02'
        AND identity.provider_user_id = '4000000';

-- + ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ------------- + ---------- +
-- | id      | select_type      | table      | type      | possible_keys      | key      | key_len      | ref      | rows      | filtered      | Extra      |
-- + ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ------------- + ---------- +
-- | 1       | SIMPLE           |            |           |                    |          |              |          |           |               | Impossible WHERE noticed after reading const tables |
-- + ------- + ---------------- + ---------- + --------- + ------------------ + -------- + ------------ + -------- + --------- + ------------- + ---------- +