Optimize MySQL Query using explain

Question

I have following SQL

SELECT COUNT(*) FROM A INNER JOIN B ON A.evnumber=B.evnumber INNER JOIN D ON B.userid=D.userid

This is explain result.

[
{
    "id": "1",
    "select_type": "SIMPLE",
    "table": "A",
    "type": "index",
    "possible_keys": "evnumber",
    "key": "evnumber",
    "key_len": "768",
    "ref": null,
    "rows": "13926",
    "Extra": "Using where; Using index"
},
{
    "id": "1",
    "select_type": "SIMPLE",
    "table": "B",
    "type": "ref",
    "possible_keys": "evnumber,UserID",
    "key": "evnumber",
    "key_len": "768",
    "ref": "A.evnumber",
    "rows": "1",
    "Extra": "Using where"
},
{
    "id": "1",
    "select_type": "SIMPLE",
    "table": "D",
    "type": "ref",
    "possible_keys": "mdl_userinfodata_usefie_ix",
    "key": "mdl_userinfodata_usefie_ix",
    "key_len": "8",
    "ref": "B.UserId",
    "rows": "134",
    "Extra": "Using where; Using index"
}

]

When I execute this SQL, it takes 40 seconds. According to products of values of rows column (13926x134x1=1,866,084), it is impossible, I think. Please help me, How can I improve this? MySQL version is 5.6

Answer 1

The query is already using indexes for the joins, but it isn't using a covering index for B. That's the only possible improvement I can see.

I would add a compound index on B for columns (evnumber, user_id). That should allow the query get "Using index" for table B, indicating that it uses the index only, it doesn't have to read table rows. It won't reduce the number you see in rows: 1 for table B, but it will help make it a little easier for B to join to D.

Sometimes this type of optimization has given a good result. For very large tables, it can improve performance a lot. But your tables are small, so it may not make as great a difference.