V
V
Vadim Stepanenko2020-03-14 12:53:19
MySQL
Vadim Stepanenko, 2020-03-14 12:53:19

Optimizing mysql queries?

There are the following tables:
matches: id, start_time, tournament_id
teams: id, name, logo
matches_teams: id, match_id, team_id
tournaments: id, name

Now I get complete information about matches as follows:
1. I get matches.id, matches with one request. start_time, tournaments.name
2. Then I make two more queries for each match from p1 - for the first team and for the second one.

As a result, I get something like this array:
match_id
match_start_time
tournament_name
team_A_name
team_A_logo
team_B_name
team_B_logo

And it turns out that 60 requests are made to get 20 matches (to get information for just one page).
Based on this, there are several questions
1. How can queries be optimized?
2. which is better, several simple requests or one complex one?
3. Is it possible to get nested arrays using mysql? so that there is no $result['team_A_name'], $result['team_A_logo'], but $result['team_A']['name'], $result['team_A']['logo'] (php). Now I'm doing this with php, "configuring" a new array with a nested structure

Answer the question

In order to leave comments, you need to log in

2 answer(s)
K
Kirill Nesmeyanov, 2020-03-14
@SerafimArts

1) JOIN
2) WHERE IN

T
ThunderCat, 2020-03-14
@ThunderCat

1) as Kirill Nesmeyanov already pointed out to you - read about join, in and nested queries, the task is just a classic one for join and in.
2) John Didact was absolutely right - you ALWAYS get "flat" data from the database, but you can separate them for yourself by prefixes in aliases and process them on the code side as convenient.
3) A large query with many joins is almost always better than a few small ones. An exception is large arrays of text / blob fields, but this is usually not critical if adequate limits are set. At a minimum, the advantage is the possibility of convenient caching. Plus, the time to open / close the connection, plus you don’t have to drive the received data back and forth several times.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question