-1

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 at 10:27
  • just edit the first message – quattrocorde Apr 16 at 11:59
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.

  • 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 at 10:33
  • @quattrocorde . . . You can use a where clause, perhaps using window functions depending on your version of MySQL. – Gordon Linoff Apr 16 at 11:24
  • can I have an example with "where clause" ? – quattrocorde Apr 16 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 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 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.