Large join on multiple indexed tables fails on copy of the schema but not on another

Question

We have two schemas, both from the same template and with near identical record count (less than 100). They are both running in the same MariaDB 5.5.68. We also have existing schemas that run this exact query over thousands of records without issue. They are also on this same DB instance.

Our issue is that sites newly created from our template, have started hanging when a specific query (details below) is executed. When the query hangs, it effectively locks the server until it is killed.

We have manual verified that the broken schema is configured the same with regards to collation, engine, indexing on the tables. All confirmed the same.

I was able to get the raw query from the processlist. Running this query directly against the DB works for older copies of the schema -- some with thousands of records. However, on newly created shcemas this behavior occurs even with less than 10 records.

The data being stored in these sites is extremely similar. The tables are simple. There are no florigen keys, no triggers or stored procedures are involved. The execution plan seems to indicate indexes are setup as needed.

We see the same behavior when we EXPLAIN the query. With the one that runs correctly, I get an execution plan.

If I try to explain the query on a schema where it fails, it just hangs as does the actual query.

The query is dynamically generated by the CMS. It is quite lengthy, but I've trimmed it to the relevant information.

SELECT `lnt_record_title`.`entry_id`, 
`lnt_record_title`.`title`, 
`lnt_record_title`.`url`,
 `lnt_record_title`.`active`, 
 `lnt_record_title`.`entry_date`, 
  [snip]
FROM (`lnt_record_title`)

RIGHT JOIN `lnt_field_data_43` ON `lnt_field_data_43`.`entry_id` = `lnt_record_title`.`entry_id`
RIGHT JOIN `lnt_field_data_44` ON `lnt_field_data_44`.`entry_id` = `lnt_record_title`.`entry_id`
[snip]
WHERE `lnt_record_title`.`host_id` =  '9921'
AND `lnt_record_title`.`site_id` =  '1'
AND `active` =  '1' 

I've snipped the full field and join list, but the snipped lines are identical to what is shown. At this point, I'm not sure what I even need to look at. UPDATE: I have also run mysqlcheck on the entire database, it reports all tables ok.

Answer 1

I would expect those to need LEFT JOIN, not RIGHT JOIN. (But I don't understand the intent.)

Although lnt_record_title seems to be quite small, it might be better if it had

INDEX(host_id, site_id, active, entry_id)

5.5.68 is quite old; suggest you upgrade if possible. A lot of improvements were made in the Optimizer after that.