How to properly compare BigInt Column in MySQL 5.7

Question

I took a lok at Mysql 5.0.91 BIGINT column value comparison with '1' to check if there is some best practices for BigInt but found nothing.

I have a column that is BigInt(20) and my query has a WHERE clause that compares the column of type BigInt IN (). This has a major impact on the performance. Althought I have an index for that when I don't use this IN condition the query performance increases a lot.

So what is the best way to do that comparison I need? IN () is a good practice or there is a best approach?

Table Description (Obfuscated for security reasons)

Field,Type,Null,Key,Default,Extra
id,bigint(20),NO,PRI,NULL,auto_increment
status,varchar(64),NO,,NULL,
dono_id,bigint(20),YES,,NULL,
dono_tipo,varchar(64),YES,MUL,NULL,
MyBigIntField,bigint(20),YES,MUL,NULL

QUERY

explain select
  DISTINCT(MyBigIntField)
FROM
  MyTable
WHERE
  MyBigIntField IN ('16', '49', '58', '155', '226')
  AND NOT (status = 'Failure')
  AND dono_id <> 1106
  and dono_tipo = 'Purchase';

Execution Plan

EXPLAIN
"{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "23.21"
    },
    "duplicates_removal": {
      "using_filesort": false,
      "table": {
        "table_name": "MyTable",
        "access_type": "range",
        "possible_keys": [
          "idx_MyTable_owner",
          "IDX_MyTable_PI"
        ],
        "key": "IDX_MyTable_PI",
        "used_key_parts": [
          "MyBigIntField"
        ],
        "key_length": "9",
        "rows_examined_per_scan": 13,
        "rows_produced_per_join": 5,
        "filtered": "45.00",
        "index_condition": "(`MyDatabase`.`MyTable`.`MyBigIntField` in (16,49,58,155,226))",
        "cost_info": {
          "read_cost": "22.04",
          "eval_cost": "1.17",
          "prefix_cost": "23.21",
          "data_read_per_join": "231K"
        },
        "used_columns": [
          "id",
          "status",
          "dono_id",
          "dono_tipo",
          "MyBigIntField"
        ],
        "attached_condition": "((`MyDatabase`.`MyTable`.`status` <> 'Failure') and (`MyDatabase`.`MyTable`.`dono_id` <> 1106) and (`MyDatabase`.`MyTable`.`dono_tipo` = 'Purchase'))"
      }
    }
  }
}"

Rick James · Answer

myBigInt = 1   -- fine
myBigInt = "1"   -- also good
myBigInt = (1)   -- optimizer treats this as the same

myBigInt = (1,2)  -- Now the query may run a lot slower
myBigInt = ('1', '2')  -- Same as without quotes

That is, IN with more than one value may or may not be done an efficient way. We need to see the rest of the query in order to discuss that might happen.

Note that many APIs quote numbers while 'binding' values. As pointed out above, that does not hurt performance.

WHERE
  MyBigIntField IN ('16', '49', '58', '155', '226')
  AND NOT (status = 'Failure')
  AND dono_id <> 1106
  and dono_tipo = 'Purchase';

Add a 'composite' index. Start with any = tested columns, then IN and/or one of the other columns. So , for that WHERE:

INDEX(dono_tipo, MyBigIntField)

NOT, <>, >, BETWEEN are harder to optimize.

If there are only two status values, then

 ... AND status = 'success' ...

INDEX(dono_tipo, status, MyBigIntField)

(There may be other optimizations that you show up if the example were not too obfuscated.)

Here's a similar case, with a different analysis:

WHERE
  MyBigIntField IN ('16', '49', '58', '155', '226')
  AND foo > 5
  and dono_tipo = 'Purchase';

In this case, I recommend

INDEX(dono_tipo,   -- first, because of "="
      MyBigIntField,  -- next because of IN
      foo)   -- range, which will be useful if IN has only one value

You seem to have MyBigIntField NULLable; perhaps it should be NOT NULL? (This does not impact what I have said above.)

How to properly compare BigInt Column in MySQL 5.7

1 Answers