MySQL performance issue on a simple two tables joined Query

Question

I'm facing a performance issue on MySQL and I'm unable to understand where I'm wrong. The machine runs MySQLServer 5.7.15 with two Xeon 64bit Processors and 8GBytes of RAM. I've got two tables:

Table data_raw contains several fields (see VRMS0,VRMS1,VRMS2,PWRA0,PWRA1,PWRA2)

describing the voltages and active powers acquired from complicated instrumentation every 30 seconds from several probes on the field, each probe is uniquely identified by its DEVICE_ID.

Table data_timeslot contains few fields and is used to keep trace of when the single data_raw record was sent (see SRV_TIMESTAMP field)

and from which device (see DEVICE_ID field).

Each table contains about 7.800.000 records. The two tables are joined using a PK on ID (auto-increment) on data_timeslot and a PK on TIMESLOT_ID (auto-increment) on data_timeslot. Here is the query:

 SELECT D.VRMS0,D.VRMS1,D.VRMS2,D.PWRA0,D.PWRA1,D.PWRA2,T.DEVICE_ID, T.SRV_TIMESTAMP 
 FROM data_raw AS D FORCE INDEX(PRIMARY) 
 INNER JOIN data_timeslots AS T ON T.ID=D.TIMESLOT_ID 
 WHERE T.DEVICE_ID='CEC02' 
 ORDER BY T.ID DESC LIMIT 1

The query takes always 10 seconds while the same query on a single table takes few milliseconds. In other words the query

SELECT * FROM 'data_raw' order by TIMESLOT_ID desc limit 1

takes just 0.0071 sec and the query

SELECT * FROM 'data_timeslots' order by ID desc limit 1

takes just 0.0042 sec so I'm wondering why the join takes so long.

Where is the bottleneck?

P.S. The 'extend' shows that the DB is using properly the PK for the operation. Below the extend printout:

`EXPLAIN SELECT D.VRMS0,D.VRMS1,D.VRMS2,D.PWRA0,D.PWRA1,D.PWRA2,T.DEVICE_ID, T.SRV_TIMESTAMP FROM data_raw AS D INNER JOIN data_timeslots AS T ON T.ID=D.TIMESLOT_ID WHERE T.DEVICE_ID='XXXXX' ORDER BY T.ID ASC LIMIT 1

1 SIMPLE T index PRIMARY,PK_CLUSTER_T,DEVICE_ID PRIMARY 8 30 3.23 Using where

1 SIMPLE D eq_ref PRIMARY PRIMARY 8 splc_smartpwr.T.ID 1 100.00 NULL`

UPDATE (suggested by @Alberto_Delgado_Roda): if I use ASC LIMIT 1 the query takes just 0,0261 sec

You need an index on DEVICE_ID (and timeslot_id, but that you are already using). — Solarflare
Please include an explain plan for the 2 table query. What columns are indexed in each table? Why are you forcing index primary? What happens if you don't do that? — Paul Maxwell
@Used_By_Already : FORCE INDEX was just an attempt to solve the issue. If I don't use it the result is the same (as the Primary Key was already used). Above the explain text result — Power Engineering
@Solarflare: on data_timestamps there are already indexes on ID (PKey), on DEVICE_ID, on SRV_TIMESTAMP and a covering index on both DEVICE_ID+SRV_TIMESTAMP (Cluster Index). On data_raw the only possible index is in TIMESLOT_ID — Power Engineering

Paul Maxwell Paul Maxwell · Accepted Answer · 2016-10-03T08:38:15

Reply to "why"

Data_timeslots has a clusteted index that suits the ascending order

How the Clustered Index Speeds Up Queries

Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records.)

See https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

MySQL performance issue on a simple two tables joined Query

3 Answers