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

Thank you for posting the EXPLAIN in a format that is easy to read! — Bill Karwin
– Bill Karwin, Commented May 12, 2022 at 13:54

Bill Karwin · Accepted Answer · 2022-05-12 13:53:05Z

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.

answered May 12, 2022 at 13:53

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

K.Kang628 Over a year ago

Thank you @Karwin, table D has 928,430 records and table A has 12990 records. And B has 82665. I think this is not small. I will try to add compound index as you said.

Collectives™ on Stack Overflow

Optimize MySQL Query using explain

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related