There are 5 tables:

1) games:

g_id | country | league | tour | team1 | team2 | и еще 10

2) events:

 e_id | name | eTime | g_id |

3) In general, everyone has a different number of fields, I try to take all the data (all fields) from all 5 tables by condition, for example:

 SELECT * FROM games INNER JOIN events ON games.g_id = events.g_id INNER JOIN stats ON games.g_id = stats.g_id INNER JOIN lineups ON games.g_id = lineups.g_id INNER JOIN ads ON games.g_id = ads.g_id WHERE games.team1 = 'Арсенал'

As a result, the request in phpmyadmin hangs (on the plate says Loading ... and everything hangs). I tried the same query with a smaller number of tables with 2-3 data loaded but the “Loading” label also hangs for a long time, although after the written request it took 0.1 seconds. Simple requests run quickly (Select * from games)

Is this a valid query, can you somehow get all the data differently? Please tell me who is not hard ...

  • how many entries do you have for each game in other tables? I suppose that not one, you end up with a Cartesian product ... if so, then you need to use union, or make 4 requests ... in general , give an example of data - Yura Ivanov

2 answers 2

 CREATE VIEW all_tables AS SELECT * FROM games INNER JOIN events ON games.g_id = events.g_id INNER JOIN stats ON games.g_id = stats.g_id INNER JOIN lineups ON games.g_id = lineups.g_id INNER JOIN ads ON games.g_id = ads.g_id

Then

 SELECT * FROM all_tables WHERE team1 = 'Арсенал' LIMIT 30
  • If phpmyadmin hangs, try to make requests through the terminal (console) if on LAN, the delay may be in the transfer of information. - Sergey Derevyanko
  • The presentation here is unlikely to help, you can try to explicitly specify ALGORITHM = TEMPTABLE, but there are doubts. - Vadim Kharitonov

Due to the fact that only one table participates in WHERE, you can try using a subquery, and then JOIN-it already received a sample

 SELECT * FROM (SELECT * FROM games WHERE games.team1 = 'Арсенал') AS A INNER JOIN events ON A.g_id = events.g_id INNER JOIN stats ON A.g_id = stats.g_id INNER JOIN lineups ON A.g_id = lineups.g_id INNER JOIN ads ON A.g_id = ads.g_id WHERE A.team1 = 'Арсенал'

But here the problem is rather with phpmyadmin itself. Try through the console, as already advised above.