Re[4]: SELECT DISTINCT with ORDER BY implementation
Hi! Andrew == Andrew Aksyonoff [EMAIL PROTECTED] writes: Andrew Hello Sergey, Andrew Monday, September 15, 2008, 10:41:31 PM, you wrote: in MySQL but in general case it can't assume any order and will have to re-sort the sub-select result by outer GROUP BY instead of inner ORDER BY. If that sorting is stable, this should work, but can we rely SP Yes. This is documented behavior: SP http://dev.mysql.com/doc/refman/5.0/en/select.html : SP If you use GROUP BY, output rows are sorted according to the GROUP BY SP columns as if you had an ORDER BY for the same columns. To avoid the SP overhead of sorting that GROUP BY produces, add ORDER BY NULL: Andrew Well, this snippet documents how the *grouped* rows will be ordered, Andrew but the question is about the properties of specific sorting algorithm Andrew that is internally used to implement GROUP BY. Andrew I'm not sure if I'm clear enough so let me provide an example. Assume Andrew that the inner SELECT produces the following: Andrew id=1, sortkey=123, groupkey=33 Andrew id=2, sortkey=124, groupkey=33 Andrew id=3, sortkey=125, groupkey=11 Andrew id=4, sortkey=126, groupkey=11 Andrew id=5, sortkey=127, groupkey=22 Andrew id=6, sortkey=128, groupkey=22 Andrew I suppose that 'GROUP BY groupkey' will have to sort the incoming Andrew rows by groupkey, then go over it sequentially, keeping only the Andrew first encountered row for every given groupkey. That is one algoritm, but MySQL has others. Andrew However if the specific sorting algorithm is not stable it *might* Andrew change the order and produce something like that for temporary Andrew sorted set: It's not stable; MySQL is using several different technics to calculate GROUP BY and may thus return the rows in any order within the group by. Andrew id=4, sortkey=126, groupkey=11 Andrew id=3, sortkey=125, groupkey=11 Andrew id=6, sortkey=128, groupkey=22 Andrew id=5, sortkey=127, groupkey=22 Andrew id=1, sortkey=123, groupkey=33 Andrew id=2, sortkey=124, groupkey=33 Andrew And put id=4 instead of id=3 into the result set. Andrew So the question is a bit more subtle :) It's whether the algorithm Andrew that GROUP BY (and possibly everything else) uses stable or not. Andrew I'd bet a quarter that it is but just want to make sure :) In general with SQL: Don't assume any order of rows if you don't explicitely specify a sort order. You can send the quarter to 'the well being of dolphin fund'. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[5]: SELECT DISTINCT with ORDER BY implementation
Hello Michael, Tuesday, September 30, 2008, 8:00:36 PM, you wrote: Andrew However if the specific sorting algorithm is not stable it *might* MW It's not stable; MySQL is using several different technics to MW calculate GROUP BY and may thus return the rows in any order within MW the group by. Thanks for clarification. MW In general with SQL: Don't assume any order of rows if you don't MW explicitely specify a sort order. Sure. But sometimes, knowing how the system works internally, we can still make a kludge. -- Best regards, Andrewmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT Optimizations
SELECT DISTINCT can be kind of slow if there are many result values, specifically if those result values include large VARCHARs. Furthermore, some database engines cannot support a SELECT DISTINCT if any LOBs are included in the result values. I'm trying to find a general way to optimize SELECT DISTINCT since for more purposes I rarely need to decide DISTINCT on any more than a single column. For example, consider I have a tree structure where leafs or groups can have more than a single parent group (i.e. they are links). (A) SELECT DISTINCT G.oid, G.name, G.description, G.type [, ...] FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345) Something like this can be slow on a large result set with many large character columns and it does not even express clearly what I mean, because what I really mean to say is: (B) SELECT ( FOR DISTINCT ON G.oid ), G.name, G.description, G.type [, ...] FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345) Of course there is no valid SQL like this. So what I end up doing is the following: (C) SELECT G.oid, G.name, G.description, G.type [, ...] FROM Groupable G WHERE G.oid IN ( SELECT G.oid FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345)) For the majority of cases this will perform better than (A). However, sometimes the expense of the subquery will out weight the sort used for the distinct and it will not be faster. Is there a standard SQL way to avoid sorting on every field for a distinct on a unique key without having to perform a subquery? Maybe something with a group by? TIA for any ideas or thoughts... R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT uses index but is still slow
0.01 seconds is so fast that I wonder if that's actually because the query cache is storing the query. Do you have query cache enabled? James At 6:35 am + 5/1/06, C.R.Vegelin wrote: Hi James, I have found similar - slowdown - effects for queries. However, it is not always clear what causes the lack of speed. For example, I have a table with more than 9 million rows, including a non-unique indexed item myKey (tinyint). The query Select myKey, count(*) from myTable Group By myKey; takes with the CLI about 25 seconds, BUT the second time it takes only 0.01 second ! I think that the 1st query run includes loading indices into memory. I suggest to test your query twice from the CLI. HTH, Cor Vegelin I have a query that takes a long time even though it is only examining the index file. Is this normal? I would have thought that MySQL would be much quicker to get the unique values from an index. select distinct date_id from data_table; # returns 89 rows in 23 seconds - simple index on date_id with 2 other indices - just under 40,000,000 rows in table - index file is 730 MB EXPLAIN SELECT gives the following: type = index key = date_id rows = 39726908 extra = using index FWIW the result is identical with 'select date_id from data_table group by date_id;'. (Using version 4.1.15 on Windows, and I can't see anything relevant in the change notes for 4.1.16.) Finally, here's a CREATE TABLE: CREATE TABLE data_table ( is_import tinyint(1) NOT NULL DEFAULT 0, comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0, date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0, value bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_2 bigint(20) UNSIGNED DEFAULT NULL, c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT uses index but is still slow
I have a query that takes a long time even though it is only examining the index file. Is this normal? I would have thought that MySQL would be much quicker to get the unique values from an index. select distinct date_id from data_table; # returns 89 rows in 23 seconds - simple index on date_id with 2 other indices - just under 40,000,000 rows in table - index file is 730 MB EXPLAIN SELECT gives the following: type = index key = date_id rows = 39726908 extra = using index FWIW the result is identical with 'select date_id from data_table group by date_id;'. (Using version 4.1.15 on Windows, and I can't see anything relevant in the change notes for 4.1.16.) Finally, here's a CREATE TABLE: CREATE TABLE data_table ( is_import tinyint(1) NOT NULL DEFAULT 0, comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0, date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0, value bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_2 bigint(20) UNSIGNED DEFAULT NULL, c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT uses index but is still slow
Hi James, I have found similar - slowdown - effects for queries. However, it is not always clear what causes the lack of speed. For example, I have a table with more than 9 million rows, including a non-unique indexed item myKey (tinyint). The query Select myKey, count(*) from myTable Group By myKey; takes with the CLI about 25 seconds, BUT the second time it takes only 0.01 second ! I think that the 1st query run includes loading indices into memory. I suggest to test your query twice from the CLI. HTH, Cor Vegelin - Original Message - From: James Harvard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 04, 2006 8:28 PM Subject: SELECT DISTINCT uses index but is still slow I have a query that takes a long time even though it is only examining the index file. Is this normal? I would have thought that MySQL would be much quicker to get the unique values from an index. select distinct date_id from data_table; # returns 89 rows in 23 seconds - simple index on date_id with 2 other indices - just under 40,000,000 rows in table - index file is 730 MB EXPLAIN SELECT gives the following: type = index key = date_id rows = 39726908 extra = using index FWIW the result is identical with 'select date_id from data_table group by date_id;'. (Using version 4.1.15 on Windows, and I can't see anything relevant in the change notes for 4.1.16.) Finally, here's a CREATE TABLE: CREATE TABLE data_table ( is_import tinyint(1) NOT NULL DEFAULT 0, comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0, date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0, value bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_2 bigint(20) UNSIGNED DEFAULT NULL, c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- 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]
SELECT DISTINCT
Hello list, I'm getting the distinct 'params' columns from a table with this query SELECT DISTINCT params FROM table; but this gets the rows with distinct 'params' cols, but I want to know from which row each params correspond, like this (which is not correct) SELECT id, DISTINCT params FROM table; and the query based in a recent post doesn't work select distinct on (params), params, id from table order by params, id I checked the syntax and the later isn't correct. Any ideas? -- Nuno Pereira Estagiário Carclasse - Comércio Automóveis, S.A. Lugar Sr. dos Perdões - Ribeirão (Famalicão) Telf.: 252 330 550 - Tlm: 965 215 076 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT
Nuno Pereira [EMAIL PROTECTED] wrote on 08/05/2005 02:04:35 PM: Hello list, I'm getting the distinct 'params' columns from a table with this query SELECT DISTINCT params FROM table; but this gets the rows with distinct 'params' cols, but I want to know from which row each params correspond, like this (which is not correct) SELECT id, DISTINCT params FROM table; and the query based in a recent post doesn't work select distinct on (params), params, id from table order by params, id I checked the syntax and the later isn't correct. Any ideas? -- Nuno Pereira Estagiário Carclasse - Comércio Automóveis, S.A. Lugar Sr. dos Perdões - Ribeirão (Famalicão) Telf.: 252 330 550 - Tlm: 965 215 076 email: [EMAIL PROTECTED] Depending on what version server you are running, the GROUP_CONCAT() function may be an option for you: SELECT params, GROUP_CONCAT(ID) FROM table GROUP BY params; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: SELECT DISTINCT
Here's one (not very clean, but it works) way to do it: SELECT id, params FROM table GROUP BY params; The trouble is, how do you know which id should come with it? If you table is id param 1 1 2 1 should the query return 1, 1 or 2, 1 ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT
[EMAIL PROTECTED] wrote: Depending on what version server you are running, the GROUP_CONCAT() function may be an option for you: SELECT params, GROUP_CONCAT(ID) FROM table GROUP BY params; I'm using 4.1.10a-standard-log Server version, and this is just what I wanted. Thanks. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT
Scott Noyes wrote: Here's one (not very clean, but it works) way to do it: SELECT id, params FROM table GROUP BY params; The trouble is, how do you know which id should come with it? If you table is id param 1 1 2 1 should the query return 1, 1 or 2, 1 ? This is not really what I want, the reply from Shawn Green is just what I want. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT ROW(A,B) ?
SELECT DISTINCT ROW(A,B) from a; While I am on a roll (of sorts) can I ask if the above syntax should be valid or not? I have a feeling it should be valid (but isn't - it causes an error). Is this the correct behaviour? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT ROW(A,B) ?
Dan Bolser wrote: SELECT DISTINCT ROW(A,B) from a; While I am on a roll (of sorts) can I ask if the above syntax should be valid or not? If you mean the exact syntax above, I think not... it looks like ROW() is a function taking two parameters in this case... what does the function return? Is it an aggregate function? If the ROW() function was a synonym for the CONCAT() function, or worked in a similar way, it might work, but what is the point? I have a feeling it should be valid (but isn't - it causes an error). Is this the correct behaviour? Try this: SELECT DISTINCTROW A,B from a; DISTINCTROW and DISTINCT are synonyms: URL: http://dev.mysql.com/doc/mysql/en/select.html#id2828077 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT ROW(A,B) ?
On Sat, 7 May 2005, Roger Baklund wrote: Dan Bolser wrote: SELECT DISTINCT ROW(A,B) from a; While I am on a roll (of sorts) can I ask if the above syntax should be valid or not? If you mean the exact syntax above, I think not... it looks like ROW() is a function taking two parameters in this case... what does the function return? Is it an aggregate function? Good question. I don't really know what the function is. You can see it in action here... http://dev.mysql.com/doc/mysql/en/row-subqueries.html The expressions (1,2) and ROW(1,2) are sometimes called row constructors. The two are equivalent. They are legal in other contexts, too. If the ROW() function was a synonym for the CONCAT() function, or worked in a similar way, it might work, but what is the point? I know it is easy to fix given the example, but I want to nest the whole thing in an IF statement COUNT(DISTINCT IF(C=1,ROW(A,B),NULL)) AS x FROM a; Which is equivelent to ... COUNT(DISTINCT A,B) AS x FROM a WHERE C=1; But the former is more powerfull (if it worked). The following works (as suggested)... COUNT(DISTINCT IF(C=1,CONCAT(A,B),NULL)) AS x FROM a; But it isn't optimal because the DISTINCT can't make use of an index over A and B. I guess I should just stop complaining and make an AB column and put an index over it, but it just 'feels' like ROW should work here. Cheers I have a feeling it should be valid (but isn't - it causes an error). Is this the correct behaviour? Try this: SELECT DISTINCTROW A,B from a; DISTINCTROW and DISTINCT are synonyms: URL: http://dev.mysql.com/doc/mysql/en/select.html#id2828077 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT Problem
People, including me, often expect the wrong thing from SELECT DISTINCT, May suggest you do the following: Create table temp_tb_spots as Select * from tb_spots where aired_station = '??' ; select distinct Date from temp_tb_spots group ; In a different window run Select Date from temp_tb_spots group by Date ; The creation of the extra table is so you can browse it to help convince yourself you are getting what you intend The two select statements are equivalent in MySQL and will give you the same result. This result should be the same as your original query. If not then there is a bug. Hope that helps set your expectation of the distinct key word. Best Regards, Boyd E. Hemphill WEST Project Manager MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com -Original Message- From: James Purser [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 08, 2005 7:19 PM To: mysql@lists.mysql.com Subject: SELECT DISTINCT Problem I have a large database that I am trying to run a SELECT DISTINCT across. SELECT DISTINCT Date FROM tb_spots WHERE aired_station = '??' However the results I am getting from this query do not match up with the data on the database, instead there are large gaps. Is there any know problem with SELECT DISTINCT across large databases? -- James Purser Winnet Developer +61 2 4223 4131 http://www.winnet.com.au -- 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]
SELECT DISTINCT Problem
I have a large database that I am trying to run a SELECT DISTINCT across. SELECT DISTINCT Date FROM tb_spots WHERE aired_station = '??' However the results I am getting from this query do not match up with the data on the database, instead there are large gaps. Is there any know problem with SELECT DISTINCT across large databases? -- James Purser Winnet Developer +61 2 4223 4131 http://www.winnet.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with select distinct
Michael, Thanks for your suggestion. Here's the query that seems to have fixed the problem, without a DISTINCT clause: select Documents.ID, Name, max(DownloadLog.AddedOn) as DownloadedOn from Documents, DocumentFiles, DownloadLog where Documents.ID = DocumentFiles.Document and DocumentFiles.ID = DownloadLog.DocumentFile group by Documents.ID order by DownloadedOn desc limit 10 It seems to work perfectly. Thanks again! Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 12:08 AM To: Frederic Wenzel Cc: Gustafson, Tim; mysql@lists.mysql.com Subject: Re: Problems with select distinct Frederic Wenzel wrote: On Tue, 25 Jan 2005 08:44:45 -0500, Michael Stassen [EMAIL PROTECTED] wrote: I suspect mysql is doing the DISTINCT before the ORDER BY. One of the reasons I avoid DISTINCT when possible. Isn't this supposed to be correct? Ordering has to take place as the very last operation, after any selection and projection, doesn't it? Regards Fred In theory, or in practice? In practice, ordering is sometimes done ahead of time using an index, and DISTINCT may be optimized as a GROUP BY. In theory, ORDER BY sorts the rows and DISTINCT throws out duplicates. So long as we sort by columns included in the SELECT output, it won't make any difference which you do first and which last. The problem comes when we sort on columns not in the selected output. Now that I've thought about it some more, I don't believe order of operations matters at all in that case, because which duplicate rows are thrown away by DISTINCT is not defined. You see? Suppose, prior to DISTINCT or ORDER BY, you have these rows: ID Name +--+--+ 10 Test 1 10 Test 1 11 Test 2 10 Test 1 11 Test 2 Which two rows should DISTINCT keep? Even if you sort first, DISTINCT is under no obligation to choose the first rows it finds. That DISTINCT may be optimized as a GROUP BY is instructive. MySQL lets you do things like SELECT id, name, updated FROM mytable GROUP BY id, name; but the 'updated' column is chosen more at less randomly for each group. That is, for each group, you simply get one value of updated from an undetermined row which belongs to the group. I think that's what's happening here. The lesson is that you cannot use DISTINCT and then ORDER BY an unselected column and get meaningful results. I'm not a big fan of DISTINCT. We get a lot of questions on the list that amount to Here's my query, but it gives me more rows than I want. I tried to fix it by adding DISTINCT, but now I don't get the right result. More often than not, there is a better query which explicitly retrieves precisely the desired rows, with no need for DISTINCT. Michael smime.p7s Description: S/MIME cryptographic signature
Problems with select distinct
Hello I am trying to run the following query on my server: select Documents.ID, Name from Documents, DocumentFiles, DownloadLog where Documents.ID = DocumentFiles.Document and DocumentFiles.ID = DownloadLog.DocumentFile order by DownloadLog.AddedOn desc limit 5 It works correctly, except that it displays duplicate rows. If I put a distinct before Documents.ID, it gives me only unique rows, but the rows are not ordered correctly. For example, without the distinct clause, I get the following rows: ID Name 10 Test 1 10 Test 1 11 Test 2 10 Test 1 12 Test 3 With the distinct clause, I get this: ID Name 12 Test 3 13 Test 4 10 Test 1 11 Test 2 14 Test 5 Why does adding the distinct clause change the order of the rows completely? Thanks in advance! Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ smime.p7s Description: S/MIME cryptographic signature
Re: Problems with select distinct
I suspect mysql is doing the DISTINCT before the ORDER BY. One of the reasons I avoid DISTINCT when possible. Try: SELECT Documents.ID, Name FROM Documents, DocumentFiles, DownloadLog WHERE Documents.ID = DocumentFiles.Document AND DocumentFiles.ID = DownloadLog.DocumentFile GROUP BY Documents.ID, Name ORDER BY MAX(DownloadLog.AddedOn) DESC LIMIT 5; Michael Gustafson, Tim wrote: Hello I am trying to run the following query on my server: select Documents.ID, Name from Documents, DocumentFiles, DownloadLog where Documents.ID = DocumentFiles.Document and DocumentFiles.ID = DownloadLog.DocumentFile order by DownloadLog.AddedOn desc limit 5 It works correctly, except that it displays duplicate rows. If I put a distinct before Documents.ID, it gives me only unique rows, but the rows are not ordered correctly. For example, without the distinct clause, I get the following rows: ID Name 10 Test 1 10 Test 1 11 Test 2 10 Test 1 12 Test 3 With the distinct clause, I get this: ID Name 12 Test 3 13 Test 4 10 Test 1 11 Test 2 14 Test 5 Why does adding the distinct clause change the order of the rows completely? Thanks in advance! Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with select distinct
On Tue, 25 Jan 2005 08:44:45 -0500, Michael Stassen [EMAIL PROTECTED] wrote: I suspect mysql is doing the DISTINCT before the ORDER BY. One of the reasons I avoid DISTINCT when possible. Isn't this supposed to be correct? Ordering has to take place as the very last operation, after any selection and projection, doesn't it? Regards Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with select distinct
Frederic Wenzel wrote: On Tue, 25 Jan 2005 08:44:45 -0500, Michael Stassen [EMAIL PROTECTED] wrote: I suspect mysql is doing the DISTINCT before the ORDER BY. One of the reasons I avoid DISTINCT when possible. Isn't this supposed to be correct? Ordering has to take place as the very last operation, after any selection and projection, doesn't it? Regards Fred In theory, or in practice? In practice, ordering is sometimes done ahead of time using an index, and DISTINCT may be optimized as a GROUP BY. In theory, ORDER BY sorts the rows and DISTINCT throws out duplicates. So long as we sort by columns included in the SELECT output, it won't make any difference which you do first and which last. The problem comes when we sort on columns not in the selected output. Now that I've thought about it some more, I don't believe order of operations matters at all in that case, because which duplicate rows are thrown away by DISTINCT is not defined. You see? Suppose, prior to DISTINCT or ORDER BY, you have these rows: ID Name +--+--+ 10 Test 1 10 Test 1 11 Test 2 10 Test 1 11 Test 2 Which two rows should DISTINCT keep? Even if you sort first, DISTINCT is under no obligation to choose the first rows it finds. That DISTINCT may be optimized as a GROUP BY is instructive. MySQL lets you do things like SELECT id, name, updated FROM mytable GROUP BY id, name; but the 'updated' column is chosen more at less randomly for each group. That is, for each group, you simply get one value of updated from an undetermined row which belongs to the group. I think that's what's happening here. The lesson is that you cannot use DISTINCT and then ORDER BY an unselected column and get meaningful results. I'm not a big fan of DISTINCT. We get a lot of questions on the list that amount to Here's my query, but it gives me more rows than I want. I tried to fix it by adding DISTINCT, but now I don't get the right result. More often than not, there is a better query which explicitly retrieves precisely the desired rows, with no need for DISTINCT. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT : I've found one trick !
Answer to my query select distinct http://lists.mysql.com/mysql/175839 http://lists.mysql.com/mysql/175839 To select properly only one time one element of a column in a list (here the journal name) I have to add a selection on the end of the name that it is not a space or a return ! Both are found to be necessary even if in my list no space or return are present in the journal names. It appears that SELECT is extended to the name , name\n and name\r ! SELECT DISTINCT journal FROM reference WHERE journal ' ' AND journal NOT LIKE '%\n' AND journal NOT LIKE '%\r ' ORDER BY journal Gives the required list with only one time each journal name; Thanks again for suggestions. Luc BARBIER _ Luc BARBIER DSM/DRECAM/SPCSI Bât 462 CEA Saclay 91191 Gif-sur-Yvette Cedex E-Mail : [mailto:[EMAIL PROTECTED] Tel : 33 (0)1 69 08 51 60 FAX : 33 (0)1 69 08 84 46
Speed up SELECT DISTINCT
I have a web application using MySQL 3.2x server. In the application theres an HTML form used for searching an ISAM table of about 500k records. To populate select (drop down) fields in the form I do a couple of SELECT DISTINCT queries against this table. What's the best indexing strategy to speed up these queries? I've added indexes for both of the fields on which I do a SELECT DISTINCT. It seems to have helped a little, but the queries are still fairly slow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed up SELECT DISTINCT
It is difficult to say without knowing more. It would be helpful if you'd show us the queries, as well as what EXPLAIN http://dev.mysql.com/doc/mysql/en/EXPLAIN.html has to say about them. Your table structure may also help. I will say that most queries I've seen posted here which used DISTINCT could have been written more efficiently without DISTINCT. Without seeing your queries though, I'd be foolish to speculate. Michael Jim McAtee wrote: I have a web application using MySQL 3.2x server. In the application theres an HTML form used for searching an ISAM table of about 500k records. To populate select (drop down) fields in the form I do a couple of SELECT DISTINCT queries against this table. What's the best indexing strategy to speed up these queries? I've added indexes for both of the fields on which I do a SELECT DISTINCT. It seems to have helped a little, but the queries are still fairly slow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed up SELECT DISTINCT
Pretty straightforward. The other query is identical, except that the column is of type INT. It executes a little faster, as would problably be expected. SELECT DISTINCT directory FROM pagestats ORDER BY directory +---+---+---+---+-++ | table | type | possible_keys | key | key_len | ref| +---+---+---+---+-++ | pagestats | index | [NULL]| Directory | 25 | [NULL] | +---+---+---+---+-++ +-+ rows | Extra | +-+ 438710 | Using index | +-+ - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Jim McAtee [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 10:03 PM Subject: Re: Speed up SELECT DISTINCT It is difficult to say without knowing more. It would be helpful if you'd show us the queries, as well as what EXPLAIN http://dev.mysql.com/doc/mysql/en/EXPLAIN.html has to say about them. Your table structure may also help. I will say that most queries I've seen posted here which used DISTINCT could have been written more efficiently without DISTINCT. Without seeing your queries though, I'd be foolish to speculate. Michael Jim McAtee wrote: I have a web application using MySQL 3.2x server. In the application theres an HTML form used for searching an ISAM table of about 500k records. To populate select (drop down) fields in the form I do a couple of SELECT DISTINCT queries against this table. What's the best indexing strategy to speed up these queries? I've added indexes for both of the fields on which I do a SELECT DISTINCT. It seems to have helped a little, but the queries are still fairly slow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select distinct
Using the request : SELECT DISTINCT journal FROM reference WHERE TYPE = 'article' AND etat = 'published' AND journal = 'Physical Review Letters' I get two type of answers One with 'Physical Review Letters' (WHERE `journal` = 'Physical Review Letters' ) the other with 'Physical Review Letters ' with an additional blank at the end of the journal name. (WHERE `journal` = 'Physical Review Letters\r\n' ) How to select with the exact journal name only. Thanks for your help, L. Barbier _ Luc BARBIER DSM/DRECAM/SPCSI Bât 462 CEA Saclay 91191 Gif-sur-Yvette Cedex E-Mail : [mailto:[EMAIL PROTECTED] Tel : 33 (0)1 69 08 51 60 FAX : 33 (0)1 69 08 84 46
Re: Index not used for select distinct?
Hi, Bill Easton wrote:
I think that MySQL is doing what would be expected, namely an index scan
which reads entire index on IP and selects distinct values of IP.
What I expect it to do is to give me all the distinct values in that
row. It should be able to notice that there are only a few distinct
values in there, and a select distinct over an indexed column doesn`t
need to do a full scan.
An open-coded loop
val = db.Do(select min(IP) from test)
while (val) {
process(val)
val = db.Do(select min(IP) from test where IP '$val')
}
runs almost instantly. MySQL should be able to do that optimization, it's
rather obvious (ten million rows, and IP has less than 100 different
values).
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index not used for select distinct?
Matthias,
I think that MySQL is doing what would be expected, namely an index scan
which reads entire index on IP and selects distinct values of IP. Whatever
you do, it's going to read 10,991,123 of something and select distinct
values. The use of the index saves a possible sort and allows reading a
smaller record than the full table.
A clever programmer, knowing that there are a handfull of values for IP,
could probably read the index structure and figure out how to avoid reading
all blocks of the index. However, this is a bit of a special case, and the
MySQL optimizer isn't that smart. You probably aren't going to do much by
tinkering with the indexes.
If you need to do this query frequently, you could add a table containing
just the IP values. You would add to it (INSERT IGNORE) when adding to the
larger table. You would either
(1) Remember to check for a delete on the smaller table when deleting from
the larger table, or
(2) Use a LEFT JOIN in your query to select those values in the smaller
table which exist in the larger table; you would then rebuild the smaller
table from time to time when it has too many obsolete entries. (The left
join should be fast, as it only has to look for one of each possible IP in
the larger table.)
HTH, Bill
Matthias Urlichs wrote:
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci
NOT NULL default 'WARN',
`epoch` int(10) unsigned NOT NULL default '0',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
However,
explain select distinct ip from test;
++-+---+---+---+--+-+--
+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+---+---+--+-+--
+--+-+
| 1 | SIMPLE | test | index | NULL | IP | 15 | NULL
| 10991123 | Using index |
++-+---+---+---+--+-+--
+--+-+
takes a *long* time and obviously scans the whole table.
Ideas, anybody?
MyISAM vs. InnoDB behave identically.
4.0 or 4.1.5 also didn't make a difference; I didn't test 5.0, as this
is supposed to be a production system.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index not used for select distinct?
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci NOT NULL
default 'WARN',
`epoch` int(10) unsigned NOT NULL default '0',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
However,
explain select distinct ip from test;
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
++-+---+---+---+--+-+--+--+-+
| 1 | SIMPLE | test | index | NULL | IP | 15 | NULL | 10991123
| Using index |
++-+---+---+---+--+-+--+--+-+
takes a *long* time and obviously scans the whole table.
Ideas, anybody?
MyISAM vs. InnoDB behave identically.
4.0 or 4.1.5 also didn't make a difference; I didn't test 5.0, as this
is supposed to be a production system.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index not used for select distinct?
[snip]
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate
latin1_german1_ci NOT NULL default 'WARN',
`epoch` int(10) unsigned NOT NULL default '0',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
Ideas, anybody?
[/snip]
Because IP is not an INDEX, only a KEY, just a guess...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index not used for select distinct?
Matthias Urlichs wrote:
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci NOT NULL
default 'WARN',
`epoch` int(10) unsigned NOT NULL default '0',
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
Your second key is redundant.
Your Key file is larger than your data file.
It would have to scan the whole keyfile anyway, so it is faster to scan
the table.
)
... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.
However,
explain select distinct ip from test;
++-+---+---+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
++-+---+---+---+--+-+--+--+-+
| 1 | SIMPLE | test | index | NULL | IP | 15 | NULL | 10991123
| Using index |
++-+---+---+---+--+-+--+--+-+
takes a *long* time and obviously scans the whole table.
Ideas, anybody?
MyISAM vs. InnoDB behave identically.
4.0 or 4.1.5 also didn't make a difference; I didn't test 5.0, as this
is supposed to be a production system.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index not used for select distinct?
Hi,
gerald_clark:
KEY `Trap` (`IP`,`Type`,`epoch`),
KEY `IP` (`IP`)
Your second key is redundant.
True (there's a UNIQUE missing in there).
Deleting the first key, however, doesn't change anything.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
I had a similar problem, but my criteria for selecting the value of f1 was different; it's a date field and I wanted only the rows with the most recent date value in that field, so only the latest of otherwise identical entries got inserted. I ended up doing something like this: create temporary table temp_table ( t_f1 date, t_f2 varchar(100) unique, t_f3 varchar(100) unique ); insert ignore into temp_table select f1,f2,f3 from new_table order by f1 desc; insert into original_table select * from temp_table; If there is a better way to do this, I would like to know about it. - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
How about INSERT INTO original_table SELECT MAX(f1), f2, f3 FROM new_table GROUP BY f2, f3; Michael Stephen E. Bacher wrote: I had a similar problem, but my criteria for selecting the value of f1 was different; it's a date field and I wanted only the rows with the most recent date value in that field, so only the latest of otherwise identical entries got inserted. I ended up doing something like this: create temporary table temp_table ( t_f1 date, t_f2 varchar(100) unique, t_f3 varchar(100) unique ); insert ignore into temp_table select f1,f2,f3 from new_table order by f1 desc; insert into original_table select * from temp_table; If there is a better way to do this, I would like to know about it. - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL question, SELECT DISTINCT
say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? Are there other columns (beyond these 3) to move as well? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM: say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 11:36:32 -0400, [EMAIL PROTECTED] said: It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? I want the DISTINCT to ignore the f1 column completely. But I want all 3 cols. ie. the entire row selected though. Pretend that f1 is a unique PK it'll always be different, I want to DISTINCT to ignore it, so if 2 or mores row have dup f2 *and* f3 Distinct will let only one through. Are there other columns (beyond these 3) to move as well? Nope. Just 3 cols. Thanks Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM: say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- 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]
Re: SQL question, SELECT DISTINCT
Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Or, is there some other criteria you need to consider in order to populate the f1 column of the new table with a value from your old table? Or, will the new table provide its own value for the f1 column? If I look at the sample data I set up, I see 3 rows with a unique f2/f3 combination but you only want to move 1 of them to a new table... Which value from f1 do you want to keep and which 2 do you want to throw away during the move? You are eliminating f1 values by reducing how many times the f2/f3 combination appears in the new table. All I need from you is a method to decide which f1 to keep and the SQL writes itself (almost) ;-D Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 12:13:06 PM: On Mon, 16 Aug 2004 11:36:32 -0400, [EMAIL PROTECTED] said: It all depends on which values of f1 you want to ignore. f1 f2 f3 - - -- val1-1 val2 val3 val1-2 val2 val3 val1-3 val2 val3 Which value of f1 would you want in your new table? Which ones to ignore? I want the DISTINCT to ignore the f1 column completely. But I want all 3 cols. ie. the entire row selected though. Pretend that f1 is a unique PK it'll always be different, I want to DISTINCT to ignore it, so if 2 or mores row have dup f2 *and* f3 Distinct will let only one through. Are there other columns (beyond these 3) to move as well? Nope. Just 3 cols. Thanks Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 08/16/2004 11:29:33 AM: say I'm selecting distinct (non-duplicate) rows for insertion, insert into original_table select distinct * from new_table these tables have 3 fields/row. Per the above code all 3 fields are evaluated by distict * . But my question is: I want to ignore field1, therefore I only want to test if any rows have field2 *and* field3 as dups - that would be my distinct test and then do the insert based on that. Test f2 and f3, not f1. What the sql? Help is greatly appreciated. -- 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]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to text only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target table, leave it out of the SELECT, and let it auto-generate IDs. Something like this: INSERT INTO original_table (f2, f3) SELECT DISTINCT f2, f3 FROM new_table; I did that in the same order as your original message, though I'd have expected original_table and new_table to be swapped, based on their names. See the manual http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html for the details on INSERT...SELECT. Michael leegold wrote: On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question, SELECT DISTINCT
Disregard by last message it's a repeat. THANKS for the help! On Mon, 16 Aug 2004 14:32:27 -0400, Michael Stassen [EMAIL PROTECTED] said: Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target table, leave it out of the SELECT, and let it auto-generate IDs. Something like this: INSERT INTO original_table (f2, f3) SELECT DISTINCT f2, f3 FROM new_table; I did that in the same order as your original message, though I'd have expected original_table and new_table to be swapped, based on their names. See the manual http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html for the details on INSERT...SELECT. Michael leegold wrote: On Mon, 16 Aug 2004 13:57:13 -0400, Michael Stassen [EMAIL PROTECTED] said: You were perfectly clear. We understand that you only want to test f2 and f3 for uniqueness. The question is, which of the possible values of f1 do you want to get. Do you see? For a particular unique f2, f3 combination, there may be multiple f1 values. How should we choose which one to put in the new table? Oh, I understand now, sorry. If I said it makes no difference then you'd ask what the heck I have f1 for in the first place...It actually doesn't make a difference. Maybe I should drop f1. f1 is an auto-increment int. so I imagine I'd want f1 re-incremented in numerical order to take the gaps out. Not exactly normalized (or normal:^), thanks. That is what Shawn has asked twice, and you have not answered. Until you answer that, no one can provide a correct solution. Michael leegold wrote: On Mon, 16 Aug 2004 12:39:32 -0400, [EMAIL PROTECTED] said: Let me see if I can explain it a little betterIf you need to move all 3 columns to the new table but you only want *1* row where f2 and f3 have a unique combination of values, how do you want to choose *which* value of f1 to move over with that combination? Do you want the minimum value, the maximum value, or no value at all? Whoa, it's not that complicatedI want to test only f2 f3 for uniqueness, not f1 f2 f3. That's all. If I'm not making it clear - don't worry...it's not life or death. Thanks. ...snip... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT + ORDER BY confusion
If what you mean by most recent are the products with the latest 'post_date', try this: SELECT ID, title, max(s.post_date) as post_date FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 GROUP BY ID,title order by post_date desc LIMIT 10 You will have an extra column of data but you should not be required to display every column, are you? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Jon Drukman | | | [EMAIL PROTECTED]| | | m | | | Sent by: news| | | [EMAIL PROTECTED]| | | rg | | || | || | | 07/09/2004 04:08 | | | PM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: SELECT DISTINCT + ORDER BY confusion | | I've got a product story setup where there can be multiple stories of a given type for any product. I want to find the names of the products with the most-recently-posted stories of a certain type. This query works well: SELECT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 +++ | id | title | +++ | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | +++ however since there are multiple stories of the correct type for some of those products, i would like to dedupe the results and just get a unique list of products. however, if i use SELECT DISTINCT it applies that BEFORE it does the sort, so i don't get only the most recent products. what i actually get seems to be pretty random. SELECT DISTINCT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 ++---+ | id | title | ++---+ | 917958 | Port Royale 2 | | 920457 | Cuban Missile Crisis | | 915000 | Axis Allies | | 919602 | Blitzkrieg: Burning Horizon | | 914594 | SuperPower 2 | | 914911 | Kohan II: Kings of War| | 915017 | Sid Meier's Pirates! | | 918842 | Warlords Battlecry III| | 919973 | RollerCoaster Tycoon 3| | 920314 | Immortal Cities: Children of the Nile | ++---+ that's pretty messed up. really what i'd like is: +++ | id | title | +++ | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989
SELECT DISTINCT + ORDER BY confusion
I've got a product story setup where there can be multiple stories of a given type for any product. I want to find the names of the products with the most-recently-posted stories of a certain type. This query works well: SELECT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 +++ | id | title | +++ | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | +++ however since there are multiple stories of the correct type for some of those products, i would like to dedupe the results and just get a unique list of products. however, if i use SELECT DISTINCT it applies that BEFORE it does the sort, so i don't get only the most recent products. what i actually get seems to be pretty random. SELECT DISTINCT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 ++---+ | id | title | ++---+ | 917958 | Port Royale 2 | | 920457 | Cuban Missile Crisis | | 915000 | Axis Allies | | 919602 | Blitzkrieg: Burning Horizon | | 914594 | SuperPower 2 | | 914911 | Kohan II: Kings of War| | 915017 | Sid Meier's Pirates! | | 918842 | Warlords Battlecry III| | 919973 | RollerCoaster Tycoon 3| | 920314 | Immortal Cities: Children of the Nile | ++---+ that's pretty messed up. really what i'd like is: +++ | id | title | +++ | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 914468 | Perimeter | | 915000 | Axis Allies | | 914811 | The Sims 2 | | 918989 | The Lord of the Rings, The Battle for Middle-earth | +++ (i built this by hand just to demonstrate the desired outcome.) is there any way to do a post-order distinct? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT + ORDER BY confusion
Have you tried using a group by clause? Group by title -Original Message- From: news To: [EMAIL PROTECTED] Sent: 7/9/04 3:08 PM Subject: SELECT DISTINCT + ORDER BY confusion I've got a product story setup where there can be multiple stories of a given type for any product. I want to find the names of the products with the most-recently-posted stories of a certain type. This query works well: SELECT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 +++ | id | title | +++ | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | +++ however since there are multiple stories of the correct type for some of those products, i would like to dedupe the results and just get a unique list of products. however, if i use SELECT DISTINCT it applies that BEFORE it does the sort, so i don't get only the most recent products. what i actually get seems to be pretty random. SELECT DISTINCT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 ++---+ | id | title | ++---+ | 917958 | Port Royale 2 | | 920457 | Cuban Missile Crisis | | 915000 | Axis Allies | | 919602 | Blitzkrieg: Burning Horizon | | 914594 | SuperPower 2 | | 914911 | Kohan II: Kings of War| | 915017 | Sid Meier's Pirates! | | 918842 | Warlords Battlecry III| | 919973 | RollerCoaster Tycoon 3| | 920314 | Immortal Cities: Children of the Nile | ++---+ that's pretty messed up. really what i'd like is: +++ | id | title | +++ | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 914468 | Perimeter | | 915000 | Axis Allies | | 914811 | The Sims 2 | | 918989 | The Lord of the Rings, The Battle for Middle-earth | +++ (i built this by hand just to demonstrate the desired outcome.) is there any way to do a post-order distinct? -jsd- -- 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]
Re: SELECT DISTINCT + ORDER BY confusion
Victor Pendleton wrote: Have you tried using a group by clause? Group by title same problem - the group by happens before the order by and you get essentially random results. -Original Message- From: news To: [EMAIL PROTECTED] Sent: 7/9/04 3:08 PM Subject: SELECT DISTINCT + ORDER BY confusion I've got a product story setup where there can be multiple stories of a given type for any product. I want to find the names of the products with the most-recently-posted stories of a certain type. This query works well: SELECT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 +++ | id | title | +++ | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | +++ however since there are multiple stories of the correct type for some of those products, i would like to dedupe the results and just get a unique list of products. however, if i use SELECT DISTINCT it applies that BEFORE it does the sort, so i don't get only the most recent products. what i actually get seems to be pretty random. SELECT DISTINCT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 ++---+ | id | title | ++---+ | 917958 | Port Royale 2 | | 920457 | Cuban Missile Crisis | | 915000 | Axis Allies | | 919602 | Blitzkrieg: Burning Horizon | | 914594 | SuperPower 2 | | 914911 | Kohan II: Kings of War| | 915017 | Sid Meier's Pirates! | | 918842 | Warlords Battlecry III| | 919973 | RollerCoaster Tycoon 3| | 920314 | Immortal Cities: Children of the Nile | ++---+ that's pretty messed up. really what i'd like is: +++ | id | title | +++ | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 914468 | Perimeter | | 915000 | Axis Allies | | 914811 | The Sims 2 | | 918989 | The Lord of the Rings, The Battle for Middle-earth | +++ (i built this by hand just to demonstrate the desired outcome.) is there any way to do a post-order distinct? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT + ORDER BY confusion
If you are usign 4.1 you could try: SELECT DISTINCT d, title FROM (select p.id, p.title from product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc ) limit 10 otherwise: select p.id, p.title from product p join e_prod ep on ep.product = p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 group by p.id, p.title order by p.title limit 10 --- Victor Pendleton [EMAIL PROTECTED] wrote: Have you tried using a group by clause? Group by title -Original Message- From: news To: [EMAIL PROTECTED] Sent: 7/9/04 3:08 PM Subject: SELECT DISTINCT + ORDER BY confusion I've got a product story setup where there can be multiple stories of a given type for any product. I want to find the names of the products with the most-recently-posted stories of a certain type. This query works well: SELECT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 +++ | id | title | +++ | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | +++ however since there are multiple stories of the correct type for some of those products, i would like to dedupe the results and just get a unique list of products. however, if i use SELECT DISTINCT it applies that BEFORE it does the sort, so i don't get only the most recent products. what i actually get seems to be pretty random. SELECT DISTINCT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 ++---+ | id | title | ++---+ | 917958 | Port Royale 2 | | 920457 | Cuban Missile Crisis | | 915000 | Axis Allies | | 919602 | Blitzkrieg: Burning Horizon | | 914594 | SuperPower 2 | | 914911 | Kohan II: Kings of War| | 915017 | Sid Meier's Pirates! | | 918842 | Warlords Battlecry III| | 919973 | RollerCoaster Tycoon 3| | 920314 | Immortal Cities: Children of the Nile | ++---+ that's pretty messed up. really what i'd like is: +++ | id | title | +++ | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 914468 | Perimeter | | 915000 | Axis Allies | | 914811 | The Sims 2 | | 918989 | The Lord of the Rings, The Battle for Middle-earth | +++ (i built this by hand just to demonstrate the desired outcome.) is there any way to do a post-order distinct? -jsd- -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http
Re: [SPAM]Re: e: Select distinct year from unix timestamp
From: Paul DuBois At 17:50 -0500 5/16/04, Paul DuBois wrote: Not a huge difference, I guess. But I suppose if a query that uses one or the other of these expressions processes a large number of rows, it might pay to run some comparative testing. Another interesting point is whether one timestamp format is to be preferred over the other in terms of performance of the operations to be done on it. The OP should be able to do this testing with the mechanism you demonstrated. John -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select distinct year from unix timestamp
Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What am I doing wrong? TNX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct year from unix timestamp
R u sure your printing out the correct array (hash) field? Did you connect to the db? Is mysql_error reporting an error? DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of T. H. Grejc Sent: Sunday, May 16, 2004 11:36 AM To: [EMAIL PROTECTED] Subject: Select distinct year from unix timestamp Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What am I doing wrong? TNX -- 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]
Re: Select distinct year from unix timestamp
From: T. H. Grejc Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What am I doing wrong? TNX I think you need this function FROM_UNIXTIME(unix_timestamp,format). Year does not operate on a unix timestamp. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct year from unix timestamp
At 22:27 +0200 5/16/04, John Fawcett wrote: From: T. H. Grejc Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What am I doing wrong? TNX I think you need this function FROM_UNIXTIME(unix_timestamp,format). Year does not operate on a unix timestamp. Sure it does: mysql select t, year(t) from tsdemo1; ++-+ | t | year(t) | ++-+ | 20010822133241 |2001 | | 20010822133241 |2001 | ++-+ The problem lies elsewhere. Where that might be is difficult to say, because no PHP code has been shown yet. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct year from unix timestamp
John Fawcett wrote: From: T. H. Grejc Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What am I doing wrong? TNX I think you need this function FROM_UNIXTIME(unix_timestamp,format). Year does not operate on a unix timestamp. Working like a charm. My query now is: SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M') FROM table_name ORDER BY created DESC How can I add more fields to query. If I write: SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM table_name ORDER BY created DESC I loose distinction (all dates are displayed). TNX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct year from unix timestamp
Paul DuBois wrote: At 22:27 +0200 5/16/04, John Fawcett wrote: From: T. H. Grejc Hello, I'm trying to select all distinct years from a unixtimestamp field in MySQL database (3.23.56). I have a query: SELECT DISTINCT YEAR(date_field) As theYear FROM table but PHP gives me an empty array. What am I doing wrong? TNX I think you need this function FROM_UNIXTIME(unix_timestamp,format). Year does not operate on a unix timestamp. Sure it does: mysql select t, year(t) from tsdemo1; ++-+ | t | year(t) | ++-+ | 20010822133241 |2001 | | 20010822133241 |2001 | ++-+ The problem lies elsewhere. Where that might be is difficult to say, because no PHP code has been shown yet. My DateField is in '1084415895' form, not in the '20010822133241' form, and the PHP is only regular mysql_fetch_array, then while loop, nothing special. Is it maybe better to use 20010822133241 instead of unix_timestamp. I have allways used Unix timestamps, but never realy done any big date calculations. Is mysql TIMESTAMP(14) more flexibile that Unix timestamp. TNX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct year from unix timestamp
From: T. H. Grejc How can I add more fields to query. If I write: SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM table_name ORDER BY created DESC I loose distinction (all dates are displayed). TNX I don't think distinction is lost. All the rows should still be distinct (considered in their entirity). What are you expecting to see as a result? John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM]Re: Select distinct year from unix timestamp
From: Paul DuBois At 22:27 +0200 5/16/04,
John Fawcett wrote:
Year does not operate on a unix timestamp.
Sure it does:
mysql select t, year(t) from tsdemo1;
++-+
| t | year(t) |
++-+
| 20010822133241 |2001 |
| 20010822133241 |2001 |
++-+
The problem lies elsewhere. Where that might be is difficult to
say, because no PHP code has been shown yet.
The first column doesn't look like a unix timestamp. It should be expressed
in seconds since 1970-01-01 00:00:00 GMT.
Try this to get the unix timestamp of the above date. Year shouldn't work on
it.
SELECT UNIX_TIMESTAMP('2001-08-22 13:32:41');
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct year from unix timestamp
John Fawcett wrote: From: T. H. Grejc How can I add more fields to query. If I write: SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM table_name ORDER BY created DESC I loose distinction (all dates are displayed). TNX I don't think distinction is lost. All the rows should still be distinct (considered in their entirity). Yes, but if I add any oter field into query I get all dates as a result (56, january's, 48 February's etc) What are you expecting to see as a result? I'm creating news archive and it should be sorted by months: January 2004 (news count is 56) February 2004 (48) ... Like ususal weblogs archives. You can see example at http://weblogs.mozillazine.org/ben/ an then check 'ARCHIVES' at the right, just below the search. TNX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM]Re: Select distinct year from unix timestamp
At 0:25 +0200 5/17/04, John Fawcett wrote:
From: Paul DuBois At 22:27 +0200 5/16/04,
John Fawcett wrote:
Year does not operate on a unix timestamp.
Sure it does:
mysql select t, year(t) from tsdemo1;
++-+
| t | year(t) |
++-+
| 20010822133241 |2001 |
| 20010822133241 |2001 |
++-+
The problem lies elsewhere. Where that might be is difficult to
say, because no PHP code has been shown yet.
The first column doesn't look like a unix timestamp.
Sorry. It's a TIMESTAMP column.
It should be expressed
in seconds since 1970-01-01 00:00:00 GMT.
Try this to get the unix timestamp of the above date. Year shouldn't work on
it.
SELECT UNIX_TIMESTAMP('2001-08-22 13:32:41');
You're right. You'd have to apply YEAR() to
FROM_UNIXTIME(UNIX_TIMESTAMP(arg)).
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: e: Select distinct year from unix timestamp
From: Paul DuBois You're right. You'd have to apply YEAR() to FROM_UNIXTIME(UNIX_TIMESTAMP(arg)). and you can avoid YEAR() altogether by using a format string. in FROM_UNIXTIME() John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct year from unix timestamp
From: T. H. Grejc I'm creating news archive and it should be sorted by months: January 2004 (news count is 56) February 2004 (48) ... So you need to use GROUP BY and COUNT. The format is like this: select monthandyear,count(othercolumn) from t group by monthandyear in your case monthandyear must be replaced by your function which extracts month and year from the unix timestamp column (in both the select and group by). I think that's what you needed. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: e: Select distinct year from unix timestamp
At 0:38 +0200 5/17/04, John Fawcett wrote: From: Paul DuBois You're right. You'd have to apply YEAR() to FROM_UNIXTIME(UNIX_TIMESTAMP(arg)). and you can avoid YEAR() altogether by using a format string. in FROM_UNIXTIME() Right again. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: e: Select distinct year from unix timestamp
At 17:50 -0500 5/16/04, Paul DuBois wrote: At 0:38 +0200 5/17/04, John Fawcett wrote: From: Paul DuBois You're right. You'd have to apply YEAR() to FROM_UNIXTIME(UNIX_TIMESTAMP(arg)). and you can avoid YEAR() altogether by using a format string. in FROM_UNIXTIME() Right again. :-) I was curious about which might be faster, and got these results on a 2.8GHz Pentium IV with MySQL 4.1.2 on Linux: mysql select benchmark(1000,from_unixtime(unix_timestamp(),'%Y')); +--+ | benchmark(1000,from_unixtime(unix_timestamp(),'%Y')) | +--+ |0 | +--+ 1 row in set (11.50 sec) mysql select benchmark(1000,year(from_unixtime(unix_timestamp(; +---+ | benchmark(1000,year(from_unixtime(unix_timestamp( | +---+ | 0 | +---+ 1 row in set (8.72 sec) On a 900MHz PowerPC G3 with MySQL 4.0.19 on Mac OS X: mysql select benchmark(1000,from_unixtime(unix_timestamp(),'%Y')); +--+ | benchmark(1000,from_unixtime(unix_timestamp(),'%Y')) | +--+ |0 | +--+ 1 row in set (55.17 sec) mysql select benchmark(1000,year(from_unixtime(unix_timestamp(; +---+ | benchmark(1000,year(from_unixtime(unix_timestamp( | +---+ | 0 | +---+ 1 row in set (36.39 sec) Not a huge difference, I guess. But I suppose if a query that uses one or the other of these expressions processes a large number of rows, it might pay to run some comparative testing. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select Distinct question
Hello List, Please forgive this rather lengthy post. I thought I had something worked out to answer Rob's question but when I put it to the test I found what MAY be a bug in 4.1.1a-alpha-nt-log. Here is what I did. I created two tables, tablea and tableb. Here are their defs: mysql show create table tablea \G *** 1. row *** Table: tablea Create Table: CREATE TABLE `tablea` ( `IP` varchar(16) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql show create table tableb \G *** 1. row *** Table: tableb Create Table: CREATE TABLE `tableb` ( `IP` varchar(16) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) I then populated them with sample data. I used odd IP addresses for tablea and even IP addresses for tableb except for .3 which exists in both. I created multiple rows for each IP to simulate a set of (extremely simplified) web server logs. Here are the table dumps: mysql select * from tablea; +-+ | IP | +-+ | 192.168.1.1 | | 192.168.1.1 | | 192.168.1.1 | | 192.168.1.3 | | 192.168.1.3 | | 192.168.1.3 | | 192.168.1.5 | | 192.168.1.5 | | 192.168.1.5 | | 192.168.1.3 | +-+ 10 rows in set (0.00 sec) mysql select * from tableb; +-+ | IP | +-+ | 192.168.1.2 | | 192.168.1.2 | | 192.168.1.2 | | 192.168.1.4 | | 192.168.1.4 | | 192.168.1.4 | | 192.168.1.6 | | 192.168.1.6 | | 192.168.1.6 | | 192.168.1.8 | | 192.168.1.8 | | 192.168.1.8 | | 192.168.1.3 | | 192.168.1.3 | +-+ 14 rows in set (0.00 sec) What I was attempting to do was to try to work around the lack of FULL OUTER JOIN capability in order to help Rob find out how many unique IP addresses exist between the two tables. I crafted the following statement: SELECT COUNT(DISTINCT IFNULL(a.ip, c.ip)) as IP_Count FROM tablea a LEFT JOIN tableb b ON b.ip = a.ip RIGHT JOIN tableb c ON c.ip = a.ip; The answer I got back was 3.. h. To try to see what was being counted distinctly I rewrote the query to read: SELECT a.ip as a, b.ip as b, c.ip as b FROM tablea a LEFT JOIN tableb b ON b.ip = a.ip RIGHT JOIN tableb c ON c.ip = a.ip; and got 148 rows. What had actually happened was the query engine created a cartesian product of the two tables. Here is a partial dump of the output: +-+-+-+ | a | b | c | +-+-+-+ | 192.168.1.1 | NULL| 192.168.1.2 | - clipped for space - | 192.168.1.5 | NULL| 192.168.1.8 | | 192.168.1.5 | NULL| 192.168.1.8 | | 192.168.1.5 | NULL| 192.168.1.8 | | 192.168.1.3 | NULL| 192.168.1.8 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | +-+-+-+ 148 rows in set (0.00 sec) All of the rows that were clipped were combinations of tablea and tableb values with the b column always coming back as null. Column a and column c were never NULL. Here is why I think it's a bug. If we break down the FROM clause we get two situations. I will refer to the tables by their aliases so that the physical names don't get confusing: FROM tablea A LEFT JOIN tableb B ON B.ip = A.ip and FROM tablea A RIGHT JOIN tableb C ON C.ip = B.ip; If A left joins to B first we get a set that consists of all of A's rows with some colmns of B data populated in where they match (8 rows of B data thanks to the duplicate matches on 192.168.1.3). If we then call that result set T and right join C to T on a.ip (of T) matching c.ip we will get all of C's rows with a few of the columns from T populated where they match. Shouldn't I have
Re: SELECT DISTINCT returns an incorrect result with special characters
I've solved the problem starting the server using the option --character-set=dos. The side effect is that searches become accent-sensitive. Best wishes. --- Ricardo [EMAIL PROTECTED] escreveu: I have a problem with SELECT DISTINCT if the target field contains special characters. Example: select MyField from MyTable +--+ | MyField | +--+ | fá | | Fá | | fa | | Fa | | fâ | | Fâ | | fã | | Fã | +--+ select distinct MyField from MyTable +--+ | MyField | +--+ | fá | +--+ MyField is varchar type and MyTable is InnoDB. Is there any way to change this behaviour in MySQL? I use version 4.0.16. Thanks. __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special char acters
At 23:28 +0200 4/28/04, Jochem van Dieten wrote: Ricardo wrote: Which collation are you using in MS SQL Server? And in MySQL? MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 The charset determines which characters can occur. The collation determines the sort order. What is the collation in MySQL? http://dev.mysql.com/doc/mysql/en/Charset-server.html That's for 4.1 and up. There isn't such a concept of collation in 4.0.x. Ricardo, you might try SELECT DISTINCT BINARY col_name rather than SELECT DISTINCT col_name. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT returns an incorrect result with special characters
I have a problem with SELECT DISTINCT if the target field contains special characters. Example: select MyField from MyTable +--+ | MyField | +--+ | fá | | Fá | | fa | | Fa | | fâ | | Fâ | | fã | | Fã | +--+ select distinct MyField from MyTable +--+ | MyField | +--+ | fá | +--+ MyField is varchar type and MyTable is InnoDB. Is there any way to change this behaviour in MySQL? I use version 4.0.16. Thanks. __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT returns an incorrect result with special char acters
Try SELECT DISTINCT BINARY(col) ... Or declare the column as binary -Original Message- From: Ricardo To: [EMAIL PROTECTED] Sent: 4/28/04 9:02 AM Subject: SELECT DISTINCT returns an incorrect result with special characters I have a problem with SELECT DISTINCT if the target field contains special characters. Example: select MyField from MyTable +--+ | MyField | +--+ | fá | | Fá | | fa | | Fa | | fâ | | Fâ | | fã | | Fã | +--+ select distinct MyField from MyTable +--+ | MyField | +--+ | fá | +--+ MyField is varchar type and MyTable is InnoDB. Is there any way to change this behaviour in MySQL? I use version 4.0.16. Thanks. __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- 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]
RE: SELECT DISTINCT returns an incorrect result with special char acters
Thanks Mr. Pendleton. But I cannot change the behaviour of the LIKE operator and the ORDER BY clause, which are going to be affected by both of your suggestions. --- Victor Pendleton [EMAIL PROTECTED] escreveu: Try SELECT DISTINCT BINARY(col) ... Or declare the column as binary __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special characters
Ricardo [EMAIL PROTECTED] wrote: I have a problem with SELECT DISTINCT if the target field contains special characters. Example: select MyField from MyTable +--+ | MyField | +--+ | f? | | F? | | fa | | Fa | | f? | | F? | | f? | | F? | +--+ select distinct MyField from MyTable +--+ | MyField | +--+ | f? | +--+ MyField is varchar type and MyTable is InnoDB. Is there any way to change this behaviour in MySQL? I use version 4.0.16. What is the character set of the data? What is the character set of MySQL server? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT returns an incorrect result with special char acters
Why can you not write SELECT DISTINCT BINARY(col) FROM table1 WHERE BINARY(col) LIKE 'criteria' ORDER BY col ? -Original Message- From: Ricardo To: Victor Pendleton; [EMAIL PROTECTED] Sent: 4/28/04 9:33 AM Subject: RE: SELECT DISTINCT returns an incorrect result with special char acters Thanks Mr. Pendleton. But I cannot change the behaviour of the LIKE operator and the ORDER BY clause, which are going to be affected by both of your suggestions. --- Victor Pendleton [EMAIL PROTECTED] escreveu: Try SELECT DISTINCT BINARY(col) ... Or declare the column as binary __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT returns an incorrect result with special char acters
- The sort order gets incorrect: SELECT DISTINCT BINARY(MyField) FROM MyTable WHERE MyField LIKE 'f%' ORDER BY MyField +-+ | BINARY(MyField) | +-+ | fa | | Fâ | | fá | | Fa | | fã | | Fá | | fâ | | Fã | +-+ - The like operator gets case-sensitive: SELECT DISTINCT BINARY(MyField) FROM MyTable WHERE BINARY(MyField) LIKE 'f%' ORDER BY MyField +-+ | BINARY(MyField) | +-+ | fâ | | fá | | fã | | fa | +-+ --- Victor Pendleton [EMAIL PROTECTED] escreveu: Why can you not write SELECT DISTINCT BINARY(col) FROM table1 WHERE BINARY(col) LIKE 'criteria' ORDER BY col ? __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special characters
latin1 --- Egor Egorov [EMAIL PROTECTED] escreveu: What is the character set of the data? What is the character set of MySQL server? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT returns an incorrect result with special char acters
It does not appear to respect the ascii values. Should the order be? fa fá fâ fã fa fá fâ fã -Original Message- From: Ricardo To: [EMAIL PROTECTED] Sent: 4/28/04 10:05 AM Subject: RE: SELECT DISTINCT returns an incorrect result with special char acters - The sort order gets incorrect: SELECT DISTINCT BINARY(MyField) FROM MyTable WHERE MyField LIKE 'f%' ORDER BY MyField +-+ | BINARY(MyField) | +-+ | fa | | Fâ | | fá | | Fa | | fã | | Fá | | fâ | | Fã | +-+ - The like operator gets case-sensitive: SELECT DISTINCT BINARY(MyField) FROM MyTable WHERE BINARY(MyField) LIKE 'f%' ORDER BY MyField +-+ | BINARY(MyField) | +-+ | fâ | | fá | | fã | | fa | +-+ --- Victor Pendleton [EMAIL PROTECTED] escreveu: Why can you not write SELECT DISTINCT BINARY(col) FROM table1 WHERE BINARY(col) LIKE 'criteria' ORDER BY col ? __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- 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]
RE: SELECT DISTINCT returns an incorrect result with special char acters
The order should be: fa Fa fá Fá fâ Fâ fã Fã As I get in MS-SQL Server. --- Victor Pendleton [EMAIL PROTECTED] escreveu: It does not appear to respect the ascii values. Should the order be? fa fá fâ fã fa fá fâ fã __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special char acters
Ricardo wrote: The order should be: fa Fa fá Fá fâ Fâ fã Fã As I get in MS-SQL Server. Which collation are you using in MS SQL Server? And in MySQL? Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special char acters
Which collation are you using in MS SQL Server? And in MySQL? Jochem MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special char acters
Ricardo wrote: Which collation are you using in MS SQL Server? And in MySQL? MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 The charset determines which characters can occur. The collation determines the sort order. What is the collation in MySQL? http://dev.mysql.com/doc/mysql/en/Charset-server.html Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special characters
But I think the collation set only applies to MySQL 4.1, doesn't it? I'm using MySQL 4.0.16. I found no character_set_server system variable. Only character_set. http://dev.mysql.com/doc/mysql/en/Charset-map.html Thanks. --- Jochem van Dieten [EMAIL PROTECTED] escreveu: Ricardo wrote: Which collation are you using in MS SQL Server? And in MySQL? MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 The charset determines which characters can occur. The collation determines the sort order. What is the collation in MySQL? http://dev.mysql.com/doc/mysql/en/Charset-server.html Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT.. ORDER BY.. DESC - bug??
After a bit of digging, found out that this is indeed a bug: http://bugs.mysql.com/bug.php?id=1274 Strangely enough, it is listed as CLOSED. Now - this is hard to explain, but it looks like MySQL developers have no answer or interest in dealing with this problem. The fact is - adding an index improves SELECT .. ORDER BY .. ASC, but slows down SELECT.. ORDER BY .. DESC at least by a factor of 10. If this is not a bug, what is it? Vadim P. wrote: Hi all, Sorry to be so persistent, but I am bringing this up again since noone from the MySQL development team commented on my previous post, and the issue seems very serious, to the point I may start looking to switching away from MySQL, so - please, please, shed some light on this issue!!! The problem is that the performance of SELECT DISTINCT... query seems to depend on the order the results are sorted, DESC being more than 10x slower than ASC (14.77 sec vs. 1.06 sec). == Here is a more detailed description: The table has over 700,000 records. MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle DESC; +--+ | billingCycle | +--+ | 2004-04-01 | | 2004-03-01 | | 2004-02-01 | | 2004-01-01 | | 2003-12-01 | | 2003-11-01 | | 2003-10-01 | | 2003-09-01 | | 2003-08-01 | | 2003-07-01 | | 2003-06-01 | | 2003-05-01 | | 2003-04-01 | | 2003-01-01 | +--+ 14 rows in set (14.77 sec) mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; +--+ | billingCycle | +--+ | 2003-01-01 | | 2003-04-01 | | 2003-05-01 | | 2003-06-01 | | 2003-07-01 | | 2003-08-01 | | 2003-09-01 | | 2003-10-01 | | 2003-11-01 | | 2003-12-01 | | 2004-01-01 | | 2004-02-01 | | 2004-03-01 | | 2004-04-01 | +--+ 14 rows in set (1.06 sec) === Thanks in advance! Vadim. mysql query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT.. ORDER BY.. DESC - bug??
Hi all, Sorry to be so persistent, but I am bringing this up again since noone from the MySQL development team commented on my previous post, and the issue seems very serious, to the point I may start looking to switching away from MySQL, so - please, please, shed some light on this issue!!! The problem is that the performance of SELECT DISTINCT... query seems to depend on the order the results are sorted, DESC being more than 10x slower than ASC (14.77 sec vs. 1.06 sec). == Here is a more detailed description: The table has over 700,000 records. MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle DESC; +--+ | billingCycle | +--+ | 2004-04-01 | | 2004-03-01 | | 2004-02-01 | | 2004-01-01 | | 2003-12-01 | | 2003-11-01 | | 2003-10-01 | | 2003-09-01 | | 2003-08-01 | | 2003-07-01 | | 2003-06-01 | | 2003-05-01 | | 2003-04-01 | | 2003-01-01 | +--+ 14 rows in set (14.77 sec) mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; +--+ | billingCycle | +--+ | 2003-01-01 | | 2003-04-01 | | 2003-05-01 | | 2003-06-01 | | 2003-07-01 | | 2003-08-01 | | 2003-09-01 | | 2003-10-01 | | 2003-11-01 | | 2003-12-01 | | 2004-01-01 | | 2004-02-01 | | 2004-03-01 | | 2004-04-01 | +--+ 14 rows in set (1.06 sec) === Thanks in advance! Vadim. mysql query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select distinct from two columns
Hello, The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. Any ideas? Thanks in advance. __ New! Unlimited Access from the Netscape Internet Service. Beta test the new Netscape Internet Service for only $1.00 per month until 3/1/04. Sign up today at http://isp.netscape.com/register Act now to get a personalized email address! Netscape. Just the Net You Need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select distinct from two columns
* Veysel Harun Sahin The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. You can use the GROUP BY clause with two columns: SELECT name,city,count(*) FROM my_table GROUP BY name,city The count(*) will give you a count of how many rows have each name/city combination. If you don't need it simply remove it, the GROUP BY should work anyway. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select distinct from two columns - solved
:) I have solved the problem. Thanks. [EMAIL PROTECTED] (Veysel Harun Sahin) wrote: Hello, The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. Any ideas? Thanks in advance. __ New! Unlimited Access from the Netscape Internet Service. Beta test the new Netscape Internet Service for only $1.00 per month until 3/1/04. Sign up today at http://isp.netscape.com/register Act now to get a personalized email address! Netscape. Just the Net You Need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ New! Unlimited Access from the Netscape Internet Service. Beta test the new Netscape Internet Service for only $1.00 per month until 3/1/04. Sign up today at http://isp.netscape.com/register Act now to get a personalized email address! Netscape. Just the Net You Need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select distinct from two columns
select distinct(col1,col2) should work.. Group by most certainly will... Select col1,col2 from table group by col1, col2.. Same thing Hello, The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select distinct from two columns
Hi, try to use group by clause in your select, e.g. select name, city from mytable group by name, city order by name, city; Best regards, Mikhail. - Original Message - From: Veysel Harun Sahin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 02, 2004 4:42 PM Subject: select distinct from two columns Hello, The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. Any ideas? Thanks in advance. __ New! Unlimited Access from the Netscape Internet Service. Beta test the new Netscape Internet Service for only $1.00 per month until 3/1/04. Sign up today at http://isp.netscape.com/register Act now to get a personalized email address! Netscape. Just the Net You Need. -- 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]
Re: Variation of SELECT DISTINCT
Alan Dickinson [EMAIL PROTECTED] wrote:
I've got a query that looks like this..
SELECT Foot_Id, Dir_Uni, Part_Suffix, Part_Number, Appln_No, Description,
Product_Ref, Vehicle_Ref, Part_Prompt, Part_Description, Foot_Id,
Qualifier, Years
FROM application_parts
WHERE (('$id' = Appln_No) and (Years = '$dropdown') and (Dir_Uni 'U')
and (Part_Description 'Pipe Kit')
and (Part_Description 'Universal Converter') and (Part_Description
'Pre Cat'))
ORDER BY Part_Prompt;
(sorry if the spacing is weird)
I need to drop the rows returned that have the same part_description field,
like the DISTINCT clause does for rows, i need the same thing for a field
value, how can I do this?
If I've got you right you need:
SELECT .. GROUP BY part_description
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Variation of SELECT DISTINCT
I've got a query that looks like this..
SELECT Foot_Id, Dir_Uni, Part_Suffix, Part_Number, Appln_No, Description,
Product_Ref, Vehicle_Ref, Part_Prompt, Part_Description, Foot_Id,
Qualifier, Years
FROM application_parts
WHERE (('$id' = Appln_No) and (Years = '$dropdown') and (Dir_Uni 'U')
and (Part_Description 'Pipe Kit')
and (Part_Description 'Universal Converter') and (Part_Description
'Pre Cat'))
ORDER BY Part_Prompt;
(sorry if the spacing is weird)
I need to drop the rows returned that have the same part_description field,
like the DISTINCT clause does for rows, i need the same thing for a field
value, how can I do this?
Thanks for the help,
Alan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT question
Hello everybody: I have the following scenario: I have a database of products, each of those products have a product number (like 04543000). Now the first five digits represent the product number, and the 2 following digits represent a differet color for the same product. Now I have to show the thumbnails of this products but only for one color. So my query should look something like this: SELECT DISTINCT(product_number) FROM products. Now this query will return all the different product numbers i.e. but it will also return the different colors as a different product number. Is there anyway to have a query like: SELECT DISTINCT(first_five_product_numbers) FROM products? I can find the solution to it. Maybe the solution will be more experience :) But maybe the solution can be the list. Thanks, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT question
SELECT DISTINCT(LEFT (product_number,5)) FROM products. http://www.mysql.com/doc/en/String_functions.html hth Jeff Personal [EMAIL PROTECTED]To: [EMAIL PROTECTED] .netcc: Subject: SELECT DISTINCT question 10/13/2003 12:38 PM Hello everybody: I have the following scenario: I have a database of products, each of those products have a product number (like 04543000). Now the first five digits represent the product number, and the 2 following digits represent a differet color for the same product. Now I have to show the thumbnails of this products but only for one color. So my query should look something like this: SELECT DISTINCT(product_number) FROM products. Now this query will return all the different product numbers i.e. but it will also return the different colors as a different product number. Is there anyway to have a query like: SELECT DISTINCT(first_five_product_numbers) FROM products? I can find the solution to it. Maybe the solution will be more experience :) But maybe the solution can be the list. Thanks, Hector -- 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]
RE: SELECT DISTINCT question (Thanks it worked)
Thank you!!! It works. Hector -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 13, 2003 1:44 PM To: Personal Cc: [EMAIL PROTECTED] Subject: Re: SELECT DISTINCT question SELECT DISTINCT(LEFT (product_number,5)) FROM products. http://www.mysql.com/doc/en/String_functions.html hth Jeff Personal [EMAIL PROTECTED]To: [EMAIL PROTECTED] .netcc: Subject: SELECT DISTINCT question 10/13/2003 12:38 PM Hello everybody: I have the following scenario: I have a database of products, each of those products have a product number (like 04543000). Now the first five digits represent the product number, and the 2 following digits represent a differet color for the same product. Now I have to show the thumbnails of this products but only for one color. So my query should look something like this: SELECT DISTINCT(product_number) FROM products. Now this query will return all the different product numbers i.e. but it will also return the different colors as a different product number. Is there anyway to have a query like: SELECT DISTINCT(first_five_product_numbers) FROM products? I can find the solution to it. Maybe the solution will be more experience :) But maybe the solution can be the list. Thanks, Hector -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select distinct speed on an indexed column
Hey everyone, I have a question about the speed of selecting distinct values on an indexed column. I have a table with a five column primary key and 3,215,540 records. I want to select all of the distinct values of the first column in the primary key. This column only has 549 distinct values. To execute this query takes about a minute and a half on a P4 2.4G. I assume that mysql is doing a complete table scan thus making the query slow. My question follows. Shouldn't the distinct values of the first column in an index be pulled from the index itself instead from the actual data? Thanks for your insights! Nathan show table status; ---+-+++ ---+ | Name | Type | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++--++ -+-+--+---++ --- | PostedZpdi | InnoDB | Dynamic| 3215540 |678 | 2180988928 |NULL |530350080 | 0 | NULL | NULL | NULL | NULL || InnoDB free: 142368768 kB | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct speed on an indexed column
Nathan Cassano wrote: Hey everyone, I have a question about the speed of selecting distinct values on an indexed column. I have a table with a five column primary key and 3,215,540 records. I want to select all of the distinct values of the first column in the primary key. This column only has 549 distinct values. To execute this query takes about a minute and a half on a P4 2.4G. I assume that mysql is doing a complete table scan thus making the query slow. My question follows. Shouldn't the distinct values of the first column in an index be pulled from the index itself instead from the actual data? Thanks for your insights! Nathan show table status; ---+-+++ ---+ | Name | Type | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++--++ -+-+--+---++ --- | PostedZpdi | InnoDB | Dynamic| 3215540 |678 | 2180988928 |NULL |530350080 | 0 | NULL | NULL | NULL | NULL || InnoDB free: 142368768 kB | Hi, What do you get when you do an EXPLAIN of your query ? -- Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct speed on an indexed column
Its a compound key, they are always slow. I would imagin you will need to seriously redesign your database to speed that up. I'm not 100% sure how the index is stored, but it would be some what pointless if it was individual field values. Its like haveing field1field2feild3field4field5 so that a single comparison of the values tells you if its unique. You might be able to speed it up by putting a secondary index on the first field I'm curious as to why any one would design a database with that many feilds in the primary key? Maybe its me but that would just be wrong. I'm not to happy when I have 2 fields in the primary key - Original Message - From: Nathan Cassano [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 15, 2003 5:07 PM Subject: Select distinct speed on an indexed column : : Hey everyone, : I have a question about the speed of selecting distinct values on an : indexed column. I have a table with a five column primary key and 3,215,540 : records. I want to select all of the distinct values of the first column in : the primary key. This column only has 549 distinct values. To execute this : query takes about a minute and a half on a P4 2.4G. I assume that mysql is : doing a complete table scan thus making the query slow. : My question follows. Shouldn't the distinct values of the first : column in an index be pulled from the index itself instead from the actual : data? Thanks for your insights! : : Nathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct speed on an indexed column
mysql explain select distinct AccountLevelId from PostedZpdi; ++---+---+-+-+--+-+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++---+---+-+-+--+-+- + | PostedZpdi | index | NULL | PRIMARY | 60 | NULL | 3447290 | Using index | ++---+---+-+-+--+-+- + 1 row in set (0.01 sec) Hi, What do you get when you do an EXPLAIN of your query ? -- Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select distinct speed on an indexed column
Since your primary key is used, maybe you should consider adding an index on PostedZpdi field only. You may also check 'key_buffer_size' value (show variables like 'key%'). Since your primary key is more than 200 MB big, allocating a big key buffer ( 256MB) may help. Joseph Bueno Nathan Cassano wrote: mysql explain select distinct AccountLevelId from PostedZpdi; ++---+---+-+-+--+-+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++---+---+-+-+--+-+- + | PostedZpdi | index | NULL | PRIMARY | 60 | NULL | 3447290 | Using index | ++---+---+-+-+--+-+- + 1 row in set (0.01 sec) Hi, What do you get when you do an EXPLAIN of your query ? -- Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct speed on an indexed column
From: Haydies [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 11:19 AM To: [EMAIL PROTECTED] Subject: Re: Select distinct speed on an indexed column Its a compound key, they are always slow. I would imagin you will need to seriously redesign your database to speed that up. I'm not 100% sure how the index is stored, but it would be some what pointless if it was individual field values. Its like haveing field1field2feild3field4field5 so that a single comparison of the values tells you if its unique. You might be able to speed it up by putting a secondary index on the first field Hmm... Well I tried adding and an additional index and that did not change the query speed. I'm curious as to why any one would design a database with that many feilds in the primary key? Maybe its me but that would just be wrong. I'm not to happy when I have 2 fields in the primary key I don't know. It's really out of my control. Maybe it's a Telecom thing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct speed on an indexed column
Well, it's an InnoDb database and has some decent memory pools. | innodb_additional_mem_pool_size | 33554432 | innodb_buffer_pool_size | 536870912 -Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED] Sent: Monday, September 15, 2003 11:47 AM To: Nathan Cassano Cc: '[EMAIL PROTECTED]' Subject: Re: Select distinct speed on an indexed column Since your primary key is used, maybe you should consider adding an index on PostedZpdi field only. You may also check 'key_buffer_size' value (show variables like 'key%'). Since your primary key is more than 200 MB big, allocating a big key buffer ( 256MB) may help. Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select distinct speed on an indexed column
Yeah, I have a similar box like yours. I copied the first column to a new table with an index. I ran select distinct and the query took 6 seconds to execute. This must have to do with the record length, because when I indexed the origional table's first column the query was 1 minute 30 seconds to select distinct. Do you think MySQL is scanning the entire index? Mike are you using ISAM or InnoDb? My tests were with InnoDb. I don't know why your Distincts are so slow. When I do a Select Distinct on 2 columns from my 2.7 million row table, I get back 256 distinct values in 5 seconds. The string columns are around 10 characters each. The column is indexed. I'm also using 2.4 g machine but wiht 1gbyte ram and 7200RPM 8m cache hard drives (8mb cache drives are faster than the conventional 2mb cache drives). Maybe it has to do with your record length? Try copying the column to a temporary table and index the column, then try your Select Distinct on that. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
