{"id":818,"date":"2022-04-08T10:14:32","date_gmt":"2022-04-08T03:14:32","guid":{"rendered":"https:\/\/www.bagi2info.com\/?p=818"},"modified":"2023-03-05T13:54:39","modified_gmt":"2023-03-05T06:54:39","slug":"tips-tips-sql","status":"publish","type":"post","link":"https:\/\/www.bagi2info.com\/en\/sql-tips-trick\/","title":{"rendered":"SQL query optimization tips with index, subquery, and cross join"},"content":{"rendered":"

\"\"<\/a><\/p>\n

1. Use numeric data type as primary key or foreign key<\/p>\n

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.<\/p>\n

The following example is a student table for course grades<\/p>\n

tb_mhs <\/strong>
\nid int auto increment primary key
\nnik varchar(50)
\nnama varchar(250)
\ndob datetime
\ngender varchat(1)<\/p><\/blockquote>\n

tb_matakuliah<\/strong>
\nid  int auto increment primary key
\nketerangan varchar(250)
\nsks int<\/p><\/blockquote>\n

tb_nilai<\/strong>
\nno  int auto increment primary key
\nid_mhs int
\nid_matakuliah int
\nnilai float<\/p><\/blockquote>\n

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.<\/p>\n

2. Subquery function to shorten the data retrieval process<\/h2>\n

Contoh<\/p>\n

SELECT m.id, m.nik, m.nama, m.dob, m.gender
\nFROM tb_mhs m
\nJOIN tb_nilai n ON m.id = n.id_mhs
\nJOIN tb_matakuliah mk ON n.id_matakuliah = mk.id<\/p><\/blockquote>\n

By using subquery we can shorten data retrieval on join process with tb_value,
\ninstead of using join and scanning the entire table on tb_value, we can use
\nsubquery by summarizing scan data<\/p>\n

SELECT m.id, m.nik, m.nama, m.dob, m.gender
\nFROM tb_mhs m
\nJOIN (SELECT * FROM tb_nilai WHERE id_mhs = 1) n ON m.id = n.id_mhs
\nJOIN tb_matakuliah mk ON n.id_matakuliah = mk.id<\/p><\/blockquote>\n

Note: the example query above can be done with a note that we only filter for one or several students.<\/p>\n

3. Update function by joining<\/p>\n

Sometimes we want to update by emptying the value in the table tb_value with the name of a certain student,
\nthen we can use update command with where link.<\/p>\n

UPDATE tb_nilai n, tb_mhs m
\nSET n.nilai =0
\nWHERE n.id_mhs = m.id
\nAND m.id IN (1, 2, 3, 4)<\/p><\/blockquote>\n

4. Rarely used cross join function<\/p>\n

The tb_value table is a liaison table between the tb_mhs and tb_matalaku tables,
\nand we can generate table tb_value by using cross join command<\/p>\n

INSERT INTO tb_nilai (id_mhs, id_matakuliah)
\nSELECT m.id, mk.id
\nFROM tb_mhs m
\nCROSS JOIN tb_matakuliah mk<\/p><\/blockquote>\n

In retail applications, cross joins are commonly used by authors to generate stock tables which are:
\nlink between product table and warehouse table<\/p>\n

INSERT INTO tb_stok (id_produk, id_gudang)
\nSELECT m.id, g.id
\nFROM tb_produk m
\nCROSS JOIN tb_gudang g<\/p><\/blockquote>\n

5. Put the index in the right position<\/p>\n

Over time, the data will continue to grow, so it is necessary to put an index on the column that
\noften used for search or join linking keys.
\nRemember redundant indexes will slow down performance, use indexes sparingly.
\nWith the example table above, the index needs to be placed on the tb_value for the id_mhs column and also the id_course.
\nUsually an automatic index will be created for the column with the primary key attribute.<\/p>\n","protected":false},"excerpt":{"rendered":"

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...<\/p>\n","protected":false},"author":1,"featured_media":1032,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,2,14],"tags":[11,7],"class_list":["post-818","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-komputer","category-recommended","tag-database","tag-sql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/818","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/comments?post=818"}],"version-history":[{"count":1,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/818\/revisions"}],"predecessor-version":[{"id":4126,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/818\/revisions\/4126"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/media\/1032"}],"wp:attachment":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/media?parent=818"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/categories?post=818"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/tags?post=818"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}