Cara optimize query JOIN 2 Table

[akbar maulana]

# Table name: entities

#  id                     :integer          not null, primary key

# Table name: risks

#  id         :integer          not null, primary key

#  entity_id  :integer

#  observable :string

    t.index ["entity_id"], name: "index_risks_on_entity_id"

    t.index ["id"], name: "index_risks_on_id", unique: true

    t.index ["observable", "entity_id"], name: "index_risks_on_observable_and_entity_id"

# Table name: transactions

#  id                         :integer          not null, primary key

#  originator_id              :integer

#  beneficiary_id             :integer

    t.index ["beneficiary_id"], name: "index_transactions_on_beneficiary_id"

    t.index ["originator_id"], name: "index_transactions_on_originator_id"

query saya seperti ini :

SELECT * FROM entities INNER JOIN transactions ON transactions.originator_id = entities.id OR transactions.beneficiary_id = entities.id INNER JOIN risks ON risks.entity_id = entities.id AND risks.observable = 'segmentation'

query diatas membutuhkan waktu 7s-10s

para master sql bagaimana query paling optimal...salam advance

[Natali Ardianto]

Coba jalankan ini dan paste disini:

EXPLAIN EXTENDED SELECT * FROM entities INNER JOIN transactions ON transactions.originator_id = entities.id OR transactions.beneficiary_id = entities.id INNER JOIN risks ON risks.entity_id = entities.id AND risks.observable = 'segmentation'

Atau dump DDL nya kesini (dump query struktur)

SHOW CREATE TABLE entities;
SHOW CREATE TABLE risks;

SHOW CREATE TABLE transactions;

[akbar maulana]

btw saya pakai rails, kalau di explain hasilnya ini :

 

Gather  (cost=258675.86..823834.23 rows=142148 width=388)

   Workers Planned: 2

   ->  Merge Join  (cost=257675.86..808619.43 rows=59228 width=388)

         Merge Cond: (entities.id = risks.entity_id)

         ->  Nested Loop  (cost=1.29..1672403.86 rows=166675 width=388)

               ->  Parallel Index Scan using index_entities_on_id on entities  (cost=0.42..35122.66 rows=333356 width=388)

               ->  Bitmap Heap Scan on transactions  (cost=0.86..4.89 rows=2 width=8)

                     Recheck Cond: ((originator_id = entities.id) OR (beneficiary_id = entities.id))

                     ->  BitmapOr  (cost=0.86..0.86 rows=2 width=0)

                           ->  Bitmap Index Scan on index_transactions_on_originator_id  (cost=0.00..0.43 rows=1 width=0)

                                 Index Cond: (originator_id = entities.id)

                           ->  Bitmap Index Scan on index_transactions_on_beneficiary_id  (cost=0.00..0.43 rows=1 width=0)

                                 Index Cond: (beneficiary_id = entities.id)

         ->  Materialize  (cost=257654.50..259076.01 rows=284301 width=4)

               ->  Sort  (cost=257654.50..258365.25 rows=284301 width=4)

                     Sort Key: risks.entity_id

                     ->  Bitmap Heap Scan on risks  (cost=7771.89..228012.51 rows=284301 width=4)

                           Recheck Cond: ((observable)::text = 'segmentation'::text)

                           ->  Bitmap Index Scan on index_risks_on_observable_and_entity_id  (cost=0.00..7700.82 rows=284301 width=0)

                                 Index Cond: ((observable)::text = 'segmentation'::text)

[Natali Ardianto]

Heumm doesn't help. Pakai native MySQL saja.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	entities	index	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index
1	SIMPLE	transactions	ALL	originator_id,beneficiary_id	NULL	NULL	NULL	1	100.00	Range checked for each record (index map: 0x6)
1	SIMPLE	risks	ref	entity_id,observable	observable	24	const,test.entities.id	1	100.00	Using where; Using index

Kelihatan kl index di table transactions tidak dipakai (type ALL).

Saya coba force index dan query nya saya pisah dan satukan pakai UNION karena index per table hanya bisa dipanggil sekali. Dan karena saya tidak bisa melihat isi datanya, jadi saya pakai asumsi ini (bisa juga join table dua kali).

explain EXTENDED 

SELECT * 

FROM entities 

INNER JOIN transactions force INDEX for join (originator_id) ON transactions.originator_id = entities.id 

INNER JOIN risks ON risks.entity_id =entities.id AND risks.observable = 'segmentation' 

UNION 

SELECT * FROM entities 

INNER JOIN transactions force INDEX for join (beneficiary_id) ON transactions.beneficiary_id =entities.id 

