Multiple dynamic FIND_IN_SET searches

So, I have an array like this in my programming language:

$animalsToSearch = ['fox', 'cat', 'dog'];

And I have a MySQL database table like this:

id  | value
----+----------------
1   | dog,elephant
2   | cat,dog
3   | spider,fox,cat
4   | cat
5   | dog,rabit

Unfortunately, I cannot change the table to be more relational (yet).

I want to pull out how many times an item in my array is present in the database value column. Now, the obvious way to do this would be something like:

<?php
$count = [];

foreach (['fox', 'cat', 'dog'] as $animal) {
    $count[$animal] = some_function_that_gets_the_data($animal);
}

But there might be dozens of animals to search for, and I don’t want to send a db request per animal. I have thought of the way below, and then using PHP to look at the values to see if the animal is present within… but it’s a bit hacky. Might there be a better way?

select
    `value`
from
    `animal`
where
       find_in_set('fox', value)
    or find_in_set('cat', value)
    or find_in_set('dog', value);

---

ysth’s solution explain‘d

id  select_type  table       partitions    type  possible_keys  key   key_len  ref    rows       filtered  Extra
1   PRIMARY      <derived2>  NULL          ALL   NULL           NULL  NULL     NULL   2          100.00    Using temporary; Using filesort
1   PRIMARY      animal      NULL          ALL   NULL           NULL  NULL     NULL   1,879,296  100.00    Using where; Using join buffer (Block Nested Loop)
2   DERIVED      NULL        NULL          NULL  NULL           NULL  NULL     NULL   NULL       NULL      No tables used
3   UNION        NULL        NULL          NULL  NULL           NULL  NULL     NULL   NULL       NULL      No tables used

Answer

Join against your list:

select animallist.animal, count(*) as times
from (
    select 'fox' as animal union all select 'cat' union all select 'dog'
) animallist
join animal on find_in_set(animallist.animal, animal.value)
group by animallist.animal

You can try forcing it to only read through animal once, I don’t know if that will make it faster or slower:

select straight_join animallist.animal, count(*) as times
from animal on find_in_set(animallist.animal, animal.value)
join (
    select 'fox' as animal union all select 'cat' union all select 'dog'
) animallist
group by animallist.animal