MariaDB Inner join is slow

execution-planmariadb-10.3performanceperformance-tuning

Here is my SQL (with explain):

EXPLAIN
SELECT * FROM
GBI gbi
INNER JOIN VF_KRED kred ON
    gbi.vendor = kred.vendor;

Giving below the outcome:

Now, the select SQL takes ~10 seconds to produce the outcome.

Below are the indexes on tables:

VF_KRED table:

GBI table:

Any clues?

Best Answer

Our solution: Set the variable optimizer_search_depth to zero. The same query now needs 0.3 instead of 13 seconds.

To test it, call before your statement:

set SESSION optimizer_search_depth=0;

To make it permanent put it in the my.conf file and restart the db.

Related Solutions

MySQL query took 98 seconds to run but EXPLAIN looks okay

It's interesting that the slow-query log entry states no rows were examined, and no rows were returned. I wonder if MySQL determined the query produced no results by looking only at indexes.

When you say you can't recreate the problem, do you mean MySQL returns the expected results for the query in much less time on subsequent attempts? That's often an indication that the first execution induced a lot of file IO to load data into buffers or cache. Subsequent executions of the same query against the same data benefit from faster access to the data.

MySQL query took 98 seconds to run but EXPLAIN looks okay

For a 10 tables query, 98 seconds could actually be OK.

Please note that under mysql your where clause:

       `a2`.`abbreviated_name` = 'PSS' 
   AND `h3`.`name` = 'Subject' 
   AND `h`.`name` = 'Scaled Score' 
   AND h2.name = 'Reading' 
   AND s.school_id = '112' 
   AND `a3`.`grade_level_id` = '8'

is implicitly canceling the LEFT JOINS, since each time you ask for "column=value", it means "column IS NOT NULL". Use INNER JOINS instead of LEFT JOINS for the tables a2, h3, h, h2, s and a3, and add theses respective filters within the ON part of their respective INNER JOIN.

rgds.

PS: By the way, would you mind showing your SHOW PROFILE result?

Best Answer

Related Solutions

MySQL query took 98 seconds to run but EXPLAIN looks okay

MySQL query took 98 seconds to run but EXPLAIN looks okay

Related Question