CODEWITHSUNDEEP
mysql
sandeep tiwari
sandeep tiwari

Reputation: 117

Benefit of creating index on both table in case of joining 2 tables

I have 2 tables Table1, Table2 with below schema.

Table1:

(PolicyHolderKey NUMERIC(24,6),Key NUMERIC(24,6))

: rows 10000

Table2:

(Key NUMERIC(24,6),City VARCHAR(255))

: rows 600

though original schema is complex, above is just a part of it.

Now what is benefit in terms of perfromance if i will create index on both table's column "KEY" and execute below query:

SQL:

SELECT table1.PolicyHolderKey,table1.Key,table2.City
FROM table1
INNER JOIN table2 ON table1.Key=table2.Key**

or if I will create index on one table which have less rows and execute same above query.

Upvotes: 2

Views: 579

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

As a first try effort, you can try creating an index on (Key, City) in the second table. Then MySQL would only have to do a full table scan of the first table, but then would be able to instantly lookup every key value and retrieve the two columns the select requests.

CREATE INDEX index_name ON table2 (Key, City);

Upvotes: 2

Related Questions