SQL query optimization tips with index, subquery, and cross join

1. Use numeric data type as primary key or foreign key

Often beginners will design tables using string types as keys, and this will result in future performance when the data is large, so it has implications for table changes and coding, and this is sometimes difficult because if the system has been implemented the database is already filled with data. data.

The following example is a student table for course grades

tb_mhs 
id int auto increment primary key
nik varchar(50)
nama varchar(250)
dob datetime
gender varchat(1)

tb_matakuliah
id  int auto increment primary key
keterangan varchar(250)
sks int

tb_nilai
no  int auto increment primary key
id_mhs int
id_matakuliah int
nilai float

Pay attention to the tb_value table which is a connecting relation from the tb_mhs and tb_matasiswa tables, the data type used is integer in the id_mhs and id_matasiswa columns.

2. Subquery function to shorten the data retrieval process

Contoh

SELECT m.id, m.nik, m.nama, m.dob, m.gender
FROM tb_mhs m
JOIN tb_nilai n ON m.id = n.id_mhs
JOIN tb_matakuliah mk ON n.id_matakuliah = mk.id

By using subquery we can shorten data retrieval on join process with tb_value,
instead of using join and scanning the entire table on tb_value, we can use
subquery by summarizing scan data

SELECT m.id, m.nik, m.nama, m.dob, m.gender
FROM tb_mhs m
JOIN (SELECT * FROM tb_nilai WHERE id_mhs = 1) n ON m.id = n.id_mhs
JOIN tb_matakuliah mk ON n.id_matakuliah = mk.id

Note: the example query above can be done with a note that we only filter for one or several students.

3. Update function by joining

Sometimes we want to update by emptying the value in the table tb_value with the name of a certain student,
then we can use update command with where link.

UPDATE tb_nilai n, tb_mhs m
SET n.nilai =0
WHERE n.id_mhs = m.id
AND m.id IN (1, 2, 3, 4)

4. Rarely used cross join function

The tb_value table is a liaison table between the tb_mhs and tb_matalaku tables,
and we can generate table tb_value by using cross join command

INSERT INTO tb_nilai (id_mhs, id_matakuliah)
SELECT m.id, mk.id
FROM tb_mhs m
CROSS JOIN tb_matakuliah mk

In retail applications, cross joins are commonly used by authors to generate stock tables which are:
link between product table and warehouse table

INSERT INTO tb_stok (id_produk, id_gudang)
SELECT m.id, g.id
FROM tb_produk m
CROSS JOIN tb_gudang g

5. Put the index in the right position

Over time, the data will continue to grow, so it is necessary to put an index on the column that
often used for search or join linking keys.
Remember redundant indexes will slow down performance, use indexes sparingly.
With the example table above, the index needs to be placed on the tb_value for the id_mhs column and also the id_course.
Usually an automatic index will be created for the column with the primary key attribute.