Ufdjrw

We have two tables, messages and customercontracts defined as follows:

create table customercontracts (customer_id varchar(20), 
 contractnumber varchar(20), 
 role varchar(4));
alter table customercontracts add index contractnumber (contractnumber);

create table messages (customer_id varchar(20), 
 contractnumber varchar(20), 
 message varchar(400));
alter table messages add index contractnumber (contractnumber);
alter table messages add index customer_id (customer_id );

And a query like this:

select * from messages m, customercontracts c 
 where m.customer_id = '12345' 
 and c.contractnumber = m.contractnumber;

There are approximately 4,000 messages rows and 3,000,000 customercontracts rows. The above query takes approximately 4 seconds to execute despite there being indexes on both customer_id and contractnumber. 'Explain' (in MySQL Workbench) shows a full table scan on customercontracts and a query cost of 628,000.

Questions:

1) Why is there a full table scan on customercontracts when I have indexes on these tables? What is the cause of the poor performance?

2) How can I rewrite this query to be performant?

IS NOT THE ANSWER:

I have test it with ca. 1.000.000 rows in each table and they use the Index

MariaDB []> EXPLAIN SELECT * FROM messages m, customercontracts c
 -> WHERE m.customer_id = 'CUST_22345'
 -> AND c.contractnumber = m.contractnumber;
+------+-------------+-------+------+----------------------------+----------------+---------+------------------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+----------------------------+----------------+---------+------------------------+------+------------------------------------+
| 1 | SIMPLE | m | ref | contractnumber,customer_id | customer_id | 63 | const | 1 | Using index condition; Using where |
| 1 | SIMPLE | c | ref | contractnumber | contractnumber | 63 | test2.m.contractnumber | 1 | |
+------+-------------+-------+------+----------------------------+----------------+---------+------------------------+------+------------------------------------+
2 rows in set (0.002 sec)

MariaDB []> SELECT * FROM messages m, customercontracts c
 -> WHERE m.customer_id = 'CUST_22345'
 -> AND c.contractnumber = m.contractnumber;
+-------------+----------------+---------+-------------+----------------+------+
| customer_id | contractnumber | message | customer_id | contractnumber | role |
+-------------+----------------+---------+-------------+----------------+------+
| CUST_22345 | 22345 | NULL | CUST_22345 | 22345 | NULL |
| CUST_22345 | 22345 | NULL | CUST_22345 | 22345 | NULL |
| CUST_22345 | 22345 | NULL | CUST_22345 | 22345 | NULL |
| CUST_22345 | 22345 | NULL | CUST_22345 | 22345 | NULL |
+-------------+----------------+---------+-------------+----------------+------+
4 rows in set (0.009 sec)

MariaDB []>

I would suggest try composite index on message Table customer_id,contractnumber and check performance, try to avoid using * in select .

I think I've finally found the answer to this. My sample table creation code above did not show the character encoding (a learn for me, don't shortcut sample code!). As it turns out, the customercontacts table is encoded in utf8 while the messages table is encoded in utf8mb4.

When columns of different character encoding are joined, indexes cannot be used.

This answers the 'why is this happening'. To fix this, we modified the utf8m4 table to downgrade the encoding of the indexed column to utf8 (while keeping the encoding of the rest of the table as utf8mb4). This has fixed performance for us.

I'd consider adding an INTEGER "id" field as the primary key for customercontracts and then reference through customercontracts_id instead of contractnumber in the messages table. You should see an improvement in performance already.

Alternatively, you can try adding FULLTEXT indexes on both contractnumber columns, but I'd advise for the first option.