Project

General

Profile

Bug #7203

Searches in the social network search box slow db down

Added by Yannick Warnier almost 7 years ago. Updated almost 7 years ago.

Status:
Bug resolved
Priority:
Normal
Category:
Social
Target version:
Start date:
05/07/2014
Due date:
% Done:

100%

Estimated time:
Spent time:
Complexity:
Normal
SCRUM pts - complexity:
?

Description

Searches in the social network's search box for users and groups, like the following, are locking the corresponding (non-InnoDB) tables and making the system use up all its "database connections".

SELECT DISTINCT u.user_id, u.username, firstname, lastname, email, tag, picture_uri
  FROM `chamilo_main`.`user` u
  INNER JOIN `chamilo_main`.`access_url_rel_user` url_rel_user ON (u.user_id = url_rel_user.user_id)
  LEFT JOIN `chamilo_main`.`user_rel_tag` uv ON (u.user_id AND uv.user_id AND  uv.user_id = url_rel_user.user_id)
  LEFT JOIN `chamilo_main`.`tag` ut ON (uv.tag_id = ut.id)
  WHERE ( tag LIKE 'morales ibarra eucario%') OR
    (
       u.firstname LIKE '%morales ibarra eucario%' OR
       u.lastname LIKE '%morales ibarra eucario%' OR
       u.username LIKE '%morales ibarra eucario%' OR
       concat(u.firstname,' ',u.lastname) LIKE '%morales ibarra eucario%' OR
       concat(u.lastname,' ',u.firstname) LIKE '%morales ibarra eucario%'
    )
    AND url_rel_user.access_url_id=1 AND u.active='1' AND u.status <> 6  ORDER BY username LIMIT 0 , 9;

An "EXPLAIN" on the query shows that user_rel_tag doesn't have a usable index:

+------+-------------+--------------+--------+-----------------------------------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+--------+---------------------------------+
| id   | select_type | table        | type   | possible_keys                                                                                                   | key                                     | key_len | ref                    | rows   | Extra                           |
+------+-------------+--------------+--------+-----------------------------------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+--------+---------------------------------+
|    1 | SIMPLE      | u            | ALL    | PRIMARY,status                                                                                                  | NULL                                    | NULL    | NULL                   | 343771 | Using temporary; Using filesort |
|    1 | SIMPLE      | url_rel_user | ref    | PRIMARY,idx_access_url_rel_user_user,idx_access_url_rel_user_access_url,idx_access_url_rel_user_access_url_user | idx_access_url_rel_user_access_url_user | 4       | chamilo_main.u.user_id |      1 | Using index                     |
|    1 | SIMPLE      | uv           | ALL    | NULL                                                                                                            | NULL                                    | NULL    | NULL                   |   1873 | Using where                     |
|    1 | SIMPLE      | ut           | eq_ref | PRIMARY                                                                                                         | PRIMARY                                 | 4       | chamilo_main.uv.tag_id |      1 | Using where                     |
+------+-------------+--------------+--------+-----------------------------------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+--------+---------------------------------+

So a first solution for optimization is to add the index on the user_id field:

alter table user_rel_tag add index idx_user_rel_tag_user (user_id);

This query has been added to the optimization manual of 1.9.8 and the upgrade procedure from 1.9 to v10.

The table should also be migrated to InnoDB to avoid lock-ups of the whole table. This should already be executed through the upgrade from 1.9 to v10, so only necessary if really blocking.

Just adding the index on user_rel_tag on a table of 1873 rows reduced the query execution time from more than 300 seconds to just 4.6s.

Also available in: Atom PDF