D
D
danforth2018-02-10 17:11:44
MySQL
danforth, 2018-02-10 17:11:44

How to optimize query with INNER JOIN and ORDER BY across multiple tables?

Hello everyone, there are tables:
Bank 4900 rows
Branch 89000 rows
City 29000 rows
State 51 rows
There is a request, in short the meaning is this:
Select bank branches with URL slug, sort by city name, branch name, leave the first 50 pieces.
It is done for pagination, since there are 5000 branches for this particular bank, and I don’t want to dump them all on the page.
The request code itself:

SELECT c.id, c.name, c.slug, s.id, s.name, s.slug, b.id, b.name, b.slug, b.location
FROM city c
INNER JOIN state s ON s.id = c.state_id
INNER JOIN branch b ON b.city_id = c.id
INNER JOIN bank bs ON b.bank_id = bs.id
WHERE bs.slug = 'some-bank'
ORDER BY c.name, b.name
LIMIT 50

His EXPLAIN:
"id"	"select_type"	"table"	"partitions"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"filtered"	"Extra"
"1"	"SIMPLE"	"bs"	\t	"ref"	"PRIMARY,slug"	"slug"	"767"	"const"	"1"	"100,00"	"Using index; Using temporary; Using filesort"
"1"	"SIMPLE"	"b"	\t	"ref"	"location_bank_fk_idx,location_city_fk_idx"	"location_bank_fk_idx"	"4"	"banks.bs.id"	"17"	"100,00"	\t
"1"	"SIMPLE"	"c"	\t	"eq_ref"	"PRIMARY,city_state_fk"	"PRIMARY"	"8"	"banks.b.city_id"	"1"	"100,00"	\t
"1"	"SIMPLE"	"s"	\t	"eq_ref"	"PRIMARY"	"PRIMARY"	"4"	"banks.c.state_id"	"1"	"100,00"	\t

The request itself takes about 0.3 seconds, which is too much for the site.
Please help me understand why this query is running slowly and how it can be speeded up.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Maxim Pavlyuchuk, 2018-02-15
@bort95

Perhaps it will help to create a view
https://habrahabr.ru/post/47031/
after doing
SELECT * FROM view_name WHERE slug = 'some-bank' LIMIT 50

B
bioroot, 2018-02-15
@bioroot

I have not used MySQL for a long time, but I will write from old memory. You have a lot of single indexes, and in your case this is no longer enough. You need to go at least double. For example, in the bank table, you are only interested in slug and id. But in the case of single indexes, your query will be processed something like this.
If you had a double index (slug, id), then the desired id would show up in the index store next to the slug. And further in the data it would not be required to climb at all.
In general, the next stage of optimization usually comes down to finding good combinations of multiple indexes. And do not forget that they are more expensive to build than single ones and the second and further value are not used if the first one is not in the request.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question