Optimizing SQL query with inner join on 3 big tables

Question

I have a SQL query with 3 tables joined on a distant MySQL DB

Two of these tables have size of about 15GB (STEP_RESULT and meas_numericlimit)

Before sending data, a TMP table is created on the server which takes about 2.5 hours to end

I am not the server admin but I can observe my queries with the MySql WorkBench This server is up to date with 64GB of RAM

How can I optimize this query ?

Thank you

My query :

select 
t1.UUT_NAME, 
t1.STATION_NUM, 
t1.START_DATE_TIME, 
t3.LOW_LIMIT, 
t3.DATA,
t3.HIGH_LIMIT,
t3.UNITS,
t2b.STEP_NAME 
from 
meas_numericlimit t3 
    inner join STEP_RESULT t2a on t3.ID = t2a.STEP_ID
    inner join STEP_RESULT t2b on t2a.STEP_PARENT = t2b.STEP_ID
    inner join uut_result t1 on t2b.UUT_RESULT = t1.ID
where 
t1.UUT_NAME like 'Variable1-1%' and 
t1.STATION_NUM = 'variable2' and 
t2b.STEP_NAME = 'variable3' and 
t2b.STEP_TYPE = 'constant'

Here the SHOW TABLES and EXPLAIN output queries :

+--------------------+
| Tables_in_spectrum |
+--------------------+
| cal_dates          |
| calibrage          |
| execution_time     |
| meas_numericlimit  |
| station_feature    |
| step_callexe       |
| step_graph         |
| step_msgjnl        |
| step_msgpopup      |
| step_passfail      |
| step_result        |
| step_seqcall       |
| step_stringvalue   |
| syst_event         |
| uptime             |
| users              |
| uut_result         |
+--------------------+

and

+----+-------------+-------+--------+-------------------------+--------

| id | select_type | table | type   | possible_keys           | key     
| 
+----+-------------+-------+--------+-------------------------+--------

|  1 | SIMPLE      | t2a   | ALL    | NULL                    | NULL    
|
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY                 | PRIMARY 
|
|  1 | SIMPLE      | t2b   | ALL    | NULL                    | NULL    
|
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY,FK_uut_result_1 | PRIMARY 
|
+----+-------------+-------+--------+-------------------------+--------


---------+----------------------+----------- +-------------------------

key_len  | ref                  |    rows    | Extra                     
|
---------+----------------------+----------- +-------------------------
NULL    | NULL                 | 48120004   |                           
|
40      | spectrum.t2a.STEP_ID |        1   |                           
|
 NULL    | NULL                 | 48120004   | Using where; Using join
                                                             buffer  |
40      | spectrum.t2b.UUT_RESULT |       1 | Using where               
|
-------+----------------------+------------+---------------------------

Here the SHOW CREATE TABLE :

