I understand why we would want these to be in relational forms but in this
situation it isn't practical for a number of reasons. Normally that would
be what I would do.
However in this case the nature of the application is such that doing this
would cause an enormous load on the system as we would regularly have to
delete and recreate this entire table and that would require looping
through millions of rows on the application side.
So we decided that keeping lists would be easier and more efficient than
forcing the application and MySQL to go through enormous amounts of data
which would require the potential execution of over a million individual
queries anytime someone changes anything.
BTW - FIND_IN_SET works for the original issue.
At 12:53 PM 6/24/2004 -0400, SGreen@stripped wrote:
>I understand how these lists come into existence (trust me I have had to
>deal with enough of them). However, it is standard practice when working
>with _relational_ databases to split those lists of numbers into unique
>record pairs in a separate table. Your original source data was not
>relational, was it... ;-)
>To illustrate I will have to make up a scenario:
>Lets say you have a table of "employees" with columns like ID, Name, etc.
>Imagine this table has a field that holds a list of numbers that represents
>everyone that reports to the employee (their subordinates). In order to see
>if an employee is a subordinate of another employee, you would have to do a
>query like the one you are asking about.
>Here's the problem, in most languages you leave off the last separator so
>the list looks like "3,4,12,20,22". Doing a substring search in that list
>is not easy. Say you want to see if employee 2 is in the list for employee
>1, you would have to search employee 1's list for "<bos>2," , ",2," , and
>",2<eos>" just to make sure you _didn't_ match on 12, 20 or 22. (in this
>example I used <bos> and <eos> to represent the "beginning of string" and
>the "end of string". Those symbols don't actually work in MySQL but you
>_can_ do those searches at least a couple of different ways). That is a
>very slow search as you cannot use any indices and you will have to perform
>at least 3 substring comparisons PER ROW of your data. Or, you could try
>the FIND_IN_SET() function
>(http://dev.mysql.com/doc/mysql/en/String_functions.html). Once again, you
>cannot use an index and you will have to execute the function on EVERY ROW
>of data in your table, even if you only find 1 row of data.
>One way out of this mess is to create another table like
>CREATE TABLE employee_subordinate (
> employee_ID int,
> subordinate_ID int,
> PRIMARY KEY (employee_ID, subordinate_ID),
> Key (subordinate_ID)
>Then you would need to insert just one row for each subordinate in the list
>INSERT employee_subordinate VALUES (1,3), (1,4), (1,12), (1,20), (1,22)
>There are MANY advantages to this style of design: Searching the
>employee_subordinate table will be lightning quick as all values are no
>longer strings but integers. The indexes will be smaller so you will be
>able to fit them into memory (also faster). And, you could use other
>relational techniques like FOREIGN KEYS (if you are using InnoDB) to ensure
>that only valid IDs are entered into the table.
>If you have to make it seem as though the data is stored as a list, MySQL
>has some functions to convert a rowset of values into separated lists and
>back. In this example if you wanted to present all of the subordinates to
>employee 1 as a list you could write the following query
>SELECT employee_ID, GROUP_CONCAT(subordinate_ID) as subordinates
>WHERE employee_ID = 1
>GROUP BY employee_ID
>and that would return:
>| employee_ID | subordinates |
>| 1 | 3,4,12,20,22 |
>1 row in set (0.00 sec)
>I know this may not match directly to your data situation but you offered
>few specifics. Hope it helps.
>Unimin Corporation - Spruce Pine
> <eric@stripped> To:
> 06/24/2004 11:11 Fax
> AM Subject: Query Problem
> with Lists
>I have a table where one field is a long list of numbers in comma-delimited
>I need to do a query like:
>WHERE [number] IN list
>If I cut and paste the actual list in it works fine but when I use the
>column-name containing the list it returns nothing. I've been searching the
>MySQL docs for hours and haven't turned up anything. Apparently I can use
>set functions but the lists seem to be too big to store as sets.
>Any help is appreciated.
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1