4

i have several problems with my query from a catalogue of products. The query is as follows:

SELECT DISTINCT (cc_id) FROM cms_catalogo 
JOIN cms_catalogo_lingua ON ccl_id_prod=cc_id 
JOIN cms_catalogo_famiglia ON (FIND_IN_SET(ccf_id, cc_famiglia) != 0) 
JOIN cms_catalogo_categoria ON (FIND_IN_SET(ccc_id, cc_categoria) != 0) 
JOIN cms_catalogo_sottocat ON (FIND_IN_SET(ccs_id, cc_sottocat) != 0) 
LEFT JOIN cms_catalogo_order ON cco_id_prod=cc_id AND cco_id_lingua=1 AND cco_id_sottocat=ccs_id 
WHERE ccc_nome='Alpine Skiing' AND ccf_nome='Ski'

I noticed that querying the first time it takes on average 4.5 seconds, then becomes rapid. I use FIND_IN_SET because in my Database on table "cms_catalogo" I have the column "cc_famiglia" , "cc_categoria" and "cc_sottocat" with inside ID separated by commas (I know it's stupid).

Example:

Table cms_catalogo

Column cc_famiglia: 1,2,3,4,5

Table cms_catalogo_famiglia

Column ccf_id: 3

The slowdown in the query may arise from the use of FIND_IN_SET that way?

If instead of having IDs separated by comma have a table with ID as an index would be faster?

I can not explain, however, why the first execution of the query is very slow and then speeds up

Lorenzo Belfanti
  • 1,205
  • 3
  • 25
  • 51
  • 1
    Yes. FIND_IN_SET is blisteringly quick, but it's still not going to beat properly normalized and indexed data. The speed-up is probably on account of query caching. Also DISTINCT is not a function. Finally, your naming convention leaves something to be desired, and you should try to get into the habit of properly qualifying column names. – Strawberry Jun 25 '14 at 09:04
  • DINSTINCT isn't a function? Why? I Use it to list the different (distinct) values, otherwise I would have duplicates. – Lorenzo Belfanti Jun 25 '14 at 09:28
  • 1
    The DISTINCT keyword is an option within the SELECT clause. It is not a function, and therefore contains no arguments. Parentheses are not needed, and could have no effect on the bahaviour of the DISTINCT option. – Strawberry Jun 25 '14 at 09:38

1 Answers1

4

It is better to use constraint connections between tables. So you better connect them by primary key.

If you want just to quick optimisation for this query:

  • Check explain select ... in mysql to see performance of you query;
  • Add indexes for columns ccc_id, ccf_id, ccs_id;
  • Check explain select ... after indexes added.

The first MySQL query takes much more time because it is raw query, the next are cached. So you should rely on first query time. If it is not complicated report then execution time should be less than 50-100ms, otherwise you can get problems with performance in total. Because I am so sure it is not the only one query for your application.

Yaro
  • 136
  • 8