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 * FROMTable 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]
Re: Query Problem with Lists
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, [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 bos2, , ,2, , and ,2eos 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
Date Problem
We have a table with a Date Time field and we need to update only the date portion of that field. We have table a with field Foo with value '2004-01-01 12:15:00' and a date '2004-03-01' and we need to change the date portion of Foo to the date and leave the time part alone. So Foo would change from: '2004-01-01 12:15:00' to: '2004-03-01 12:15:00' I've been struggling with this for several hours now and I know there must be an easy solution. Any suggestions? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
Have one more question - indexing the relevant columns based on the explain info has made all of our queries immensely faster. But it appears that new rows are not automatically indexed. Does anyone know about this and if they are not indexed how do I reindex the tables? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
For anyone who is interested the thing that worked and brought the query down from 8 minutes to 5 seconds was separating out the JOIN to remove the OR. I made it into two queries and UNIONed them together and it all works beautifully now. Thanks. At 02:33 PM 2/25/2004 -0800, Daniel Clark wrote: I know Oracle likes the indexes separatly, but mySQL might like combinations. No, we tried individual indexes and then one big grouped index but not individual indexes on each of the fields. Adding the index actually added a few seconds to the query so we weren't sure if that was the way to go. I'll try this, though. Eric At 10:36 AM 2/25/2004 -0800, Daniel Clark wrote: Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- 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]
Query Problems
I am doing a very simple query joining two copies of tables with identical structures but different data. We are running MySQL 4.1.1. The tables each have about 24,000 lines of data in them. For some reason this query, which is a simple join between the two tables is taking 8 minutes to run. The query looks something like this: Select * FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. Does anyone have any idea what might be the problem and what we can do to fix it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Problems
I am doing a very simple query joining two copies of tables with identical structures but different data. We are running MySQL 4.1.1. The tables each have about 24,000 lines of data in them. For some reason this query, which is a simple join between the two tables is taking 8 minutes to run. The query looks something like this: Select * FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. Does anyone have any idea what might be the problem and what we can do to fix it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
No, we tried individual indexes and then one big grouped index but not individual indexes on each of the fields. Adding the index actually added a few seconds to the query so we weren't sure if that was the way to go. I'll try this, though. Eric At 10:36 AM 2/25/2004 -0800, Daniel Clark wrote: Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problems
Tried to make the indexes separate and did an EXPLAIN and no performance increase and this is what the explain says: id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE tb ALL PRIMARY,tb_ndx3,tb_ndx4,tb_ndx5 NULLNULLNULL24238 Using where 1 SIMPLE tb2 ALL PRIMARY,tb_ndx1,tb_ndx2,tb_ndx3 NULLNULLNULL24063 Using where 1 SIMPLE c eq_ref PRIMARY,c_ndx1 PRIMARY 4 Table1.key 1NULL Not sure exactly what this means but the fact that it isn't using keys seems to me that it would be a problem. Could it be the JOIN condition, which in this case is (tb.a = tb2.a OR (tb.b = tb2.b AND tb.c = tb2.c)) Seems like this should run rather smoothly but I've never done JOIN conditions like this before. -- At 05:33 PM 2/25/2004, Daniel Clark wrote: I know Oracle likes the indexes separatly, but mySQL might like combinations. No, we tried individual indexes and then one big grouped index but not individual indexes on each of the fields. Adding the index actually added a few seconds to the query so we weren't sure if that was the way to go. I'll try this, though. Eric At 10:36 AM 2/25/2004 -0800, Daniel Clark wrote: Do you have separate indexes on: Table1.ID Table2.ID Table1.Field1 Table2.Field1 Table1.Field1 Table1.Field2 Select* FROM Table1 as a INNER JOIN Table2 as b ON (a.ID = b.ID or (a.Field1 = b.Field1 and a.Field2 = b.Field2)) WHERE bla bla bla We have tried to set up indexes and the query still takes 8 minutes to run. It only returns 6,000 records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