INNER JOIN risks ON risks.entity_id = entities.id AND risks.observable = 'segmentation'  

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	entities	index	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index
1	PRIMARY	transactions	ref	originator_id	originator_id	4	test.entities.id	1	100.00
1	PRIMARY	risks	ref	entity_id,observable	observable	24	const,test.entities.id	1	100.00	Using where; Using index
2	UNION	entities	index	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index
2	UNION	transactions	ref	beneficiary_id	beneficiary_id	4	test.entities.id	1	100.00
2	UNION	risks	ref	entity_id,observable	observable	24	const,test.entities.id	1	100.00	Using where; Using index
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL

--
Untuk memposting, kirim email ke mysql-i...@googlegroups.com |
Untuk unsubscribe, kirim email ke mysql-indones...@googlegroups.com | Arsip milis : https://groups.google.com/d/forum/mysql-indonesia?hl=id
---
Anda menerima pesan ini karena berlangganan grup "MySQL Indonesia" di Google Grup.
Untuk berhenti berlangganan dan berhenti menerima email dari grup ini, kirim email ke mysql-indones...@googlegroups.com.
Untuk opsi lebih lanjut, kunjungi https://groups.google.com/d/optout.

[akbar maulana]

betul sekali, untuk index di transactions tidak kepakai karena saya pakai OR Clause, saya juga sudah mencoba menggunakan UNION kemarin namun muncul error seperti ini (begitu juga suggest query mas ardi)

`PG::DuplicateAlias: ERROR:  table name "transactions" specified more than once`, saya tahu persis kenapa muncul itu. karena terjadi join 2x ke transactions yang tidak di ALIAS dan setelah di kasih AS jadi begini :

SELECT * 

FROM entities 

INNER JOIN transactions AS orig ON orig.originator_id = entities.id 

INNER JOIN risks ON risks.entity_id =entities.id AND risks.observable = 'segmentation' 

UNION 

SELECT * FROM entities 

INNER JOIN transactions As ben ON ben.beneficiary_id = entities.id 

INNER JOIN risks ON risks.entity_id = entities.id AND risks.observable = 'segmentation'

untuk query diatas, index di beneficiary dan originator kepakai, kecepatan lumayan ningkat namun saya belum dapat mengimplementasikan di rails, karena .union di activerecord rails itu tidak ada T_T

[akbar maulana]

saya sudah ketemu gemnya untuk union di active record, dengan query diatas hasilnya menjadi 8s - 10s, mungkin memang sudah tidak bisa di optimize lagi ya

[Natali Ardianto]

Bro, you are missing this:

FORCE INDEX FOR JOIN (originator_id)   

Pakai ini di rails:

query = <<-SQL

SELECT * 

FROM entities 

INNER JOIN transactions force INDEX for join (originator_id) ON transactions.originator_id = entities.id 

INNER JOIN risks ON risks.entity_id =entities.id AND risks.observable = 'segmentation' 

UNION 

SELECT * FROM entities 

INNER JOIN transactions force INDEX for join (beneficiary_id) ON transactions.beneficiary_id =entities.id 

INNER JOIN risks ON risks.entity_id = entities.id AND risks.observable = 'segmentation'  

SQL 

result = ActiveRecord::Base.connection.execute(query)

Sedangkan kalau query elo:

SELECT * 

FROM entities 

INNER JOIN transactions AS orig ON orig.originator_id = entities.id 

INNER JOIN risks ON risks.entity_id =entities.id AND risks.observable = 'segmentation' 

UNION 

SELECT * FROM entities 

INNER JOIN transactions As ben ON ben.beneficiary_id = entities.id 

INNER JOIN risks ON risks.entity_id = entities.id AND risks.observable = 'segmentation'

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1

PRIMARY

entities

index

PRIMARY

PRIMARY

4

NULL

1

100.00

Using index

1

PRIMARY

orig

ALL

originator_id

NULL

NULL

NULL

1

100.00

Using where; Using join buffer (flat, BNL join)

1	PRIMARY	risks	ref	entity_id,observable	observable	24	const,test.entities.id	1	100.00	Using where; Using index
2	UNION	entities	index	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index

2

UNION

ben

ALL

beneficiary_id

NULL

NULL

NULL

1

100.00

Using where; Using join buffer (flat, BNL join)

2	UNION	risks	ref	entity_id,observable	observable	24	const,test.entities.id	1	100.00	Using where; Using index
NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	NULL

Bisa di lihat di baris kedua dan baris kelima, type ALL, itu full table scan. Sedangkan query saya semua pakai index.