Optimize SQL query on two tables

Question

I have an SQL query on tables having a lot of rows. So this query runs for a very long time. How can I optimize this query? These tables already have indexes on id and friend_id

SELECT u.id, u.first, u.last, 
  group_concat(u2.first, " " , u2.last) MyFriends 
  FROM Users u
  INNER JOIN Friends f ON f.user_id = u.id 
  INNER JOIN Users u2 ON u2.id = f.friend_id
  GROUP BY u.id;

These are the table structures:

CREATE TABLE Users ( 
  id int(10) unsigned NOT NULL, 
  first varchar(50) DEFAULT NULL, 
  last varchar(50) DEFAULT NULL, 
  city varchar(20) DEFAULT NULL, 
  country varchar(20) DEFAULT NULL, 
  Age tinyint(3) unsigned NOT NULL, 
  KEY users_idx_id (id)) 
ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE Friends ( 
  user_id int(10) unsigned NOT NULL, 
  friend_id int(10) unsigned NOT NULL, 
  KEY idx_friends (friend_id)) 
ENGINE=InnoDB DEFAULT CHARSET=latin1;

you have to provide us the execution plan , and maybe the index definitions — eshirvana
– eshirvana, Commented Oct 21, 2021 at 17:26
@Max study this dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html — eshirvana
– eshirvana, Commented Oct 21, 2021 at 17:35

Rick James · Accepted Answer · 2021-10-21 23:18:10Z

1

A many-to-many mapping table (Friends) needs improved indexes. Drop all the indexes you have now and add

PRIMARY KEY(user_id, friend_id),
INDEX(friend_id, user_id)

More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Age is a moving target. Think about a better way to store that.

There are about 6 countries with names longer than 20. "Saint Vincent and the Grenadines" is 32.

As for cities, 'Poselok Uchebnogo Khozyaystva Srednego Professionalno-Tekhnicheskoye Uchilishche Nomer Odin' is 91 chars.

ALTER TABLE Friends
    DROP INDEX idx_friends,
    ADD PRIMARY KEY(user_id, friend_id),
    ADD INDEX(friend_id, user_id);

Every table should have a PRIMARY KEY:

ALTER TABLE Users
    DROP INDEX users_idx_id,
    ADD PRIMARY KEY(user_id)

Read about AUTO_INCREMENT.

The "execution plan" can be had by running EXPLAIN SELECT .... However it won't provide many clues in this case.

edited Oct 21, 2021 at 23:18

answered Oct 21, 2021 at 22:48

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Max Over a year ago

So is that the only change I need to do?

Max Over a year ago

what is the command to add them?

Rick James Over a year ago

@Max - I feel certain that it will speed up that query. If it fails to satisfy enough, then provide EXPLAIN SELECT ... and SHOW CREATE TABLE for each table. Also specify about how many rows there are in the tables. (There are many less-common things that could be causing performance problems.)

Rick James Over a year ago

@Max - I must see the SHOW CREATE TABLE so I can also specify how to drop the existing indexes.

Rick James Over a year ago

@Max - I added a bunch.

Collectives™ on Stack Overflow

Optimize SQL query on two tables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related