Menu
It will be very great-full if anyone will provide me a small help in mysql.
I have a table having 1 billion records in which one column having comma separated value.
I have a comma separated values to search.
I want to select those rows which having anyone value in that comma separated column from that string value.
e.g, Table is A having column comma_separated like this:-
and i have a string having comma separated values "79, 62, 70, 107".
Result will be row number 1,2,3,5,7,8,9,10 (In mention Image.)
I did it with regex but it is taking too much time, so i want to avoid this for optimization purpose.
585
MySQL has a dedicated function FIND_IN_SET() that returns field index if the value is found in a string containing comma-separated values. For example, the following statement returns one-based index of value C in string A,B,C,D . If the given value is not found, FIND_IN_SET() function returns 0 .
INTERSECT Operator using DISTINCT and INNER JOIN Clause The following statement uses the DISTINCT operator and INNER JOIN clause for returning the distinct rows from both tables: mysql> SELECT DISTINCT column_list FROM table_name1. INNER JOIN table_name2 USING(join_condtion);
SELECT category_id FROM products INTERSECT SELECT category_id FROM inventory; Since you can't use the INTERSECT operator in MySQL, you will use the IN operator to simulate the INTERSECT query as follows: SELECT products.
SELECT is used to retrieve rows selected from one or more tables, and can include UNION operations and subqueries. Beginning with MySQL 8.0.31, INTERSECT and EXCEPT operations are also supported. The UNION , INTERSECT , and EXCEPT operators are described in more detail later in this section.
You can't really optimize what you are doing. Basically, you can run a query like this:
where find_in_set(79, comma_separated) > 0 or
find_in_set(62, comma_separated) > 0 or
find_in_set(70, comma_separated) > 0 or
find_in_set(107, comma_separated) > 0
This requires a full-table scan. And, although the performance might be slightly better than a regular expression, it still will not be efficient.
The proper way to store this data is as a junction table. This multiplies the number of rows, so the first row in your data becomes three rows in the junction table (one for each value).
There are numerous reasons why you do not want to store lists of things as a comma-separated list. Your values look like ids in another table, making things even worse:
185
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