CREATE TABLE `uut_result` (
`ID` varchar(38) NOT NULL DEFAULT '', 
`STATION_NUM` varchar(255) DEFAULT NULL, 
`SOFTVER_ODTGEN` varchar(10) DEFAULT NULL, 
`HARDVER_ODTGEN` varchar(10) DEFAULT NULL, 
`NEXT_CAL_DATE` date DEFAULT NULL, 
`UUT_NAME` varchar(255) DEFAULT NULL, 
`UUT_SERIAL_NUMBER` varchar(255) DEFAULT NULL, 
`UUT_VERSION` varchar(255) DEFAULT NULL, 
`USER_LOGIN_NAME` varchar(255) DEFAULT NULL, 
`USER_LOGIN_LOGIN` varchar(255) NOT NULL DEFAULT '', 
`START_DATE_TIME` datetime DEFAULT NULL, 
`EXECUTION_TIME` float DEFAULT NULL, 
`UUT_STATUS` varchar(255) DEFAULT NULL, 
`UUT_ERROR_CODE` int(11) DEFAULT NULL, 
`UUT_ERROR_MESSAGE` varchar(1023) DEFAULT NULL, 
`PAT_NAME` varchar(255) NOT NULL DEFAULT '', 
`PAT_VERSION` varchar(10) NOT NULL DEFAULT '', 
`TEST_LEVEL` varchar(50) DEFAULT NULL, 
`INTERFACE_ID` int(10) unsigned NOT NULL DEFAULT '0', 
`EXECUTION_MODE` varchar(45) DEFAULT NULL, 
`LOOP_MODE` varchar(45) DEFAULT NULL, 
`STOP_ON_FAIL` tinyint(4) unsigned NOT NULL DEFAULT '0', 
`EXECUTION_COMMENT` text, 
PRIMARY KEY (`ID`), 
KEY `FK_uut_result_1` (`STATION_NUM`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and

CREATE TABLE `meas_numericlimit` (
`ID` varchar(38) NOT NULL DEFAULT '', 
`STEP_RESULT` varchar(38) NOT NULL DEFAULT '', 
`NAME` varchar(255) DEFAULT NULL, 
`COMP_OPERATOR` varchar(30) DEFAULT NULL, 
`HIGH_LIMIT` double DEFAULT NULL, 
`LOW_LIMIT` double DEFAULT NULL, 
`UNITS` varchar(255) DEFAULT NULL, 
`DATA` double DEFAULT NULL, 
`STATUS` varchar(255) DEFAULT NULL, 
`FORMAT` varchar(15) DEFAULT NULL, 
`NANDATA` int(11) DEFAULT '0', 
PRIMARY KEY (`ID`), 
KEY `FK_meas_numericlimit_1` (`STEP_RESULT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

and

CREATE TABLE `step_result` (
`ID` varchar(38) NOT NULL DEFAULT '', 
`UUT_RESULT` varchar(38) NOT NULL DEFAULT '', 
`STEP_PARENT` varchar(38) DEFAULT NULL, 
`STEP_NAME` varchar(255) DEFAULT NULL, 
`STEP_ID` varchar(38) NOT NULL DEFAULT '', 
`STEP_TYPE` varchar(255) DEFAULT NULL, 
`STATUS` varchar(255) DEFAULT NULL, 
`REPORT_TEXT` text, 
`DIAG` text, 
`ERROR_OCCURRED` tinyint(1) NOT NULL DEFAULT '0', 
`ERROR_CODE` int(11) DEFAULT NULL, 
`ERROR_MESSAGE` varchar(1023) DEFAULT NULL, 
`MODULE_TIME` float DEFAULT NULL, 
`TOTAL_TIME` float DEFAULT NULL, 
`NUM_LOOPS` int(11) DEFAULT NULL, 
`NUM_PASSED` int(11) DEFAULT NULL, 
`NUM_FAILED` int(11) DEFAULT NULL, 
`ENDING_LOOP_INDEX` int(11) DEFAULT NULL, 
`LOOP_INDEX` int(11) DEFAULT NULL, 
`INTERACTIVE_EXENUM` int(11) DEFAULT NULL, 
`STEP_GROUP` varchar(30) DEFAULT NULL, 
`STEP_INDEX` int(11) DEFAULT NULL, 
`ORDER_NUMBER` int(11) DEFAULT NULL, 
PRIMARY KEY (`ID`), 
KEY `FK_step_result_1` (`UUT_RESULT`), 
KEY `IDX_step_parent` (`STEP_PARENT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin

user_107744 · Answer

If these were InnoDB tables (and not MyISAM) I'd write the query like this:

SELECT t1.uut_name
     , t1.station_num
     , t1.start_date_time
     , t3.low_limit
     , t3.data
     , t3.high_limit
     , t3.units
     , t2b.step_name
  FROM uut_result t1
  JOIN step_result t2b
    ON t2b.uut_result     = t1.id
   AND t2b.step_type      = 'constant'
   AND t2b.step_name      = 'variable3' 
  JOIN step_result t2a
    ON t2a.step_parent    = t2b.step_id
  JOIN meas_numericlimit t3
    ON t3.id = t2a.step_id
 WHERE t1.station_num     = 'variable2'
   AND t1.uut_name     LIKE 'Variable1-1%'

And I would create suitable covering indexes:

... uut_result_IX1 ON uut_result (station_num, uut_name, start_date_time, id)

... step_result_IX1 ON step_result (uut_result, step_type, step_name, step_id) 

... step_result_IX2 ON step_result (step_parent, step_id)

For a possible incremental performance increase, I'd also consider one more covering index ...

... meas_numericlimit_IX1 ON meas_numericlimit (id, low_limit, data, high_limit, units)

(With InnoDB, the primary key column(s) are the cluster key, so there is less of a benefit here.)

With MyISAM, suitable indexes are important. But I don't think that covering indexes give the same kind of benefit as they do with InnoDB.

With MyISAM, a covering index isn't going to avoid a visit to the pages in the underlying table. So I think we'd be better off with shorter indexes, with just the columns used in the predicates:

... uut_result_IX1 ON uut_result (station_num, uut_name)

... step_result_IX1 ON step_result (uut_result, step_type, step_name)

... step_result_IX2 ON step_result (step_parent)

Increasing key_buffer_size to a larger value will allow MyISAM indexes to be cached; but don't over allocate. There's no caching of the MyISAM table pages, except the OS file system cache.

Making modifications to other configuration parameters for MyISAM (in my limited experience with MyISAM) have a net effect of just over allocating (i.e. wasting) memory, with negligible or marginal impacts on performance. So I wouldn't mess with those. (That's not to say that some haven't gleaned some performance improvement. I just haven't had any success with my test cases.)

Instead of mucking with MyISAM tuning, I'd expend my efforts to lobby for these tables to be changed to InnoDB storage engine. And then tuning InnoDB.

EDIT

The order of the columns in the indexes I suggested is based on having the leading columns with the equality comparisons... with a preference for the most "selective" columns before the columns with more repeated values.

These indexes were suggested with an execution plan in mind ... starting with t1 (uut_result) as the driving table, with a join to t2b, then the join to t2a, and finally the join to t3.

Moving the predicates from the WHERE clause to the ON clause wasn't for a performance gain... it was to keep the predicates on each table grouped together in the query, as an aid to the future reader.

I see the query as starting as a query against the t1 (uut_result) table

SELECT t1.uut_name
     , t1.station_num
     , t1.start_date_time

--      , t3.low_limit
--      , t3.data
--      , t3.high_limit
--      , t3.units
--      , t2b.step_name

  FROM uut_result t1

--   JOIN step_result t2b
--     ON t2b.uut_result     = t1.id
--    AND t2b.step_type      = 'constant'
--    AND t2b.step_name      = 'variable3' 

--   JOIN step_result t2a
--     ON t2a.step_parent    = t2b.step_id

--   JOIN meas_numericlimit t3
--     ON t3.id = t2a.step_id

 WHERE t1.station_num     = 'variable2'
   AND t1.uut_name     LIKE 'Variable1-1%'

And then un-commenting the lines that reference t2b ...

Dems_53341 · Answer

First thing to note is that just because you write the joins in one order doesn't mean that they actually get executed in that order. (Look up declarative languages.)

For this reason I would start by creating indexes that would satisfy each where clause, then each join predicate, but in both directions...

From the WHERE clause, all compound/covering indexes should start with...

STEP_RESULT : (STEP_NAME, STEP_TYPE)
uut_result : (STATION_NUM, UUT_NAME)

Also accounting for the JOIN predicates, all compound/covering indexes should then be...

meas_numericlimit : (ID)
STEP_RESULT : (STEP_NAME, STEP_TYPE, STEP_ID)
STEP_RESULT : (STEP_NAME, STEP_TYPE, STEP_PARENT)
STEP_RESULT : (STEP_NAME, STEP_TYPE, UUT_RESULT)
uut_result : (STATION_NUM, UUT_NAME, ID)

Of those 5 indexes, you will likely only see four in use, so you may want to drop the one not in use, or keep it in case a change in statistics changes the explain plan.

This does depend somewhat on the nature of your data. For example, you may just want the index on uut_result to be "reversed" to be (ID, STATION_NUM, UUT_NAME). Without knowing anything about the behaviour of your data, it may be worth trying both. (The same applies to the other index suggestions.)

Optimizing SQL query with inner join on 3 big tables

2 Answers