CODEWITHSUNDEEP
mysqlsql
cjhill
cjhill

Reputation: 1064

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

Upvotes: 0

Views: 337

Answers (2)

ysth
ysth

Reputation: 98398

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562731

A conventional index cannot help improve the performance of FIND_IN_SET() just like an index cannot help improve SUBSTRING() or LIKE with wildcards. The performance will never be good, and will get worse the larger your table grows.

But a fulltext index can help.

ALTER TABLE animals ADD FULLTEXT INDEX (value);

mysql> select * from animals where match(value) against ('rabbit' in boolean mode);
+----+--------------+
| id | value        |
+----+--------------+
|  5 | doggy,rabbit |
+----+--------------+
1 row in set (0.00 sec)

mysql> select * from animals where match(value) against ('rabbit doggy' in boolean mode);
+----+----------------+
| id | value          |
+----+----------------+
|  5 | doggy,rabbit   |
|  1 | doggy,elephant |
|  2 | kittycat,doggy |
+----+----------------+

For my test I made all the animal names at least 4 characters, because the default ft_min_word_len is 4 characters. Words shorter than that aren't indexed by a FULLTEXT index.

Read the sections on https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html for more information.

Upvotes: 0

Related Questions