Speed of query using FIND_IN_SET on MySql

Question

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

Solution

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.

Comments

[user2342558]

To avoid the use of the cached version, append SQL_NO_CACHE after SELECT