Why the request is Hanging and no response?

All terms have index.

SELECT u.timestamp ,u.login, app.id appId, a.id aId, b.id bId ah.id ahId, ah.date_time, ah.as_revealed, ah.user_id, ah.barrier_id
 FROM `user` u
 LEFT JOIN attribut_app app ON u.id = app.user_id
 LEFT JOIN brand b ON app.barrier_id = b.id AND b.manager_id IS NULL /*will not work if the object will appear */
 LEFT JOIN access a ON SUBSTRING(u.login,2) = SUBSTRING(a.access_number,2) AND deleted = '0000-00-00 00:00:00' /*get all possible accesses it according to our user */
 LEFT JOIN access_history ah ON DATE_SUB(NOW(), INTERVAL 30 DAY ) <= ah.date_time AND (ah.access_id = a.id OR ah.user_id = app.user_id) /*history for last 30 days*/
 WHERE u.key IS NOT NULL
 ORDER BY u.timestamp DESC


If you remove this piece of the query takes place OR ah.user_id = app.user_id

All that join individually tested.
June 3rd 19 at 19:32
1 answer
June 3rd 19 at 19:34
If in the left part of the conditions of the JOIN, WHERE or HAVING clause uses the function, then the indices in these expressions do not work, which greatly slows down the query.
Well, you have to see the size of source tables and JOINS selectivity s. Maybe you have built a Cartesian product of 1000000 on5 lines.
access_history > 5000000 rows. - Candido.Hartma commented on June 3rd 19 at 19:37
, Run a separate query with no JOINS and the last.
Now for each row of the result separately execute the last subquery, substituting the appropriate a.id and app.user_id. Add up the number of received lines and see the result of all your request. - Kenyatta commented on June 3rd 19 at 19:40

Find more questions by tags MySQL