MYSQL best use of indexes for a JOIN query

Question

I have two tables

homes_info with 4 columns

 home_id (primary_key)  
 title (varchar)
 price (double)
 location_id (foreign key)

And I have another table

locations with 3 columns

 location_id (primary key)
 location_name (varchar)
 location_state (varchar)

I just want to make these two queries

SELECT * FROM homes_info WHERE title = 'xxxxx' && price = 'xxxx'

SELECT * FROM homes_info LEFT JOIN locations on homes_info.location_id = locations.location_id WHERE title = 'xxx' && price = 'xxxxx' && location_name = 'xxxx'

are the following the best way to use indexes on above tables for optimizing above two queries?

ALTER TABLE homes_info ADD INDEX(title, price)

ALTER TABLE locations ADD INDEX(location_name)

I know for the first query the composite index INDEX(title, price) is better than separate indexes, But what about the second query (where I used LEFT JOIN)? would using separate indexes on title and price columns be better for the second query

In simple words, what would be the best use of indexes on those two tables for optimizing above two queries?

Thanks!

`ALTER TABLE locations ADD INDEX(location_name)` (I think you mean `locations` table instead of `home_info`) — Madhur Bhaiya, Sep 07 '18 at 14:06
@user9050678, Just FYI, The thumb rule is to create an index on all the columns included in Where clause and in Join condition. Since you have to use 2 different queries so I would suggest that create a composite index on (title, price) and a different index on location_name. Location_id already had an index being the primary key for locations table. — Ankit Bajpai, Sep 07 '18 at 14:11
Maybe not exactly what you're looking for, but there are many SO answers on whether the order of an index matters and how. Like Ankit said, your index and where/on should have the same fields; this explains a bit more, if you want to know finer differences -- https://stackoverflow.com/a/24315519/2430549 — HoldOffHunger, Sep 07 '18 at 14:38
@AnkitBajpai Thanks, I thought the same but the scaisEdge answer says differently, i will do some more research myself, and update that here, Thanks — user9050678, Sep 07 '18 at 14:45

ScaisEdge · Accepted Answer · 2018-09-07T14:15:34.497

1

for table location you could use y .. you could use single composite

ALTER TABLE locations ADD locations_info INDEX(location_name , localtion_id)

using a composite with localtion_name and location_id) you are sure that all the infor needed by the where (join ) clause are resolved by the index, avoiding the acces to the data table for get .. the location_id

edited Sep 07 '18 at 14:15

answered Sep 07 '18 at 14:07

ScaisEdge

131,976
10
91
107

hi, sorry, there was a mistake in question, `ALTER TABLE locations ADD INDEX(location_name)` instead of `ALTER TABLE homes_info ADD INDEX(location_name)` – user9050678 Sep 07 '18 at 14:08
update your question .. so i can answer properly .. (happen) – ScaisEdge Sep 07 '18 at 14:09
hi, thanks, but I am still confused, why we need to use an index on `location_id`, it is a primary key so already indexed? and what about first table, is using composite index ok on first table's columns ?could you explain little bit more pls – user9050678 Sep 07 '18 at 14:15
simple .. using composite index .. both the info for where are in the same index .. so with a single access you have both the info .. and query optimizer can use just one index for a table in a query .. in your case the query optimizer use the index with primary key but this index have not the info for name so this part of info must be retrived with an acces to the data table ... hope is clear – ScaisEdge Sep 07 '18 at 14:18
Thanks for your explanation, so for this query `SELECT * FROM homes_info LEFT JOIN locations on homes_info.location_id = locations.location_id WHERE title = 'xxx' && price = 'xxxxx' && location_name = 'xxxx'` The best use of indexes would be `ALTER TABLE homes_info ADD INDEX(title, price)` and `ALTER TABLE locations ADD INDEX(location_name, location_id)` so actually we don't need to worry about the JOIN, we should think how should we use indexes on the two tables for best performance (separately) considering which column from that table we are using in join query – user9050678 Sep 07 '18 at 14:31
Oh, but then we should use this index on first table `ALTER TABLE homes_info ADD INDEX(title, price, location_id)`, i am very confused now, could you pls shed some light on this – user9050678 Sep 07 '18 at 14:33
i have not (more) then index (title, price, location_id) .. is unuseful for the first query and unuseful for the second query ... you need INDEX(title, price) for the first query and I NDEX(location_name , localtion_id) for the second query .. the second query using two table .. use both the index .. – ScaisEdge Sep 07 '18 at 15:45

MYSQL best use of indexes for a JOIN query

1 Answers1