Dear forum users!

I have a sql query to the database in which there are 100,000 lines in the "users" table and 20,000,000 records in the "friends" table. I am trying to execute the following query in phpMyAdmin:

SELECT `friends`.`friend_age` FROM `friends` WHERE `id_user` IN(SELECT `users`.`uid` FROM `users` WHERE `age`=13 AND `number_of_friends` <> 0) AND `friend_age` <> 0 ORDER BY `friend_age`

When you try to execute the request, everything hangs .... Then after some time phpMyAdmin gives the following error:

Fatal error: Maximum execution time of 300 seconds exceeded in D:\XAMPP\phpMyAdmin\libraries\dbi\DBIMysqli.class.php on line 290

I have already tried to create indices of fields for which the sample is carried out as it is written here: MySQL: using indexes . However, this did not help, the request is still not executed. Help please solve this problem.

I tried to look with the help of EXPLAIN , which is not so, and I realized that the first index is not very good: Result of the operation EXPLAIN

However, it is not yet clear how to optimize. Went here: 5.2.1. EXPLAIN statement syntax (getting information about SELECT)

So far, too, not really helped (((At least someone, please respond! Och need help !!!

PS : added an index on the id_user column in the friends table and the first index became better:

Index improvement

Transferred from meta.ru.stackoverflow.com May 3, '15 at 9:23 .

This question was originally posted on the discussion forum, support and innovations for the site programmers.

  • one
    You need to migrate from the meta to the main site. - VladD
  • @VladD, I do not understand you. I went under my account as usual and asked a question. Before that, the system said that everything was fine, that my accounts were merged. What else is needed? - neo
  • Well, you asked a question on Mette, but you had to on the main site. Wait a little, I think they will take it. - VladD
  • @VladD, can you help me?))))) - neo
  • 2
    Perhaps your database does not know how to optimize subquery, and you should try to rewrite to JOIN. - VladD

1 answer 1

Try this:

 select distinct f.friend_age from fiends f join users u on f.user_id = u.uid where u.age = 13 and f.friend_age<>0 order by f.friend_age

Index for users by age (you have it apparently).
The index for friends by user_id .

If will hang, explain this request show, probably it is necessary to use.

Restriction on the number of friends is not necessary, because only those who have them will be selected.

UPD

Unfortunately, the problem described in the question cannot be solved. The question here is not in the indices (although without them everything will be even worse), but in the data volume.

To obtain the median, the result of such a query is sufficient:

 select f.friend_age, count(*) cnt from fiends f join users u on f.user_id = u.uid where u.age = 13 and f.friend_age<>0 group by f.friend_age

Thus, you get an aggregated list of ages (we found that a similar query with a distinct one is executed in a more or less reasonable time).

Further calculating the median is no longer so difficult. If difficulties still arise, it is better to arrange another question.

  • Thank you so much for the answer!))) But I don’t quite understand what f and u means. Are these any aliases in mySql? - neo
  • one
    they are usually called "aliases" - artoodetoo
  • @Yura Ivanov, the request worked, even timed 300 seconds. But I just need to not unique age were, and everything. Therefore, the word "DISTINCT" unnecessary. I tried without it, the request is frozen ... - neo
  • @cat_woman Please specify for what purpose you need millions of identical numbers within the age of thirteen friends? The request has been frozen due to too many entries. Formulate what problem you are solving, maybe there will be a simpler and accordingly quick solution? - Yura Ivanov
  • one
    @Yura Ivanov, mean and median are different things. I need exactly the median. - neo