MySql JOIN performance go down with group by

Question

I have these tables:

table "f" (26000 record)
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| idFascicolo      | int(11)          | NO   | PRI |         |       |
| oggetto          | varchar          | NO   |index|         |       |
+------------------+------------------+------+-----+---------+-------+

table "r" (22000 record)
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| idRichiedente    | int(11)          | NO   | PRI |         |       |
| name             | varchar          | NO   |index|         |       |
+------------------+------------------+------+-----+---------+-------+

table "fr" (32000 record)
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| id               | int(11)          | NO   | PRI |         |       |
| idFascicolo      | int(11)          | NO   |index|         |  FK   |
| idRichiedente    | int(11)          | NO   |index|         |  FK   |
+------------------+------------------+------+-----+---------+-------+

this is my select:

SELECT
f.idFascicolo,
f.oggetto,
r.richiedente
FROM fr
JOIN f ON (f.idFascicolo=fr.idFascicolo)
JOIN r ON (r.idRichiedente=fr.idRichiedente)
WHERE r.name LIKE '%string%'

in the result, I would like to see only 1 row per f.idFascicolo (I should have "Rossi Mario" and "Rossi Marco" for the same f.idFascicolo) , the my new select is:

SELECT
f.idFascicolo,
f.oggetto,
r.richiedente
FROM fr
JOIN f ON (f.idFascicolo=fr.idFascicolo)
JOIN r ON (r.idRichiedente=fr.idRichiedente)
WHERE r.name LIKE '%string%'
GROUP BY f.idFascicolo

here, the performance read from PhpMyAdmin:

0.0057 seconds: .. WHERE r.name LIKE '%string%'
0.0527 seconds: .. WHERE r.name LIKE '%string%' GROUP BY f.idFascicolo
0.0036 seconds: .. WHERE r.name LIKE 'string%' GROUP BY f.idFascicolo

I don't understand if the problem of the slow query is GROUP BY or LIKE '%string%'(i need '%string%' .. I can't find an equivalent solution with fulltext index and MATCH .. AGAINST)

This is the explain:

+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+
| id   | select type | table | type | possible keys           |  key          | key_len | ref                  | rows      | Extra                                       |
+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+
| 1    | simple      | r     | ALL  | PRIMARY                 | NULL          | NULL    | NULL                 | 20925     |Using where; Using temporary; Using filesort | 
+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+ 
| 1    | simple      | fr    | ref  |idFascicolo,idRichiedente| idRichiedente | 4       | db.r.idRichiedente   | 1         |                                             |  
+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+
| 1    | simple      | f     |eq_ref|PRIMARY                  | PRIMARY       | 4       | db.fr.idFascicolo    | 1         |                                             |  
+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+

Where's the output of `EXPLAIN` for that given query? – Nico Haase Apr 16 '19 at 10:27 — Nico Haase, Apr 16 '19 at 10:27
just edit the first message – quattrocorde Apr 16 '19 at 11:59 — quattrocorde, Apr 16 '19 at 11:59

score 1 · Answer 1 · answered Apr 16 '19 at 10:23

1

You have two potential performance issues. First is the GROUP BY. This requires sorting the data, so it has to read all the data and do a lot of work.

The second is the LIKE. There is a fundamental difference between:

WHERE r.name LIKE '%string%'

and

WHERE r.name LIKE 'string%'

The second can use an index on r(name), because the like pattern does not start with a pattern.

I am not sure what your actual question is. I don't recommend doing using GROUP BY the way you are using it -- because you have unaggregated columns in the SELECT.

answered Apr 16 '19 at 10:23

Gordon Linoff

1,242,037
58
646
786

ok, I can use WHERE r.name LIKE 'string%' and so, use WHERE r.name LIKE '%string%' only when is necessary. But now, starting from my select, how can I have a single row for each f.idFascicolo if I shouldn't use GRUOP BY ? – quattrocorde Apr 16 '19 at 10:33
@quattrocorde . . . You can use a `where` clause, perhaps using window functions depending on your version of MySQL. – Gordon Linoff Apr 16 '19 at 11:24
can I have an example with "where clause" ? – quattrocorde Apr 16 '19 at 11:34
@quattrocorde . . . New questions should be asked as *questions* not *comments*. Please be clear about the results that you want. – Gordon Linoff Apr 16 '19 at 11:47
now that I understand the issue with "LIKE ''%string%" and GROUP BY , i would like to know how to use "WHERE clause" to have 1 row for each f.idFascicolo I have to open a new question? sorry but this is my 10th (?!?) message on stackoverflow ! – quattrocorde Apr 16 '19 at 12:05

MySql JOIN performance go down with group by

1 Answers1

Linked