Same data , same queries , different response time. 

Server 1 is Mariadb version 10.2.6  using the innodb version 5.7.14

Server 2 is MariaDB version 10.3.17 a using the innodb version 10.3.17

There is  a VIEW and within the VIEW there is a UNION covering two SELECT statements . The underlying dataset is  greater than 100 million rows.Note: Dataset is refreshed twice a year - 

Since the migration to Server 2 - the index from the Table 1 is not used  , causing a significant drop is response time for certain queries. The Execution Plan is now forcing a Table Scan rather than the previous ref type. 

Clearly there has been a significant change in the Optimizer impacting DERIVED TABLES.

Troubleshooting included:

1) OPTIMIZE TABLE 

2) ANALYZE TABLE

3)  'SET max_seeks_for_key=100'

4)  Tested replacing the optimizer_switch values on Server 2 from the exact values on Server 1. 

5)  By default the innodb_stats_sample_pages are set at 8. Often , this can be insufficient to get a wide enough sample of data to optimize the statistics

SHOW VARIABLES LIKE 'innodb_stats_sample_pages%'

I changed it to 200 - and ran OPTIMIZE TABLE

The final solution\workaround was to materialize the data set into a BASE TABLE and create the same indexes over the data. The queries are now running as expected.

As the data set is only refreshed twice a year - it's just an extra couple of steps to place the values into a BASE TABLE. The data is used as read only  - so this was a fairly straightforward procedure .   i.e create a one-off INSERT into the base table , of the VIEW output. 

If the data need to be edited - this would require some extra customisations

MariaDB doesn't have a native materialization method , such as other DBMS. For example SQL Server has the concept of materialized views -  Use SQL Materialized view to eliminate DISTINCT and speed up response time   - SQL Server supports creating indexes on a VIEW .