count - Find most used id int comma seperated string in Mysql

Question

I have a Mysql database with a column that stores ids in a comma seperated string. I now need a Mysql command that gives me the most used id in all rows.

For example this Column

• id_12,id_13,id_14
• id_12,id_15,id_13
• id_11,id_12,id_18

Now i need a query that gives me the most used ids. In this case the most used is id_12 with 3 and the second most used is id_13 with 2.

I hope its understandable what i need

question from:https://stackoverflow.com/questions/65942087/find-most-used-id-int-comma-seperated-string-in-mysql

Answer 1

Your question is related with [this one] (Is storing a delimited list in a database column really that bad?).

I do think that your task is bound to be awkward and inefficient if you store the values in a comma-separated list. It would be far easier if you stored id values one per row. Then you could do this:

SELECT id, COUNT(*) AS count
FROM mytable
GROUP BY id
ORDER BY count DESC LIMIT 1;

But just telling you "you're doing it wrong, change everything" doesn't answer the question you asked.

The best I can come up with given your comma-separated list format is:

First create another table that has each id value, one per row.

create table idvalues ( id char(4) primary key );
insert into idvalues values ('id_11'),('id_12'),('id_13'),('id_14'),('id_15'),('id_18');

Then you can join this to your table and use FIND_IN_SET() to match each value to the lists:

SELECT idvalues.id
FROM idvalues JOIN yourtable 
  ON FIND_IN_SET(idvalues.id, yourtable.id_list);

This will produce a result set that you can count, as if you had stored the data in normal form:

SELECT id, COUNT(*) AS count
FROM (
    SELECT idvalues.id
    FROM idvalues JOIN yourtable 
      ON FIND_IN_SET(idvalues.id, yourtable.id_list)
) AS t
GROUP BY id
ORDER BY count DESC LIMIT 1;

But the caveat is that FIND_IN_SET() cannot be optimized with an index, so it's bound to do a Cartesian product. In other words, it will have to use that function as many times as the number of rows in idvalues multiplied by the number of rows in yourtable. It'll take a long time if these tables are large.

If you want this kind of query to be better optimized, then avoid using comma-separated lists.