Join multiple tables without overhead on the DB Server using PDO I have multiple tables called light_readings, temp_readings, co2_readings and humidity_readings.

The column format of the tables are as below:


light_readings:

Id, light_value, unitid, date_time

temp_readings:

Id, temp_value, unitid, date_time

co2_readings:

Id, co2_value, unitid, date_time

humidity_readings:

Id, humidity_value, unitid, date_time



I am using PDO to fetch these values to my webapp, but retrieving these using multiple SQL queries is putting too much load on my DB server. I need to get the latest readings of all the tables in just one row if possible.

I tried using UNION, but even that takes up some load on the server

(SELECT temp_value FROM temp_readings where unitid='SS0007' order by Id desc limit 1)
UNION ALL
(SELECT humid_value FROM humid_readings where unitid='SS0007' order by Id desc limit 1)
UNION ALL
(SELECT co2_value FROM co2_readings where unitid='SS0007' order by Id desc limit 1)
UNION ALL
(SELECT light_value FROM light_readings where unitid='SS0007' order by Id desc limit 1)


Could someone assist me with this?
You need to rethink the schema...

Plan A:  Have temp, humid, etc, as columns in a single table.  If the date_time is the same from all the readings, then that does not need repeating.

Plan B:  Have those values in a JSON string in a single column in a single table.

Plan C:  Have a 3-column table:  (unitid, reading_name, value) and fetch the 4 values from 4 rows, but in a single SELECT.

In any case, Id is probably not needed.  The PRIMARY KEY can be (or start with) (unitid, date_time)