Mysql JOIN query apparently slow

Question

I have 2 tables. The first, called stazioni, where I store live weather data from some weather station, and the second called archivio2, where are stored archived day data. The two tables have in common the ID station data (ID on stazioni, IDStazione on archvio2).

stazioni (1,743 rows)

CREATE TABLE `stazioni` (
  `ID` int(10) NOT NULL,
  `user` varchar(100) NOT NULL,
  `nome` varchar(100) NOT NULL,
  `email` varchar(50) NOT NULL,
  `localita` varchar(100) NOT NULL,
  `provincia` varchar(50) NOT NULL,
  `regione` varchar(50) NOT NULL,
  `altitudine` int(10) NOT NULL,
  `stazione` varchar(100) NOT NULL,
  `schermo` varchar(50) NOT NULL,
  `installazione` varchar(50) NOT NULL,
  `ubicazione` varchar(50) NOT NULL,
  `immagine` varchar(100) NOT NULL,
  `lat` double NOT NULL,
  `longi` double NOT NULL,
  `file` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `temperatura` decimal(10,1) DEFAULT NULL,
  `umidita` decimal(10,1) DEFAULT NULL,
  `pressione` decimal(10,1) DEFAULT NULL,
  `vento` decimal(10,1) DEFAULT NULL,
  `vento_direzione` decimal(10,1) DEFAULT NULL,
  `raffica` decimal(10,1) DEFAULT NULL,
  `pioggia` decimal(10,1) DEFAULT NULL,
  `rate` decimal(10,1) DEFAULT NULL,
  `minima` decimal(10,1) DEFAULT NULL,
  `massima` decimal(10,1) DEFAULT NULL,
  `orario` varchar(16) DEFAULT NULL,
  `online` int(1) NOT NULL DEFAULT '0',
  `tipo` int(1) NOT NULL DEFAULT '0',
  `webcam` varchar(255) DEFAULT NULL,
  `webcam2` varchar(255) DEFAULT NULL,
  `condizioni` varchar(255) DEFAULT NULL,
  `Data2` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

archivio2 (2,127,347 rows)

CREATE TABLE `archivio2` (
  `ID` int(10) NOT NULL,
  `IDStazione` int(4) NOT NULL DEFAULT '0',
  `localita` varchar(100) NOT NULL,
  `temp_media` decimal(10,1) DEFAULT NULL,
  `temp_minima` decimal(10,1) DEFAULT NULL,
  `temp_massima` decimal(10,1) DEFAULT NULL,
  `pioggia` decimal(10,1) DEFAULT NULL,
  `pressione` decimal(10,1) DEFAULT NULL,
  `vento` decimal(10,1) DEFAULT NULL,
  `raffica` decimal(10,1) DEFAULT NULL,
  `records` int(10) DEFAULT NULL,
  `Data2` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The indexes that I set

-- Indexes for table `archivio2`
--
ALTER TABLE `archivio2`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `IDStazione` (`IDStazione`),
  ADD KEY `Data2` (`Data2`);

-- Indexes for table `stazioni`
--
ALTER TABLE `stazioni`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `Tipo` (`Tipo`);
ALTER TABLE `stazioni` ADD FULLTEXT KEY `localita` (`localita`);

On a map, I call by a calendar the date to search data on archive2 table, by this INNER JOIN query (I put an example date):

SELECT *, c.pioggia AS rain, c.raffica AS raff, c.vento AS wind, c.pressione AS press
FROM stazioni as o
INNER JOIN archivio2 as c ON o.ID = c.IDStazione
WHERE c.Data2 LIKE '2019-01-01%'

All works fine, but the time needed to show result are really slow (4/5 seconds), even if the query execution time seems to be ok (about 0.5s/1.0s). I tried to execute the query on PHPMyadmin, and the results are the same. Execution time quickly, but time to show result extremely slow.

EXPLAIN query result

id  select_type table   type    possible_keys       key         key_len ref                 rows    Extra
1   SIMPLE      o       ALL     PRIMARY,ID          NULL        NULL    NULL                1743    NULL    
1   SIMPLE      c       ref     IDStazione,Data2    IDStazione  4       sccavzuq_rete.o.ID  1141    Using where 

---

UPDATE: the query goes fine if I remove the index from 'IDStazione'. But in this way I lost all advantages and speed on other queries... why only that query become slow if I put index on that field?

Answer 1

In your WHERE clause

WHERE c.Data2 LIKE '2019-01-01%'

the value of Data2 must be casted to a string. No index can be used for that condition.

Change it to

WHERE c.Data2 >= '2019-01-01' AND c.Data2 < '2019-01-01' + INTERVAL 1 DAY

This way the engine should be able to use the index on (Data2).

Now check the EXPLAIN result. I would expect, that the table order is swapped and the key column will show Data2 (for c) and ID (for o).

Answer 2

(Fixing the DATE is the main performance solution; here is a less critical issue.)

The tables are much bigger than necessary. Size impacts disk space and, to some extent, speed.

You have 1743 stations, yet the datatype is a 32-bit (4-byte) number (INT). SMALLINT UNSIGNED would allow for 64K stations and use only 2 bytes.

Does it get really, really, hot there? Like 999999999.9 degrees? DECIMAL(10.1) takes 5 bytes; DECIMAL(4,1) takes only 3 and allows up to 999.9 degrees. DECIMAL(3,1) has a max of 99.9 and takes only 2 bytes.

What is "localita varchar(100)" doing in the big table? Seems like you could JOIN to the stations table when you need it? Removing that might cut the table size in half.