Speed of query using FIND_IN_SET on MySql

mysql performance find-in-set
Lorenzo Belfanti picture Lorenzo Belfanti · Jun 25, 2014 · Viewed 11.9k times · Source

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

Answer

Yaro picture Yaro · Sep 25, 2015

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.

Related questions

MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More
MySQL vs MongoDB 1000 reads

I have been very excited about MongoDb and have been testing it lately. I had a table called posts in MySQL with about 20 million records indexed only on a field called 'id'. I wanted to compare speed with MongoDB and …

Read More