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, [EMAIL PROTECTED] 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 like
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 FROM employee_subordinate 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.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Eric Scuccimarra
<[EMAIL PROTECTED]> To: [EMAIL PROTECTED]
cc:
06/24/2004 11:11 Fax to:
AM Subject: Query Problem with Lists
I have a table where one field is a long list of numbers in comma-delimited
format.
I need to do a query like:
SELECT * FROM Table 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.
Eric
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
