0

I've just been stuck for a bit on a couple of very weird index choices from mysql.
I have a table with half a billion entries and primary key is an auto inc ID

Original query: "select id from table WHERE id in (x,x,x,x,x,x)"

When I use a list of 2000 ids it works perfectly fine:

    EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "902.03"
    },
    "table": {
      "table_name": "table",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 2001,
      "rows_produced_per_join": 2001,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "701.93",
        "eval_cost": "200.10",
        "prefix_cost": "902.03",
        "data_read_per_join": "31M"
      },
      "used_columns": [
        "id"
      ],

Now the only thing I did was adding another 1000 ids to the SAME query:

EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "48429076.70"
    },
    "table": {
      "table_name": "table",
      "access_type": "index",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "intern_id_hash",
      "used_key_parts": [
        "intern_id_hash"
      ],
      "key_length": "5",
      "rows_examined_per_scan": 452740737,
      "rows_produced_per_join": 226370368,
      "filtered": "50.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "25792039.85",
        "eval_cost": "22637036.85",
        "prefix_cost": "48429076.70",
        "data_read_per_join": "3T"
      },
      "used_columns": [
        "id"
      ],

When I add "FORCE INDEX (PRIMARY)" it shows query cost of 1300 and works fine.
'intern_id_hash' is an index on an unused column (for historic reasons still present)

Question: What the heck is happening with the query optimizer that two identical primary id queries with a different number of integers in the IN () clause suddenly malfunctions ?

  • Optimizer is restricted in time and another resources for analysing and execution plan creation. So it may make mistakes. Forcing index hint is safe solution. You may reconfigure optimizer, of course, but I don't recommend - this affects on all queries executed, and the effect may be positive not always. Also optimiser uses tables statistic for plan creation, but this statistic may be inaccurate - refreshing may help. – Akina Dec 25 '19 at 6:13
  • Perhaps one set of ids looked like a tight "range" of values, and the other was more scattered? – Rick James Dec 25 '19 at 7:24
  • 1
    Have you concidered using a JOIN against a temp table instead of an IN predicate? – Lennart Dec 25 '19 at 11:48
  • Please post A) TEXT results of SHOW CREATE TABLE table; and B) complete 1st query and C) complete 2nd query. The first explain is for access type RANGE check using your id and the second explain is for access type INDEX check using your intern_id_hash. – Wilson Hauck Jan 13 at 10:07
  • I used "optimize table" and the problem is gone, it was a mysql index bug. I forgot about those when writing the question – John Jan 17 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.