How to speed up loading data from MySQL query with multiple joins

Question

When I run this query it only displays loading that is not finished. So the time to run the query has run out without displaying any data. In fact, making computer operations become very slow.

Here is the problem. I am selecting and doing multiple joins to get the correct items... This query takes so long time. Anyone know how I can speed this up? Here is the query.

SELECT a.*, 
LEFT(a.tgl_inovasi, 10) AS tgl_ino, b.`nm_unit`, c.`nm_inovasijns`, 
d.`nm_regulasi`, e.`nm_sediasdm`, f.`nm_anggaran`, g.`nm_it`, h.`nm_bimtek`, 
i.`nm_renstra`, j.`nm_jejaring`, k.`nm_replikasi`, l.`nm_pedoman`, 
m.`nm_pengelola`, n.`nm_informasi`, o.`nm_penyelesaian`, p.`nm_partisipasi`, 
q.`nm_kemudahaninf`, r.`nm_kemudahanpro`, s.`nm_online`, t.`nm_kecepatan`, 
u.`nm_kemanfaatan`, v.`nm_kepuasan`, w.`nm_user`, x.nm_inovasikat 
FROM inovasi a, unit b, inovasijns c, regulasi d, sediasdm e, anggaran f, 
it g, bimtek h, renstra i, jejaring j, replikasi k, pedoman l, pengelola m, 
informasi n, penyelesaian o, partisipasi p, kemudahaninf q, kemudahanpro r, 
online s, kecepatan t, kemanfaatan u, kepuasan v, `user` w, inovasikat x 
WHERE x.id_inovasikat = a.id_inovasikat AND w.`id_user` = a.`id_user` AND 
v.`id_kepuasan` = a.`id_kepuasan` AND u.`id_kemanfaatan` = a.`id_kemanfaatan` AND 
t.`id_kecepatan` = a.`id_kecepatan` AND s.`id_online` = a.`id_online` AND 
r.`id_kemudahanpro` = a.`id_kemudahanpro` AND q.`id_kemudahaninf` = 
a.`id_kemudahaninf` AND p.`id_partisipasi` = a.`id_partisipasi` AND 
o.`id_penyelesaian` = a.`id_penyelesaian` AND n.`id_informasi` = a.`id_informasi` 
AND m.`id_pengelola` = a.`id_pengelola` AND l.`id_pedoman` = a.`id_pedoman` AND 
k.`id_replikasi` = a.`id_replikasi` AND j.`id_jejaring` = a.`id_jejaring` AND 
i.`id_renstra` = a.`id_renstra` AND h.`id_bimtek` = a.`id_bimtek` AND g.`id_it` = 
a.`id_it` AND f.`id_anggaran` = a.`id_anggaran` AND e.`id_sediasdm` = 
a.`id_sediasdm` AND d.`id_regulasi` = a.`id_regulasi` AND c.`id_inovasijns` = 
a.`id_inovasijns` AND b.`id_unit` = a.`id_unit` AND a.no_inovasi = '$no_inovasi'

I try looking for the solution from other threat, almost all of them suggest using an index. But I don't understand which tables and fields should be added to the index. Please help to solve this problem.

I already run this query :

CREATE INDEX idx_inovasi1 ON inovasi (no_inovasi, tgl_inovasi, satuan, id_unit, nm_kelompok, subjek, id_inovasijns, id_inovasikat, id_regulasi, regulasi_doc, id_sediasdm, sediasdm_doc);

but it still hasn't affected anything.

Answer 1

you should use indexes, check how to index fields, but above all index the fields you have after the "where"

Answer 2

If you add the results of the EXPLAIN (your SQL), it would be helpful to pinpoint issues. https://dev.mysql.com/doc/refman/8.0/en/explain.html

Answer 3

How about switching to left join's, using the modern JOIN syntax.

SELECT a.*
, LEFT(a.tgl_inovasi, 10) AS tgl_ino
, b.`nm_unit`
, c.`nm_inovasijns`
, d.`nm_regulasi`
, e.`nm_sediasdm`
, f.`nm_anggaran`
, g.`nm_it`
, h.`nm_bimtek`
, i.`nm_renstra`
, j.`nm_jejaring`
, k.`nm_replikasi`
, l.`nm_pedoman`
, m.`nm_pengelola`
, n.`nm_informasi`
, o.`nm_penyelesaian`
, p.`nm_partisipasi`
, q.`nm_kemudahaninf`
, r.`nm_kemudahanpro`
, s.`nm_online`
, t.`nm_kecepatan`
, u.`nm_kemanfaatan`
, v.`nm_kepuasan`
, w.`nm_user`
, x.nm_inovasikat 
FROM inovasi a
LEFT JOIN unit b ON b.`id_unit` = a.`id_unit` 
LEFT JOIN inovasijns c ON c.`id_inovasijns` = a.`id_inovasijns` 
LEFT JOIN regulasi d ON d.`id_regulasi` = a.`id_regulasi` 
LEFT JOIN sediasdm e ON e.`id_sediasdm` = a.`id_sediasdm` 
LEFT JOIN anggaran f ON f.`id_anggaran` = a.`id_anggaran` 
LEFT JOIN it g ON g.`id_it` = a.`id_it` 
LEFT JOIN bimtek h ON h.`id_bimtek` = a.`id_bimtek` 
LEFT JOIN renstra i ON i.`id_renstra` = a.`id_renstra` 
LEFT JOIN jejaring j ON j.`id_jejaring` = a.`id_jejaring` 
LEFT JOIN replikasi k ON k.`id_replikasi` = a.`id_replikasi` 
LEFT JOIN pedoman l ON l.`id_pedoman` = a.`id_pedoman` 
LEFT JOIN pengelola m ON m.`id_pengelola` = a.`id_pengelola` 
LEFT JOIN informasi n ON n.`id_informasi` = a.`id_informasi` 
LEFT JOIN penyelesaian o ON o.`id_penyelesaian` = a.`id_penyelesaian` 
LEFT JOIN partisipasi p ON p.`id_partisipasi` = a.`id_partisipasi` 
LEFT JOIN kemudahaninf q ON q.`id_kemudahaninf` = a.`id_kemudahaninf` 
LEFT JOIN kemudahanpro r ON r.`id_kemudahanpro` = a.`id_kemudahanpro` 
LEFT JOIN online s ON s.`id_online` = a.`id_online` 
LEFT JOIN kecepatan t ON t.`id_kecepatan` = a.`id_kecepatan` 
LEFT JOIN kemanfaatan u ON u.`id_kemanfaatan` = a.`id_kemanfaatan`
LEFT JOIN kepuasan v ON v.`id_kepuasan` = a.`id_kepuasan`
LEFT JOIN `user` w ON w.`id_user` = a.`id_user` 
LEFT JOIN inovasikat x ON x.id_inovasikat = a.id_inovasikat 
WHERE a.no_inovasi = '$no_inovasi'

As for the indexes. There's almost a whole alphabeth of tables linked.
Most are probably joined on the primary index.

So look at the defenition of those tables, and discover which tables don't have their PK joined.
Maybe those could benefit from an index on the field used in the query.