Why is the index not used in the query execution plan?

Question

To briefly summarize the problem I have, it is that the index does not work properly in queries, which is just a difference in the database.

I am currently using two databases. For convenience, we will call them database1 and database2.

There are also tables such as member table (TB_MBR_INFO) and chat table (TB_CHAR_ROOM).

The TB_CHAT_ROOM table contains seller and buyer member IDs.

I want to get the membership number of this member ID.

So the query I created is as follows.

SELECT 
    A.regdate AS REG_DT,
    (select MBR_NO from database2.TB_MBR_INFO USE INDEX (TB_MBR_INFO_MBR_ID_IDX) where MBR_ID = A.seller) AS SELL_MBR_NO,
    (select MBR_NO from database2.TB_MBR_INFO USE INDEX (TB_MBR_INFO_MBR_ID_IDX) where MBR_ID = A.buyer) AS PRCH_MBR_NO
FROM database1.TB_CHAT_ROOM A

However, if you look at the execution plan of the query, possible_keys does not exist, so the member table is being fully scanned.

id|select_type       |table      |type |possible_keys|key                   |key_len|ref|rows  |Extra                   |
--+------------------+-----------+-----+-------------+----------------------+-------+---+------+------------------------+
 1|PRIMARY           |A          |ALL  |             |                      |       |   |474031|                        |
 4|DEPENDENT SUBQUERY|TB_MBR_INFO|index|             |TB_MBR_INFO_MBR_ID_IDX|62     |   |422098|Using where; Using index|
 3|DEPENDENT SUBQUERY|TB_MBR_INFO|index|             |TB_MBR_INFO_MBR_ID_IDX|62     |   |422098|Using where; Using index|

For testing, I copied the chat table and moved it to database2, and the index was applied normally.

SELECT 
    A.regdate AS REG_DT,
    (select MBR_NO from database2.TB_MBR_INFO USE INDEX (TB_MBR_INFO_MBR_ID_IDX) where MBR_ID = A.seller) AS SELL_MBR_NO,
    (select MBR_NO from database2.TB_MBR_INFO USE INDEX (TB_MBR_INFO_MBR_ID_IDX) where MBR_ID = A.buyer) AS PRCH_MBR_NO
FROM database2.TB_CHAT_ROOM A

id|select_type       |table      |type|possible_keys         |key                   |key_len|ref            |rows |Extra                   |
--+------------------+-----------+----+----------------------+----------------------+-------+---------------+-----+------------------------+
 1|PRIMARY           |A          |ALL |                      |                      |       |               |62721|                        |
 4|DEPENDENT SUBQUERY|TB_MBR_INFO|ref |TB_MBR_INFO_MBR_ID_IDX|TB_MBR_INFO_MBR_ID_IDX|62     |database2.A.buyer |1    |Using where; Using index|
 3|DEPENDENT SUBQUERY|TB_MBR_INFO|ref |TB_MBR_INFO_MBR_ID_IDX|TB_MBR_INFO_MBR_ID_IDX|62     |database2.A.seller|1    |Using where; Using index|

The only difference between the two is the database. Columns and indexes are all the same.

I moved everything to database 1 and tested it, just in case, but the problem of the index not working still persists.

I really want to solve the problem. If you know the cause, don’t pass it by.

Answer 1

Please provide SHOW CREATE TABLE for each table in each db involved.

I'll go out on a limb and say that there is an inconsistency in the dataypes or collations.

The SHOWs will help spot it.