Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
Mike Spreitzer wrote: So I am trying to use mysqldump --tab for the first time. I am running into the same problem everybody does on their first try --- Errcode: 13. I have set the permissions on the target directory to be completely liberal --- anybody can do anything with it --- and I still get Errcode: 13. I can even write into that directory when logged in as mysql (UID 100, GID 100, and yes that is what the mysqld process is running as). What's going wrong here? SELinux enabled? What's the syntax of the command you're using? Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
I am running MySQL 5.0.51a-community on RedHat Enterprise Linux 4. Here is a further typescript showing the failure: [EMAIL PROTECTED] /]# mysqldump -u root -p --skip-opt --quick --tab=/dump1 wyky red1_p2 Enter password: mysqldump: Got error: 1: Can't create/write to file '/dump1/red1_p2.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' [EMAIL PROTECTED] /]# ls -ld /dump1 drwxrwxrwx 2 mysql mysql 4096 Feb 22 19:11 /dump1 [EMAIL PROTECTED] /]# ls -l /dump1 total 16 -rw-r--r-- 1 mysql mysql 29 Feb 22 18:38 foo.bar -rw-r--r-- 1 root root 742 Feb 22 19:11 red1_p2.sql [EMAIL PROTECTED] /]# Thanks, Mike Jed Reynolds [EMAIL PROTECTED] 02/22/08 07:04 PM Please respond to [EMAIL PROTECTED] To Mike Spreitzer/Watson/[EMAIL PROTECTED] cc mysql list mysql@lists.mysql.com Subject Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' Mike Spreitzer wrote: So I am trying to use mysqldump --tab for the first time. I am running into the same problem everybody does on their first try --- Errcode: 13. I have set the permissions on the target directory to be completely liberal --- anybody can do anything with it --- and I still get Errcode: 13. I can even write into that directory when logged in as mysql (UID 100, GID 100, and yes that is what the mysqld process is running as). What's going wrong here? SELinux enabled? What's the syntax of the command you're using? Jed
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
what happens when you delete the files that are already in there? Looks like you're dumping to a file owned by root. Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
Sorry if I have confused people by presenting evidence in the wrong order. Also, it seems that the .sql file is written by the client while the .txt file is (attempted to be) written by the server. Here is a single typescript with all the evidence: [EMAIL PROTECTED] ~]# cd / [EMAIL PROTECTED] /]# rm -f dump1/* [EMAIL PROTECTED] /]# ls -ld dump1 drwxrwxrwx 2 mysql mysql 4096 Feb 22 20:45 dump1 [EMAIL PROTECTED] /]# ls -l dump1 total 0 [EMAIL PROTECTED] /]# mysqldump -u root -p --skip-opt --quick --tab=/dump1 wyky red1_p2 Enter password: mysqldump: Got error: 1: Can't create/write to file '/dump1/red1_p2.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' [EMAIL PROTECTED] /]# ls -l dump1 total 8 -rw-r--r-- 1 root root 742 Feb 22 20:46 red1_p2.sql [EMAIL PROTECTED] /]# ps axlw | grep mysqld 0 0 8494 1 25 0 3408 944 wait Spts/5 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/xd026.watson.ibm.com.pid 4 100 8517 8494 16 0 251828 154772 - Sl ? 4:05 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/xd026.watson.ibm.com.pid --skip-external-locking 0 0 26228 26177 16 0 4704 672 pipe_w S+ pts/1 0:00 grep mysqld [EMAIL PROTECTED] /]# su - mysql -bash-3.00$ id uid=100(mysql) gid=101(mysql) groups=101(mysql) context=user_u:system_r:unconfined_t -bash-3.00$ cd /dump1 -bash-3.00$ date foo.bar -bash-3.00$ ls -l total 16 -rw-r--r-- 1 mysql mysql 29 Feb 22 20:46 foo.bar -rw-r--r-- 1 root root 742 Feb 22 20:46 red1_p2.sql -bash-3.00$ Thanks, Mike Jed Reynolds [EMAIL PROTECTED] 02/22/08 07:24 PM Please respond to [EMAIL PROTECTED] To Mike Spreitzer/Watson/[EMAIL PROTECTED] cc mysql list mysql@lists.mysql.com Subject Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' what happens when you delete the files that are already in there? Looks like you're dumping to a file owned by root. Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Got error: 1: Can't create/write to file 'dumpdir/tablename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
Mike Spreitzer wrote: Sorry if I have confused people by presenting evidence in the wrong order. Also, it seems that the .sql file is written by the client while the .txt file is (attempted to be) written by the server. Here is a single typescript with all the evidence: [EMAIL PROTECTED] ~]# cd / [EMAIL PROTECTED] /]# rm -f dump1/* [EMAIL PROTECTED] /]# ls -ld dump1 drwxrwxrwx 2 mysql mysql 4096 Feb 22 20:45 dump1 [EMAIL PROTECTED] /]# ls -l dump1 total 0 [EMAIL PROTECTED] /]# mysqldump -u root -p --skip-opt --quick --tab=/dump1 wyky red1_p2 Enter password: mysqldump: Got error: 1: Can't create/write to file '/dump1/red1_p2.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' I'm reading thru http://dev.mysql.com/doc/refman/5.0/en/select.html and it says that it will not write to an already existing file (so you deleted the old files) and you need the FILE privilege, and that it writes the file mode 777 as the user running the client (and suggests not using root). I would try $ rm -f /dump1/* $ echo SELECT * FROM red1_p2 INTO OUTFILE '/dump1/red1_p2.txt'; \ | mysql -u root -p to see if this is a mysql permissions issue. The above will create a file owned by the mysql process. You might try changing the directory to /tmp or /var/tmp to see if that makes some kind of magical difference. Might also check your grants for [EMAIL PROTECTED] Unlikely, but possible. Also, check /var/log/messages and if there's any SELinux warnings. You might have the option 'secure_file_priv' set? http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_secure-file-priv Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different [... ] (solved thankyou !)
Hello, thankyou to everyone who has helped me out on this one as I did not think it was actuallay possible ! :) This is what worked best for me : SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 0) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; I'm sorry if I was not clear with my first email yesterday making it sound like I wanted a 0 or a 1 and nothing else... I made a mistake and thought that I was getting nothing or 1 whereas it was actually counting corectly. I was also suggested a LEFT OUTER JOIN but have read that it is a synonym to LEFT JOIN, is this the case or is there a difference between the two? Thanks again, Richard David Schneider-Joseph a écrit : Try this one: SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 0) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; The LEFT JOIN will ensure you still get a result row even if there are no matching rows in `login_table`. And the COALESCE will give you a value of 0 instead of NULL for the count, in that case. On Feb 19, 2008, at 5:29 PM, Richard wrote: Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- 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]
group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Hi, and thankyou for trying to help me out! I've tried this and it does not work. Here are the problems : 1) If a user has never logged in he doesn't show the user in the list 2) It doesn't count if it is 0 it's not on the liste and if the user has logged in more than once the result is 1 (because of the group by ...). Thankyou Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; Donna Richard [EMAIL PROTECTED] 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ? Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Actually, this works too: SELECT a.username, a.first_name, a.last_name, Count(b.username) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; __ Try SELECT a.username, a.first_name, a.last_name, SUM(case when b.username is null then 0 else 1 end) as count FROM user_list a LEFT OUTER JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; Donna Richard [EMAIL PROTECTED] 02/19/2008 05:29 PM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ? Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Try this one: SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 0) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; The LEFT JOIN will ensure you still get a result row even if there are no matching rows in `login_table`. And the COALESCE will give you a value of 0 instead of NULL for the count, in that case. On Feb 19, 2008, at 5:29 PM, Richard wrote: Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different tables using result of first table to do the COUNT ... is it possible ?
Richard, 1) If a user has never logged in he doesn't show the user in the list 2) It doesn't count if it is 0 it's not on the liste and if the user has logged in more than once the result is 1 (because of the group by ...). Do you mean by #1 that you want to list all users whether they have logged in or not? #2 is less clear still; does it mean the query is to show a count of 0 for no logins and 1 for any positive number of logins? If so, try... SELECT a.username, a.first_name, a.last_name,COUNT(DISTINCT b.username) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB Richard wrote: Hi, and thankyou for trying to help me out! I've tried this and it does not work. Here are the problems : 1) If a user has never logged in he doesn't show the user in the list 2) It doesn't count if it is 0 it's not on the liste and if the user has logged in more than once the result is 1 (because of the group by ...). Thankyou Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A sql/select question.
table users iduser_iduser_name 1 M11 Shirley 2 M12 Bruce 3 M13 Fred 4 M14 Albert 5 M15 Elizabeth 6 T11 Helen 7 T12 Tracy 8 T13 Charles 9 T14 Jack 10 T15 Ann table job_records job_idsubmit_user_idsubmit_timefixed_user_id fixed_time SC12033214495468 T13 1203321449 M11 1203321763 SC12033215980303 T13 1203321598 M11 1203321788 SC12033216636547 T12 1203321663 M11 1203321796 SC12033216729280 T14 1203321672 M13 1203321803 SC12033216819810 T15 1203321681 M15 1203321809 SC12033216898223 T11 1203321689 M13 1203321816 How to select out data with below format? job_idsubmit_user_idsubmit_timefixed_user_id fixed_time SC12033214495468 Charles 1203321449 Shirley 1203321763 SC12033215980303 Charles 1203321598 Shirley 1203321788 SC12033216636547 Tracy 1203321663 Shirley 1203321796 SC12033216729280 Jack 1203321672 Fred 1203321803 SC12033216819810 Ann 1203321681 Elizabeth 1203321809 SC12033216898223 Helen 1203321689 Fred 1203321816 select t1.job_id, t2.user_name, t1.submit_time, t1.fixed_user_id, t1.fixed_time from job_records as t1 left join users as t2 on (t1.submit_user_id = t2.user_id); The output is, job_iduser_namesubmit_timefixed_user_idfixed_time SC12033214495468 Charles 1203321449 M11 1203321763 SC12033215980303 Charles 1203321598 M11 1203321788 SC12033216636547 Tracy 1203321663 M11 1203321796 SC12033216729280 Jack 1203321672 M13 1203321803 SC12033216819810 Ann 1203321681 M15 1203321809 SC12033216898223 Helen 1203321689 M13 1203321816 Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A sql/select question.
2008/2/18 King C. Kwok [EMAIL PROTECTED]: table users iduser_iduser_name 1 M11 Shirley 2 M12 Bruce 3 M13 Fred 4 M14 Albert 5 M15 Elizabeth 6 T11 Helen 7 T12 Tracy 8 T13 Charles 9 T14 Jack 10 T15 Ann table job_records job_idsubmit_user_idsubmit_timefixed_user_id fixed_time SC12033214495468 T13 1203321449 M11 1203321763 SC12033215980303 T13 1203321598 M11 1203321788 SC12033216636547 T12 1203321663 M11 1203321796 SC12033216729280 T14 1203321672 M13 1203321803 SC12033216819810 T15 1203321681 M15 1203321809 SC12033216898223 T11 1203321689 M13 1203321816 How to select out data with below format? job_idsubmit_user_idsubmit_timefixed_user_id fixed_time SC12033214495468 Charles 1203321449 Shirley 1203321763 SC12033215980303 Charles 1203321598 Shirley 1203321788 SC12033216636547 Tracy 1203321663 Shirley 1203321796 SC12033216729280 Jack 1203321672 Fred 1203321803 SC12033216819810 Ann 1203321681 Elizabeth 1203321809 SC12033216898223 Helen 1203321689 Fred 1203321816 select t1.job_id, t2.user_name, t1.submit_time, t1.fixed_user_id, t1.fixed_time from job_records as t1 left join users as t2 on (t1.submit_user_id = t2.user_id); The output is, job_iduser_namesubmit_timefixed_user_idfixed_time SC12033214495468 Charles 1203321449 M11 1203321763 SC12033215980303 Charles 1203321598 M11 1203321788 SC12033216636547 Tracy 1203321663 M11 1203321796 SC12033216729280 Jack 1203321672 M13 1203321803 SC12033216819810 Ann 1203321681 M15 1203321809 SC12033216898223 Helen 1203321689 M13 1203321816 Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] First off it is usally a good idea to explain exactly what is not working for you. The 'below format' is pretty ambiguous. It looks like all you need to do in order to get what you want is to join on the user table again and alias a few columns. select t1.job_id, t2.user_name as `submit_user_id`, t1.submit_time, t3.user_name as `fixed_user_id`, t1.fixed_time from job_records as t1 left join users as t2 on t1.submit_user_id = t2.user_id left join users as t3 on t1.fixed_user_id = t3.user_id; Is that what you are going for? If not, then please explain in a bit better detail what is problematic. -- Rob Wultsch
Re: A sql/select question.
Hi Rob, That is just what I need. I can't use 'join' very well yet. Thank you very much. -- King Kwok -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select unique ? (solved thankyou :))
Price, Randall a écrit : Since both of these work, I was wondering which one would be faster. Here is an EXPLAIN on a similar test I did on one of my test tables. (NO index on field1, WinXP, MySQL 5.0.41-community-nt, SQLyog query window) SELECT COUNT(*) FROM tblClients (1660 row(s) returned) (0 ms taken) RESET QUERY CACHE SELECT DISTINCT field1 FROM tblClients (130 row(s) returned) (0 ms taken) EXPLAIN SELECT DISTINCT field1 FROM tblClients /* 1457 rows, Using temporary */ RESET QUERY CACHE SELECT field1 FROM tblClients GROUP BY field1 (130 row(s) returned) (16 ms taken) EXPLAIN SELECT field1 FROM tblClients GROUP BY field1 /* 1457 rows, Using temporary; Using filesort */ It appears that the SELECT DISTINCT did not have to use the filesort. So that should be faster, which confirms what I see here. This is just my $0.02... Thanks, Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Thursday, February 14, 2008 11:57 AM To: Richard Cc: mysql@lists.mysql.com Subject: Re: select unique ? Try: SELECT DISTINCT Colour FROM table; Or, if you want to do it correctly: SELECT Colour FROM table GROUP BY color; Richard wrote: Hello, I don't know if it is possible to do this with mysql alone ... Here goes : I've got a database list which is like to following : Num|Name|Colour --- 1|Harry|Red 2|Tom|Blue 3|Jane|Green 4|Philip|Red 5|Sarah|Red 6|Robert|Blue And from this table I wish to get a list of used colours. The correct answer would be : Colour - Red Blue Green The answer I don't want : Colour Red Blue Green Red Red Blue How would I achieve the first result with mysql ? Is it possible? Thanks in advance, Richard Thanks :) It works great with the SELECT DISTINCT, and if it's faster than I will keep to this solution :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select unique ?
Hi, On Feb 15, 2008 10:26 AM, Price, Randall [EMAIL PROTECTED] wrote: Since both of these work, I was wondering which one would be faster. Here is an EXPLAIN on a similar test I did on one of my test tables. (NO index on field1, WinXP, MySQL 5.0.41-community-nt, SQLyog query window) SELECT COUNT(*) FROM tblClients (1660 row(s) returned) (0 ms taken) RESET QUERY CACHE SELECT DISTINCT field1 FROM tblClients (130 row(s) returned) (0 ms taken) EXPLAIN SELECT DISTINCT field1 FROM tblClients /* 1457 rows, Using temporary */ RESET QUERY CACHE SELECT field1 FROM tblClients GROUP BY field1 (130 row(s) returned) (16 ms taken) EXPLAIN SELECT field1 FROM tblClients GROUP BY field1 /* 1457 rows, Using temporary; Using filesort */ It appears that the SELECT DISTINCT did not have to use the filesort. So that should be faster, which confirms what I see here. That's because MySQL automatically sorts GROUP BY queries by the group-by columns. If you add ORDER BY NULL, the two queries should be exactly equivalent. But I'd just use DISTINCT :-) Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select with table name
Hi, I have a small issue that i can get my head around to solve: Is it possible to do a select from two tables using a union all (select * from t1 union all select * from t2), and have it display the table name in front of each row? What i need is, on the big resulting list, to know from which table the row came from. Example: Table: t1 --- id name --- 1 john 2 mary Table: t2 --- id name --- 1 paul 2 peter I need these results: id namefromtable 1 johnt1 2 maryt1 3 pault2 4 peter t2 Is this possible? If so, how? Thanks! Pag -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select with table name
At 12:04 AM + 2/17/08, Miguel Vaz wrote: Hi, I have a small issue that i can get my head around to solve: Is it possible to do a select from two tables using a union all (select * from t1 union all select * from t2), and have it display the table name in front of each row? mysql select 't1', t1.* from t1 union select 't2', t2.* from t2; ++--+ | t1 | i| ++--+ | t1 |1 | | t2 |2 | | t2 |3 | ++--+ -- 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 with table name
Can you just do SELECT t1.*, t1 as tablename FROM t1 UNION SELECT t2.*, t2 as tablename FROM t2 Roger -Original Message- From: Miguel Vaz [mailto:[EMAIL PROTECTED] Sent: 17 February 2008 00:04 To: mysql@lists.mysql.com Subject: select with table name Hi, I have a small issue that i can get my head around to solve: Is it possible to do a select from two tables using a union all (select * from t1 union all select * from t2), and have it display the table name in front of each row? What i need is, on the big resulting list, to know from which table the row came from. Example: Table: t1 --- id name --- 1 john 2 mary Table: t2 --- id name --- 1 paul 2 peter I need these results: id namefromtable 1 johnt1 2 maryt1 3 pault2 4 peter t2 Is this possible? If so, how? Thanks! Pag -- 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 unique ?
Since both of these work, I was wondering which one would be faster. Here is an EXPLAIN on a similar test I did on one of my test tables. (NO index on field1, WinXP, MySQL 5.0.41-community-nt, SQLyog query window) SELECT COUNT(*) FROM tblClients (1660 row(s) returned) (0 ms taken) RESET QUERY CACHE SELECT DISTINCT field1 FROM tblClients (130 row(s) returned) (0 ms taken) EXPLAIN SELECT DISTINCT field1 FROM tblClients /* 1457 rows, Using temporary */ RESET QUERY CACHE SELECT field1 FROM tblClients GROUP BY field1 (130 row(s) returned) (16 ms taken) EXPLAIN SELECT field1 FROM tblClients GROUP BY field1 /* 1457 rows, Using temporary; Using filesort */ It appears that the SELECT DISTINCT did not have to use the filesort. So that should be faster, which confirms what I see here. This is just my $0.02... Thanks, Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Thursday, February 14, 2008 11:57 AM To: Richard Cc: mysql@lists.mysql.com Subject: Re: select unique ? Try: SELECT DISTINCT Colour FROM table; Or, if you want to do it correctly: SELECT Colour FROM table GROUP BY color; Richard wrote: Hello, I don't know if it is possible to do this with mysql alone ... Here goes : I've got a database list which is like to following : Num|Name|Colour --- 1|Harry|Red 2|Tom|Blue 3|Jane|Green 4|Philip|Red 5|Sarah|Red 6|Robert|Blue And from this table I wish to get a list of used colours. The correct answer would be : Colour - Red Blue Green The answer I don't want : Colour Red Blue Green Red Red Blue How would I achieve the first result with mysql ? Is it possible? Thanks in advance, Richard -- 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 unique ?
Hello, I don't know if it is possible to do this with mysql alone ... Here goes : I've got a database list which is like to following : Num | Name| Colour --- 1 | Harry | Red 2 | Tom | Blue 3 | Jane| Green 4 | Philip | Red 5 | Sarah | Red 6 | Robert | Blue And from this table I wish to get a list of used colours. The correct answer would be : Colour - Red Blue Green The answer I don't want : Colour Red Blue Green Red Red Blue How would I achieve the first result with mysql ? Is it possible? Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select unique ?
SELECT DISTINCT Colour FROM tablename Peter On 14/02/2008, Richard [EMAIL PROTECTED] wrote: Hello, I don't know if it is possible to do this with mysql alone ... Here goes : I've got a database list which is like to following : Num | Name| Colour --- 1 | Harry | Red 2 | Tom | Blue 3 | Jane| Green 4 | Philip | Red 5 | Sarah | Red 6 | Robert | Blue And from this table I wish to get a list of used colours. The correct answer would be : Colour - Red Blue Green The answer I don't want : Colour Red Blue Green Red Red Blue How would I achieve the first result with mysql ? Is it possible? Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: select unique ?
Try: SELECT DISTINCT Colour FROM table; Or, if you want to do it correctly: SELECT Colour FROM table GROUP BY color; Richard wrote: Hello, I don't know if it is possible to do this with mysql alone ... Here goes : I've got a database list which is like to following : Num|Name|Colour --- 1|Harry|Red 2|Tom|Blue 3|Jane|Green 4|Philip|Red 5|Sarah|Red 6|Robert|Blue And from this table I wish to get a list of used colours. The correct answer would be : Colour - Red Blue Green The answer I don't want : Colour Red Blue Green Red Red Blue How would I achieve the first result with mysql ? Is it possible? Thanks in advance, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
very strange slow plain select
Hello: I just stumbled on this hard to explain problem. Below select * from BigTable (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right away regardless of server config in my.cnf. What is interesting is the following sql select * from BigTable limit 1000 return the rows immediately as expected. Thanks for help.
Re: very strange slow plain select
In the last episode (Feb 07), [EMAIL PROTECTED] said: I just stumbled on this hard to explain problem. Below select * from BigTable (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right away regardless of server config in my.cnf. What is interesting is the following sql select * from BigTable limit 1000 return the rows immediately as expected. You want the --quick option. From the mysql manpage: o --quick, -q Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very strange slow plain select
Thanks very much. The default mysql behavior is a little unusual. What is the parameter in my.cnf to control the cache size before forcing a flush. I waited for 2 mininutes second before the output came out without the -q option. My server is fast enough to read in more than 2 GB data during this time so the cache would have been filled long before that. Kind of puzzling. In the last episode (Feb 07), [EMAIL PROTECTED] said: I just stumbled on this hard to explain problem. Below select * from BigTable (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right away regardless of server config in my.cnf. What is interesting is the following sql select * from BigTable limit 1000 return the rows immediately as expected. You want the --quick option. From the mysql manpage: o --quick, -q Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very strange slow plain select
At 03:49 PM 2/7/2008, [EMAIL PROTECTED] wrote: Thanks very much. The default mysql behavior is a little unusual. What is the parameter in my.cnf to control the cache size before forcing a flush. I waited for 2 mininutes second before the output came out without the -q option. My server is fast enough to read in more than 2 GB data during this time so the cache would have been filled long before that. Kind of puzzling. Have you tried Select SQL_NO_CACHE col1,col2 from table ...? If you are returning more than 10,000 rows at a time, why not use the LIMIT Offset,Limit to pull in just a few thousand rows at a time, then re-execute with a new offset to get the next thousand rows etc.. Why tie up the server with one huge query? You're also transferring 2gb over the network in one gulp so that can't be efficient either. Break it into smaller queries should help. Mike In the last episode (Feb 07), [EMAIL PROTECTED] said: I just stumbled on this hard to explain problem. Below select * from BigTable     (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right away regardless of server config in my.cnf.  What is interesting is the following sql select * from BigTable limit 1000 return the rows immediately as expected. You want the --quick option. From the mysql manpage: o --quick, -q Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file. -- Dan Nelson [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]
feature request: statement SELECT...(INSERT|UPDATE) :)
Hi! I use mysql on amateurish level mainly for personal needs and so please forgive me if this feature request is impossible to fulfil or if it is sent to the wrong mail-list, or if this functionality has been already realized in other ways :) Note: I've read the discription of C-function mysql_insert_id() and the discription of SELECT LAST_INSERT_ID() in new versions, but as far as I've understood it concerns only AUTOINCREMENT columns, and very often it isn't enough (some columns may be filled by mysql functions (for example data/time/mathematical functions etc) and very often the resulting values are needed at once for report representation or for using in the next statements INSERT) So: The INSERT(UPDATE) statement returns the quantity of strings inserted(updated) into a table. (*) However as far as I understand at the stage of fulfilling this operator mysql operates with these very strings. Is it possible to add to the syntax of the INSERT operator appoximately in such way: INSERT [IGNORE] INTO ... - a general syntax SELECT list INSERT [IGNORE] INTO ... - an added one. UPDATE ... - a general syntax SELECT list UPDATE - an added one. Where can it be needed? Example 1 ~ We have a data base: table (id, name, value1, value2, value3) Now let's imagine CGI-script which makes an insert(update) and shows the results to a user. In the current case we need to: 1. INSERT INTO table (name, value1, value2, value3) VALUES (?,?,?,?), (?,?,?,?),(?,?,?,?); 2. SELECT * FROM table WHERE ... while the server has all the data needed for the step 2 already on the step 1 :) When selecting a few inserted strings at once we have either a complex expression in the statement WHERE, or we need to split the INSERT call into few single ones and replace 1 and 2 by the sequence INSERT - SELECT - INSERT - SELECT. It would be excellent to write: SELECT * INSERT table (name, value1, value2, value3) VALUES (?,?,?,?), (?,?,?,?), (?,?,?,?); and, having on the entry the data for the insert on the exit, to get the result of insert at once (and to display it if necessary) similar UPDATE statement: SELECT column1, column2 UPDATE table SET column3=value, column4=value WHERE ...; Example 2: ~~ For example we have a data base: table1 (id, name) -- id - AUTOINCREMENT table2 (id, table1_id, value1) table3 (id, table1_id, value2) Now let's imagine CGI-script working with such data base. In case if it makes a data insert into this data base we need to: 1. INSERT [IGNORE] INTO table1 SET name=? 2. SELECT id FROM table1 WHERE name=? or SELECT LAST_INSERT_ID() as id; 3. INSERT INTO table2 SET value1=?, table1_id=? -- value, id_fromt1 INSERT INTO table3 SET value2=?, table1_id=? -- value, id_fromt1 In the current situation all the data necessary for fulfillment of the point 3 are actually available on mysql server when fulfilling point 1 however unfortunately it is impossible to extract them by making an additional request :( But if we had an additional syntax then we could unite points 1 and 2: SELECT id INSERT INTO table1 SET name=? And in some cases even points 1,2,3 altogether: We insert in all the tables at once: INSERT INTO table3 (table1_id, value2) SELECT table1_id, ? INSERT INTO table2 (table1_id, value1) SELECT id, ? INSERT INTO table1 SET name=?; -- value2, value1, name That is by adding the mirror statement SELECT...INSERT to the existing statement INSERT...SELECT we would gain a very interesting functionality, allowing sometimes to get rid of using transactions and (or) to refuse from storage procedures and to replace the mass colls by the single ones etc. PS: I understand that adding the changes into a language is a very serious question that needs a great discussion but one never can tell, may be mysql developers will be interested in my proposal ;) I think that taking into account (*) it will be relatively simply to realise such an operator (even not embedded for a start). Or am I not right? signature.asc Description: Digital signature
Re: feature request: statement SELECT...(INSERT|UPDATE) :)
On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote: Is it possible to add to the syntax of the INSERT operator appoximately in such way: SELECT list INSERT [IGNORE] INTO ... - an added one. SELECT list UPDATE - an added one. PS: I understand that adding the changes into a language is a very serious question that needs a great discussion but one never can tell, may be mysql developers will be interested in my proposal ;) I think it would be a good idea to look at the way other databases can do this and see if there is some common syntax that could also be implemented by MySQL. For instance PostgreSQL implements a INSERT / UPDATE / DELETE ... RETURNING ... statement. ISTM that is exactly the functionality you want, but with a different syntax. If there are no other (better) competing implementation syntaxes (I don't know any, but maybe other list members do), I would like MySQL to adopt the PostgreSQL example. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: feature request: statement SELECT...(INSERT|UPDATE) :)
On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote: Is it possible to add to the syntax of the INSERT operator appoximately in such way: SELECT list INSERT [IGNORE] INTO ... - an added one. SELECT list UPDATE - an added one. PS: I understand that adding the changes into a language is a very serious question that needs a great discussion but one never can tell, may be mysql developers will be interested in my proposal ;) I think it would be a good idea to look at the way other databases can do this and see if there is some common syntax that could also be implemented by MySQL. For instance PostgreSQL implements a INSERT / UPDATE / DELETE ... RETURNING ... statement. ISTM that is exactly the functionality you want, but with a different syntax. If there are no other (better) competing implementation syntaxes (I don't know any, but maybe other list members do), I would like MySQL to adopt the PostgreSQL example. Firebird uses this syntax as well, I believe it's the SQL standard syntax for this feature. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select from otherdb.table question?
When you establish a connection, it's a connection to a server, not a specific DB. You can set a default db so that you don't always have to specify the db name you are working with. So to answer your question, no, a new connection is not established. Brent On Jan 19, 2008, at 10:19 AM, Alex K wrote: Hi Guys, What does the statement select * from otherdb.table do if I haven't explicitly connected to otherdb previously? I would assume it connects to otherdb and does the select on table but does it create a new connection each time? Is it as efficient as explicitly connecting to otherdb and then querying. I'm using webware DBUtils for connection pooling. Would these connections also be taken into account? Thank you so much, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select from otherdb.table question?
Hi Brent, ahh of course :) thank you so much for answering though. Alex On 20/01/2008, Brent Baisley [EMAIL PROTECTED] wrote: When you establish a connection, it's a connection to a server, not a specific DB. You can set a default db so that you don't always have to specify the db name you are working with. So to answer your question, no, a new connection is not established. Brent On Jan 19, 2008, at 10:19 AM, Alex K wrote: Hi Guys, What does the statement select * from otherdb.table do if I haven't explicitly connected to otherdb previously? I would assume it connects to otherdb and does the select on table but does it create a new connection each time? Is it as efficient as explicitly connecting to otherdb and then querying. I'm using webware DBUtils for connection pooling. Would these connections also be taken into account? Thank you so much, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [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 from otherdb.table question?
Hi Guys, What does the statement select * from otherdb.table do if I haven't explicitly connected to otherdb previously? I would assume it connects to otherdb and does the select on table but does it create a new connection each time? Is it as efficient as explicitly connecting to otherdb and then querying. I'm using webware DBUtils for connection pooling. Would these connections also be taken into account? Thank you so much, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select
On Fri, 18 Jan 2008, Sebastian Mendel wrote: Hiep Nguyen schrieb: hi all, i have a table looks like this: ID sDate 1 1997-03-21 2 1997-04-30 3 1997-05-30 4 1998-01-29 5 1998-02-24 6 1998-03-21 7 1999-05-10 8 1999-07-12 9 1999-10-20 10 2000-01-01 11 2000-02-15 12 2000-03-20 13 2000-05-18 how do i construct my select statement so that i only get distinct year? so the above data will return something like this: sDate 2000 1999 1998 1997 did your tried: SELECT DISTINCT YEAR(`sDate`); -- Sebastian thanks, that's perfect. t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select
Hiep Nguyen schrieb: hi all, i have a table looks like this: ID sDate 1 1997-03-21 2 1997-04-30 3 1997-05-30 4 1998-01-29 5 1998-02-24 6 1998-03-21 7 1999-05-10 8 1999-07-12 9 1999-10-20 10 2000-01-01 11 2000-02-15 12 2000-03-20 13 2000-05-18 how do i construct my select statement so that i only get distinct year? so the above data will return something like this: sDate 2000 1999 1998 1997 did your tried: SELECT DISTINCT YEAR(`sDate`); -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select statement help
Hi, On Jan 18, 2008 2:59 PM, RoryGRen [EMAIL PROTECTED] wrote: Hi all I am quite new to mySQL and have the following question I hope someone can help me with: I have a database table imported directly from MS Access with two of the field names having brackets - F(1) and S(1). I don't want to change the field names, as I am simply going to import again when the database needs updating. How can I select these fields from the table? - SELECT F(1), S(1) doesn't work, SELECT F(1) , S(1) doesn't work and neither does SELECT [F(1)], [S(1)] Quote the names with backticks: `F(1)`, `S(1)` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select statement help
Hi all I am quite new to mySQL and have the following question I hope someone can help me with: I have a database table imported directly from MS Access with two of the field names having brackets - F(1) and S(1). I don't want to change the field names, as I am simply going to import again when the database needs updating. How can I select these fields from the table? - SELECT F(1), S(1) doesn't work, SELECT F(1) , S(1) doesn't work and neither does SELECT [F(1)], [S(1)] Thanks, in anticipation! Rory -- View this message in context: http://www.nabble.com/Select-statement-help-tp14957781p14957781.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select
hi all, i have a table looks like this: ID sDate 1 1997-03-21 2 1997-04-30 3 1997-05-30 4 1998-01-29 5 1998-02-24 6 1998-03-21 7 1999-05-10 8 1999-07-12 9 1999-10-20 10 2000-01-01 11 2000-02-15 12 2000-03-20 13 2000-05-18 how do i construct my select statement so that i only get distinct year? so the above data will return something like this: sDate 2000 1999 1998 1997 thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select multiple rows with no reference table
Hey folks. I just joined the list. Please let me know if I am not posting to the correct list. I am trying to use a select statement to retreive multiple records without a reference table. Obviously retreiving a single record is simple enough: mysql SELECT 'value1' AS var1, 'value2' AS var2; +++ | var1 | var2 | +++ | value1 | value2 | +++ What I need to do is something similar, but producing multiple records without referencing any tables: +++ | var1 | var2 | +++ | value1 | value2 | | value3 | value4 | | value5 | value6 | +++ Thank you in advance for any advice you can give.
Re: Select multiple rows with no reference table
Hi Mark, On Jan 15, 2008 12:51 PM, Mark Wexler [EMAIL PROTECTED] wrote: Hey folks. I just joined the list. Please let me know if I am not posting to the correct list. You're in the right list. I am trying to use a select statement to retreive multiple records without a reference table. Obviously retreiving a single record is simple enough: mysql SELECT 'value1' AS var1, 'value2' AS var2; +++ | var1 | var2 | +++ | value1 | value2 | +++ What I need to do is something similar, but producing multiple records without referencing any tables: +++ | var1 | var2 | +++ | value1 | value2 | | value3 | value4 | | value5 | value6 | +++ You can use UNION for this. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select multiple rows with no reference table
Thank you -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Baron Schwartz Sent: Tuesday, January 15, 2008 1:54 PM To: Mark Wexler Cc: mysql@lists.mysql.com Subject: Re: Select multiple rows with no reference table Hi Mark, On Jan 15, 2008 12:51 PM, Mark Wexler [EMAIL PROTECTED] wrote: Hey folks. I just joined the list. Please let me know if I am not posting to the correct list. You're in the right list. I am trying to use a select statement to retreive multiple records without a reference table. Obviously retreiving a single record is simple enough: mysql SELECT 'value1' AS var1, 'value2' AS var2; +++ | var1 | var2 | +++ | value1 | value2 | +++ What I need to do is something similar, but producing multiple records without referencing any tables: +++ | var1 | var2 | +++ | value1 | value2 | | value3 | value4 | | value5 | value6 | +++ You can use UNION for this. Baron -- 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 SELECT Statement with Date help request
Hi List, I am wondering if someone can help me with a query to check what databases are on the MySQL server and then check which of those databases are either partially or completely within the date range i require. The scenario is as follows: db1 : 2007-01-01 to 2007-02-01 db2 : 2007-02-01 to 2007-03-01 db3 : 2007-03-01 to 2007-04-01 db4 : 2007-04-01 to 2007-05-01 db5 : 2007-05-01 to 2007-06-01 db6 : 2007-06-01 to 2007-07-01 I require a select statement to query all the db's to identify records with the date range for eg. 2007-02-15 to 2007-05-12. Logically this tells me that the databases that will have this information will be db2,db3,db4 and db5. My problem is that i have multiple servers running at different locations that uses the same app that writes to the MySQL db. However the amount of databases on each server differs in amount of db's and date ranges for each server. Is there a way of getting such a result with MySQL? Thanks in advance, Craig
Re: MySQL SELECT Statement with Date help request
Sounds like you should create a MERGE table that links all the underlying tables together. Then you just query the merge table and MySQL handles which tables it needs to pull data from. You also then don't need to query for the tables. On Jan 9, 2008, at 9:12 AM, Cx Cx wrote: Hi List, I am wondering if someone can help me with a query to check what databases are on the MySQL server and then check which of those databases are either partially or completely within the date range i require. The scenario is as follows: db1 : 2007-01-01 to 2007-02-01 db2 : 2007-02-01 to 2007-03-01 db3 : 2007-03-01 to 2007-04-01 db4 : 2007-04-01 to 2007-05-01 db5 : 2007-05-01 to 2007-06-01 db6 : 2007-06-01 to 2007-07-01 I require a select statement to query all the db's to identify records with the date range for eg. 2007-02-15 to 2007-05-12. Logically this tells me that the databases that will have this information will be db2,db3,db4 and db5. My problem is that i have multiple servers running at different locations that uses the same app that writes to the MySQL db. However the amount of databases on each server differs in amount of db's and date ranges for each server. Is there a way of getting such a result with MySQL? Thanks in advance, Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: derived select can't find table
Hi, On Dec 26, 2007 11:02 PM, wang shuming [EMAIL PROTECTED] wrote: Hi, select * , ( select f2 from (select f1,f2 from t2 where t2.id=t1.id limit 1 union all ... order by f1 ) t2 limit 1 ) f2 from t1 Unknown column ' t1.id' in 'where clause' Best regard! Shuming Wang Read this page: http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html Subqueries in the FROM clause cannot be correlated subqueries. They are materialized (executed to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
derived select can't find table
Hi, select * , ( select f2 from (select f1,f2 from t2 where t2.id=t1.id limit 1 union all ... order by f1 ) t2 limit 1 ) f2 from t1 Unknown column ' t1.id' in 'where clause' Best regard! Shuming Wang
Applying LIMIT to SELECT count(*)
Hi, My task is to limit calculation of total number of items in the database that satisfy certain conditions. I join two tables using WHERE and there are millions of records as the result. When I do SELECT count(*) it takes really too long. The table has appropriate indexes and I experimented with replacing the conditions, etc., so I don't think there is a way to make it work any faster. In my case it would be anough to say that there are more than e.g. 50 000 of items instead of calculating the exact quantity. My question is how to apply a certain limit to count() function in order it would either return the real quantity if it is smaller than the limit or return the limit and stop further calculation, quite same as when using SELECT * FROM ... LIMIT 0, 100 Another option could be estimating approximate quantity in the result but it seems to me much more complex and I honestly don't know where to start from. Thanks! -- View this message in context: http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14453544.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Applying LIMIT to SELECT count(*)
If exact number isn't important, you might want to try table_rows in information_schema.tables or show table status. On Dec 21, 2007 7:53 PM, Urms [EMAIL PROTECTED] wrote: Hi, My task is to limit calculation of total number of items in the database that satisfy certain conditions. I join two tables using WHERE and there are millions of records as the result. When I do SELECT count(*) it takes really too long. The table has appropriate indexes and I experimented with replacing the conditions, etc., so I don't think there is a way to make it work any faster. In my case it would be anough to say that there are more than e.g. 50 000 of items instead of calculating the exact quantity. My question is how to apply a certain limit to count() function in order it would either return the real quantity if it is smaller than the limit or return the limit and stop further calculation, quite same as when using SELECT * FROM ... LIMIT 0, 100 Another option could be estimating approximate quantity in the result but it seems to me much more complex and I honestly don't know where to start from. Thanks! -- View this message in context: http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14453544.html Sent from the MySQL - General mailing list archive at Nabble.com. -- 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: Applying LIMIT to SELECT count(*)
The problem is that there are certain conditions after WHERE different for each query and the results number can be very different. -- View this message in context: http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14459808.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Applying LIMIT to SELECT count(*)
Oh, I misunderstood,sorry. Using summary tables doesn't work for you? On Dec 22, 2007 3:00 AM, Urms [EMAIL PROTECTED] wrote: The problem is that there are certain conditions after WHERE different for each query and the results number can be very different. -- View this message in context: http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14459808.html Sent from the MySQL - General mailing list archive at Nabble.com. -- 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]
Can slow-query-log option only record select statement?
I want to know how to configurate slow-query-log to let it not record the update sql. I just want to know how the slow select statement ,not the update or insert. Anybody's reply is appreciated,thanks. -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Select mit Subselect Problem
Hallo, folgendes Szenario: Tabelle Sales (s) mit jede Menge Abverkaufsdaten. ArtikelNr,Kassenbon,HändlerID,Datum Jetzt sollen alle zu einem gekauften Artikel ebenfalls augelistet werden, d.h. was wurde mit dem Artikel zusätzliche gekauft Bisher habe ich das mit PHP gemacht, nur macht jetzt bei 5 Mio Datensätze in den Abverkäufen PHP schlapp. DArum meine Frage kann ich effizienter Mit MySQL machen? Bisher SELECT ArtikelNr,Kassenbon,Haendler,Datum FROM sales s WHERE ArtikelNr = '10099' SCHLEIFE mit Subselect SELECT * FROM sales WHERE ArtikelNr='$ArtikelNr' AND Kassenbon='$Kassenbon' AND Haendler='$Haendler' AND Datum='$Datum' Danach die Ergebnisse aufsummiert. Geht das effizienter? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select mit Subselect Problem
At 7:45a -0500 on 17 Dec 2007, Marcus Füg wrote: Tabelle Sales (s) mit jede Menge Abverkaufsdaten. ArtikelNr,Kassenbon,HändlerID,Datum Jetzt sollen alle zu einem gekauften Artikel ebenfalls augelistet werden, d.h. was wurde mit dem Artikel zusätzliche gekauft Bisher habe ich das mit PHP gemacht, nur macht jetzt bei 5 Mio Datensätze in den Abverkäufen PHP schlapp. DArum meine Frage kann ich effizienter Mit MySQL machen? [snip] Danach die Ergebnisse aufsummiert. Geht das effizienter? 1. This list is largely an English list, so you may have better luck asking your question in English. Alternatively, you might ask your question on this list: http://lists.mysql.com/mysql-de 2. How can you more efficiently use MySQL in this scenario? It depends on exactly what you're trying to do. If you can describe the problem rather than exactly what you're trying to do, we may be able to better help. That said, I'm guessing you're looking for GROUP BY and ORDER BY. Take a gander at the MySQL docs for the version that you are using. A starting point: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions-and-modifiers.html Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with select
hi list, i have two tables: idea(iid int not null primary_key auto_increment, completed_by int, submitted_by int); employee(eid int not null primary_key auto_increment, first varchar(20), last varchar(30)); table idea data: 1 | 4 | 10 2 | 3 | 7 table employee data: 3 | john | Doe 4 | betty | smith 7 | bob | Gomez 10 | sun | mcnab i'm trying to select from idea table such that when iid = 1, i should get betty smith for completed_by column and sun mcnab for submitted_by column. 1st trial: select iid,completed_by,submitted_by from idea where iid=1 i got: 1 | 4 | 10 2nd trial: select idd,concat(first, ,last),submitted_by from idea,employee where iid=1 and completed_by=eid; i got: 1 | betty smith | 10 now, instead of 10 for the submitted_by column, how do i get sun mcnab? thanks, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with select
You might try this: SELECT I.iid, CONCAT(ECOMP.last, ', ', ECOMP.first) AS 'Completed By', CONCAT(ESUB.last, ', ', ESUB.first) AS 'Submitted By', FROM idea I JOIN employee ECOMP ON I.completed_by = ECOMP.eid JOIN employee ESUB ON I.submitted_by = ESUB.eid andy Hiep Nguyen wrote: hi list, i have two tables: idea(iid int not null primary_key auto_increment, completed_by int, submitted_by int); employee(eid int not null primary_key auto_increment, first varchar(20), last varchar(30)); table idea data: 1 | 4 | 10 2 | 3 | 7 table employee data: 3 | john | Doe 4 | betty | smith 7 | bob | Gomez 10 | sun | mcnab i'm trying to select from idea table such that when iid = 1, i should get betty smith for completed_by column and sun mcnab for submitted_by column. 1st trial: select iid,completed_by,submitted_by from idea where iid=1 i got: 1 | 4 | 10 2nd trial: select idd,concat(first, ,last),submitted_by from idea,employee where iid=1 and completed_by=eid; i got: 1 | betty smith | 10 now, instead of 10 for the submitted_by column, how do i get sun mcnab? thanks, T. Hiep -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with select
mysql select idea.iid, concat(employee.first, ,employee.last),(SELECT concat( employee.first, ,employee.last) FROM EMPLOYEE where employee.eid=idea.submitted_by) from ide a,employee where idea.iid=1 and idea.completed_by=employee.eid; M-- - Original Message - From: Hiep Nguyen [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 05, 2007 2:02 PM Subject: help with select hi list, i have two tables: idea(iid int not null primary_key auto_increment, completed_by int, submitted_by int); employee(eid int not null primary_key auto_increment, first varchar(20), last varchar(30)); table idea data: 1 | 4 | 10 2 | 3 | 7 table employee data: 3 | john | Doe 4 | betty | smith 7 | bob | Gomez 10 | sun | mcnab i'm trying to select from idea table such that when iid = 1, i should get betty smith for completed_by column and sun mcnab for submitted_by column. 1st trial: select iid,completed_by,submitted_by from idea where iid=1 i got: 1 | 4 | 10 2nd trial: select idd,concat(first, ,last),submitted_by from idea,employee where iid=1 and completed_by=eid; i got: 1 | betty smith | 10 now, instead of 10 for the submitted_by column, how do i get sun mcnab? thanks, T. Hiep -- 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 Speed
At 05:57 PM 11/26/2007, you wrote: The second query might be faster due to caching. This can be verified by executing: RESET QUERY CACHE before executing the second query. This will clear the queries from the cache. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Speed
On Nov 27, 2007 10:21 AM, mos [EMAIL PROTECTED] wrote: At 05:57 PM 11/26/2007, you wrote: The second query might be faster due to caching. This can be verified by executing: RESET QUERY CACHE before executing the second query. This will clear the queries from the cache. No need to blow your whole cache. Just do this on the session you test from: SET SESSION query_cache_type = OFF; However, while this disables the query cache, it doesn't reset the caching of disk data, which is the most likely reason for queries to run faster the second time. The needed index or data records will be in RAM the second time the query is run. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT speed
Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds. The statement example is: select nas.nasIpAddress, count(distinct(acct.user_id)), count(*), sum(acct.acctOutputOctets) from acct, nas, can, acctStatusType where acct.can_id = can.id and acct.acctStatusType_id = acctStatusType.id and acct.day = '2007-09-01' and acct.day = '2007-11-01' and acct.nas_id = nas.id and can.can = 10 and acctStatusType.acctStatusType = 'Stop' group by acct.nas_id ; EXPLAIN shows the following: ++-++++- ---+-+-+---++ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-++++- ---+-+-+---++ | 1 | SIMPLE | can| const | PRIMARY,can| can| 2 | const | 1 | Using filesort | | 1 | SIMPLE | acctStatusType | const | PRIMARY,acctStatusType | acctStatusType | 10 | const | 1 || | 1 | SIMPLE | acct | ref| index1 | index1 | 4 | const,const | 63827 | Using where| | 1 | SIMPLE | nas| eq_ref | PRIMARY| PRIMARY| 4 | GWF.acct.nas_id | 1 || ++-++++- ---+-+-+---++ I have the following table with 59742411 rows: mysql describe acct; +---+---+--+-+-- ---+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-- ---+---+ | date | datetime | | | -00-00 00:00:00 | | | user_id | int(10) unsigned | | PRI | 0 | | | nas_id| int(10) unsigned | | PRI | 0 | | | can | smallint(5) unsigned | YES | | NULL | | | can_id| smallint(5) unsigned | YES | MUL | NULL | | | acctStatusType_id | tinyint(3) unsigned | | PRI | 0 | | | acctTerminateCause_id | tinyint(3) unsigned | | | 0 | | | sweetEventContext_id | tinyint(3) unsigned | | | 0 | | | acctSessionId | varchar(8)| | PRI | | | | acctDelayTime | mediumint(8) unsigned | | | 0 | | | acctSessionTime | mediumint(8) unsigned | YES | | NULL | | | acctInputOctets | bigint(20) unsigned | YES | | NULL | | | acctOutputOctets | bigint(20) unsigned | YES | | NULL | | | wisprBwMaxUp | int(10) unsigned | YES | | NULL | | | wisprBwMaxDown| int(10) unsigned | YES | | NULL | | | day | date | | PRI | -00-00 | | | acctMultiSessionId| varchar(27) | YES | | NULL | | +---+---+--+-+-- ---+---+ mysql show index from acct; +---++--+--+---+ ---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+---+ ---+-+--++--++-+ | acct | 0 | PRIMARY |1 | user_id | A | 53341 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |2 | nas_id| A | 277871 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |3 | acctStatusType_id | A | 558340 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |4 | acctSessionId | A |59742411 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |5 | day | A |59742411 | NULL | NULL | | BTREE | | | acct | 1 | index1 |1 | can_id| A | 467 | NULL | NULL | YES | BTREE | | | acct | 1 | index1 |2 | acctStatusType_id | A | 936 | NULL | NULL | | BTREE | | | acct | 1 | index1 |3 | day
SELECT Speed
Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds. The statement example is: select nas.nasIpAddress, count(distinct(acct.user_id)), count(*), sum(acct.acctOutputOctets) from acct, nas, can, acctStatusType where acct.can_id = can.id and acct.acctStatusType_id = acctStatusType.id and acct.day = '2007-09-01' and acct.day = '2007-11-01' and acct.nas_id = nas.id and can.can = 10 and acctStatusType.acctStatusType = 'Stop' group by acct.nas_id ; EXPLAIN shows the following: ++-++++- ---+-+-+---++ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++++- ---+-+-+---++ | 1 | SIMPLE | can| const | PRIMARY,can| can | 2 | const | 1 | Using filesort | | 1 | SIMPLE | acctStatusType | const | PRIMARY,acctStatusType | acctStatusType | 10 | const | 1 || | 1 | SIMPLE | acct | ref| index1 | index1 | 4 | const,const | 63827 | Using where| | 1 | SIMPLE | nas| eq_ref | PRIMARY| PRIMARY| 4 | GWF.acct.nas_id | 1 || ++-++++- ---+-+-+---++ I have the following table with 59742411 rows: mysql describe acct; +---+---+--+-+-- ---+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-- ---+---+ | date | datetime | | | -00-00 00:00:00 | | | user_id | int(10) unsigned | | PRI | 0 | | | nas_id| int(10) unsigned | | PRI | 0 | | | can | smallint(5) unsigned | YES | | NULL | | | can_id| smallint(5) unsigned | YES | MUL | NULL | | | acctStatusType_id | tinyint(3) unsigned | | PRI | 0 | | | acctTerminateCause_id | tinyint(3) unsigned | | | 0 | | | sweetEventContext_id | tinyint(3) unsigned | | | 0 | | | acctSessionId | varchar(8)| | PRI | | | | acctDelayTime | mediumint(8) unsigned | | | 0 | | | acctSessionTime | mediumint(8) unsigned | YES | | NULL | | | acctInputOctets | bigint(20) unsigned | YES | | NULL | | | acctOutputOctets | bigint(20) unsigned | YES | | NULL | | | wisprBwMaxUp | int(10) unsigned | YES | | NULL | | | wisprBwMaxDown| int(10) unsigned | YES | | NULL | | | day | date | | PRI | -00-00 | | | acctMultiSessionId| varchar(27) | YES | | NULL | | +---+---+--+-+-- ---+---+ mysql show index from acct; +---++--+--+---+ ---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+---+ ---+-+--++--++-+ | acct | 0 | PRIMARY |1 | user_id | A | 53341 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |2 | nas_id| A | 277871 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |3 | acctStatusType_id | A | 558340 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |4 | acctSessionId | A |59742411 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |5 | day | A |59742411 | NULL | NULL | | BTREE | | | acct | 1 | index1 |1 | can_id| A | 467 | NULL | NULL | YES | BTREE | | | acct | 1 | index1 |2 | acctStatusType_id | A | 936 | NULL | NULL | | BTREE | | | acct | 1 | index1 |3 | day | A | 88638 | NULL
Re: SELECT Speed
You seem to have an over-reliance on BTREE Indexes over BITMAPPED Indexes or HASH Indexes There are specific rules governing implementation of BTREE Index http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.h tm#sthref893 As well as specific rules governing use of HASH Index http://www.geekinterview.com/question_details/28844 not to forget rules governing use of BITMAP indexes http://www.dba-oracle.com/oracle_tips_bitmapped_indexes.htm M- - Original Message - From: Alexander Bespalov [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 26, 2007 10:03 AM Subject: SELECT Speed Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds. The statement example is: select nas.nasIpAddress, count(distinct(acct.user_id)), count(*), sum(acct.acctOutputOctets) from acct, nas, can, acctStatusType where acct.can_id = can.id and acct.acctStatusType_id = acctStatusType.id and acct.day = '2007-09-01' and acct.day = '2007-11-01' and acct.nas_id = nas.id and can.can = 10 and acctStatusType.acctStatusType = 'Stop' group by acct.nas_id ; EXPLAIN shows the following: ++-++++- ---+-+-+---++ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++++- ---+-+-+---++ | 1 | SIMPLE | can| const | PRIMARY,can| can | 2 | const | 1 | Using filesort | | 1 | SIMPLE | acctStatusType | const | PRIMARY,acctStatusType | acctStatusType | 10 | const | 1 || | 1 | SIMPLE | acct | ref| index1 | index1 | 4 | const,const | 63827 | Using where| | 1 | SIMPLE | nas| eq_ref | PRIMARY| PRIMARY| 4 | GWF.acct.nas_id | 1 || ++-++++- ---+-+-+---++ I have the following table with 59742411 rows: mysql describe acct; +---+---+--+-+-- ---+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-- ---+---+ | date | datetime | | | -00-00 00:00:00 | | | user_id | int(10) unsigned | | PRI | 0 | | | nas_id| int(10) unsigned | | PRI | 0 | | | can | smallint(5) unsigned | YES | | NULL | | | can_id| smallint(5) unsigned | YES | MUL | NULL | | | acctStatusType_id | tinyint(3) unsigned | | PRI | 0 | | | acctTerminateCause_id | tinyint(3) unsigned | | | 0 | | | sweetEventContext_id | tinyint(3) unsigned | | | 0 | | | acctSessionId | varchar(8)| | PRI | | | | acctDelayTime | mediumint(8) unsigned | | | 0 | | | acctSessionTime | mediumint(8) unsigned | YES | | NULL | | | acctInputOctets | bigint(20) unsigned | YES | | NULL | | | acctOutputOctets | bigint(20) unsigned | YES | | NULL | | | wisprBwMaxUp | int(10) unsigned | YES | | NULL | | | wisprBwMaxDown| int(10) unsigned | YES | | NULL | | | day | date | | PRI | -00-00 | | | acctMultiSessionId| varchar(27) | YES | | NULL | | +---+---+--+-+-- ---+---+ mysql show index from acct; +---++--+--+---+ ---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+---+ ---+-+--++--++-+ | acct | 0 | PRIMARY |1 | user_id | A | 53341 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |2 | nas_id| A | 277871 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |3
Re: SELECT Speed
The second query might be faster due to caching. On 11/26/07, Alexander Bespalov [EMAIL PROTECTED] wrote: Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds. The statement example is: select nas.nasIpAddress, count(distinct(acct.user_id)), count(*), sum(acct.acctOutputOctets) from acct, nas, can, acctStatusType where acct.can_id = can.id and acct.acctStatusType_id = acctStatusType.id and acct.day = '2007-09-01' and acct.day = '2007-11-01' and acct.nas_id = nas.id and can.can = 10 and acctStatusType.acctStatusType = 'Stop' group by acct.nas_id ; EXPLAIN shows the following: ++-++++- ---+-+-+---++ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++++- ---+-+-+---++ | 1 | SIMPLE | can| const | PRIMARY,can| can | 2 | const | 1 | Using filesort | | 1 | SIMPLE | acctStatusType | const | PRIMARY,acctStatusType | acctStatusType | 10 | const | 1 || | 1 | SIMPLE | acct | ref| index1 | index1 | 4 | const,const | 63827 | Using where| | 1 | SIMPLE | nas| eq_ref | PRIMARY| PRIMARY| 4 | GWF.acct.nas_id | 1 || ++-++++- ---+-+-+---++ I have the following table with 59742411 rows: mysql describe acct; +---+---+--+-+-- ---+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-- ---+---+ | date | datetime | | | -00-00 00:00:00 | | | user_id | int(10) unsigned | | PRI | 0 | | | nas_id| int(10) unsigned | | PRI | 0 | | | can | smallint(5) unsigned | YES | | NULL | | | can_id| smallint(5) unsigned | YES | MUL | NULL | | | acctStatusType_id | tinyint(3) unsigned | | PRI | 0 | | | acctTerminateCause_id | tinyint(3) unsigned | | | 0 | | | sweetEventContext_id | tinyint(3) unsigned | | | 0 | | | acctSessionId | varchar(8)| | PRI | | | | acctDelayTime | mediumint(8) unsigned | | | 0 | | | acctSessionTime | mediumint(8) unsigned | YES | | NULL | | | acctInputOctets | bigint(20) unsigned | YES | | NULL | | | acctOutputOctets | bigint(20) unsigned | YES | | NULL | | | wisprBwMaxUp | int(10) unsigned | YES | | NULL | | | wisprBwMaxDown| int(10) unsigned | YES | | NULL | | | day | date | | PRI | -00-00 | | | acctMultiSessionId| varchar(27) | YES | | NULL | | +---+---+--+-+-- ---+---+ mysql show index from acct; +---++--+--+---+ ---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+---+ ---+-+--++--++-+ | acct | 0 | PRIMARY |1 | user_id | A | 53341 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |2 | nas_id| A | 277871 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |3 | acctStatusType_id | A | 558340 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |4 | acctSessionId | A |59742411 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY |5 | day | A |59742411 | NULL | NULL | | BTREE | | | acct | 1 | index1 |1 | can_id| A | 467 | NULL | NULL | YES | BTREE | | | acct | 1 | index1
Select rows containing identical values in two columns
I have a table that has a column with the id of the person that created the row. In another column in the same row there is a column with the id of the person that modified that row. Is there a way to write a SELECT statement that will return all the rows where the value in the creation column equals the value in the modification column? I don't want to specify a specific id in either of the columns. TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select rows containing identical values in two columns
I have a table that has a column with the id of the person that created the row. In another column in the same row there is a column with the id of the person that modified that row. Is there a way to write a SELECT statement that will return all the rows where the value in the creation column equals the value in the modification column? I don't want to specify a specific id in either of the columns. SELECT * FROM your_table WHERE created_by_id = updated_by_id; ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Use select within delete
Try this (I do not have 4.0.24 to try): delete table1.* from table1, table2 where table1.id = table2.id and dateoneweek Ravi. On 11/13/07, mark addison [EMAIL PROTECTED] wrote: Dario Hernan wrote: Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario Not until 4.1. What you can do instead is run the select into a temp table and then run the delete as a join with that temp table. mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Use select within delete
thanks all, I'll do it with a perl script! Dario 2007/11/14, Ravi Kumar. [EMAIL PROTECTED]: Try this (I do not have 4.0.24 to try): delete table1.* from table1, table2 where table1.id = table2.id and dateoneweek Ravi. On 11/13/07, mark addison [EMAIL PROTECTED] wrote: Dario Hernan wrote: Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario Not until 4.1. What you can do instead is run the select into a temp table and then run the delete as a join with that temp table. mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- 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]
Use select within delete
Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Use select within delete
Dario Hernan wrote: Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario Not until 4.1. What you can do instead is run the select into a temp table and then run the delete as a join with that temp table. mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select sum order
hi there, this seems so easy, but i'm out of sql for a long time and need help i have: id,amount,state 1,2.00,il 2,2.00,oh 3,1.00,il 4,1.00,ks 5,3.00,ks 6,4.00,oh how do i construct a sql statement that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. thank much, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select sum order
Hi, Hiep Nguyen wrote: hi there, this seems so easy, but i'm out of sql for a long time and need help i have: id,amount,state 1,2.00,il 2,2.00,oh 3,1.00,il 4,1.00,ks 5,3.00,ks 6,4.00,oh how do i construct a sql statement that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. Try this: select state, sum(amount) from tbl group by state; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select sum order
thanks, T. Hiep On Tue, 13 Nov 2007, Baron Schwartz wrote: Hi, Hiep Nguyen wrote: hi there, this seems so easy, but i'm out of sql for a long time and need help i have: id,amount,state 1,2.00,il 2,2.00,oh 3,1.00,il 4,1.00,ks 5,3.00,ks 6,4.00,oh how do i construct a sql statement that results as following: il,3.0 oh,6.0 ks,4.0 sum (amount) all the same state. Try this: select state, sum(amount) from tbl group by state; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select question
I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Thanks Mat
Re: Select question
Matthew Stuart schrieb: I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Hi Mat, TOP 2 is not MySQL? However, MySQL knows LIMIT [1] which is more powerful, try: SELECT * FROM Content ORDER BY ContentID DESC LIMIT 1,1 regards -Ralf [1]: http://dev.mysql.com/doc/refman/5.0/en/select.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select question
Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Matthew Stuart [mailto:[EMAIL PROTECTED] Sent: Thursday, October 25, 2007 6:55 AM To: MySQL email support Subject: Select question I've got this statement to select the last two entries in my db: SELECT top 2 * FROM Content ORDER BY ContentID desc and it works fine because it selects the last two items entered into the db. However, I only want to be able to select item 2 rather than both 1 and 2. How do I do that? Thanks Mat [JS] What does adding LIMIT 2,1 do? I'm not sure what SELECT top 2 * means. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?
When will I be able to do something seemingly so basic as this re-use of an
alias?
SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS group_duration_date,
UNIX_TIMESTAMP(group_duration_date)
AS group_duration_date_timestamp
FROM end_user_groups;
Error Code : 1054
Unknown column 'group_duration_date' in 'field list'
(0 ms taken)
So instead I must do this very cumbersome and inefficient way:
SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS group_duration_date,
UNIX_TIMESTAMP(DATE_ADD('2007-10-23',
INTERVAL user_access_hours HOUR))
AS group_duration_date_timestamp
FROM end_user_groups;
*sigh*
This has been a cause of frustration since mysql 3.x series.
Are there any plans to fix this annoyance? If so, in what version?
Currently using:
mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?
When will I be able to do something seemingly
so basic as this re-use of an alias?
Do you know an implementation of SQL which allows this?
PB
Daevid Vincent wrote:
When will I be able to do something seemingly so basic as this re-use of an
alias?
SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS group_duration_date,
UNIX_TIMESTAMP(group_duration_date)
AS group_duration_date_timestamp
FROM end_user_groups;
Error Code : 1054
Unknown column 'group_duration_date' in 'field list'
(0 ms taken)
So instead I must do this very cumbersome and inefficient way:
SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS group_duration_date,
UNIX_TIMESTAMP(DATE_ADD('2007-10-23',
INTERVAL user_access_hours HOUR))
AS group_duration_date_timestamp
FROM end_user_groups;
*sigh*
This has been a cause of frustration since mysql 3.x series.
Are there any plans to fix this annoyance? If so, in what version?
Currently using:
mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?
Nope. I've only ever use mySQL. I only ever care to use mySQL. mySQL puts in
plenty of other features that no other RDBMS has or uses, and other RDBMS
have features that mySQL has, so what's the problem. Unless I was porting
to/from another RDBMS? It seems stupid that I can't do that though. I can
use the alias in the HAVING clause, and also in an ORDER BY clause. I'm not
saying it's a trivial change, I am saying that it would make a lot of sense
to do and I'm sure I'm not the first person to desire such a feature.
d
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 23, 2007 5:39 PM
To: Daevid Vincent
Cc: mysql@lists.mysql.com
Subject: Re: Why can't I still not use an ALIAS in the SELECT
portion of an SQL statement?
When will I be able to do something seemingly
so basic as this re-use of an alias?
Do you know an implementation of SQL which allows this?
PB
Daevid Vincent wrote:
When will I be able to do something seemingly so basic as
this re-use of an
alias?
SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS group_duration_date,
UNIX_TIMESTAMP(group_duration_date)
AS group_duration_date_timestamp
FROM end_user_groups;
Error Code : 1054
Unknown column 'group_duration_date' in 'field list'
(0 ms taken)
So instead I must do this very cumbersome and inefficient way:
SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS group_duration_date,
UNIX_TIMESTAMP(DATE_ADD('2007-10-23',
INTERVAL
user_access_hours HOUR))
AS group_duration_date_timestamp
FROM end_user_groups;
*sigh*
This has been a cause of frustration since mysql 3.x series.
Are there any plans to fix this annoyance? If so, in what version?
Currently using:
mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686)
--
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: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?
Daevid Vincent wrote: Nope. I've only ever use mySQL. I only ever care to use mySQL. mySQL puts in plenty of other features that no other RDBMS has or uses, and other RDBMS have features that mySQL has, so what's the problem. Unless I was porting to/from another RDBMS? This strikes me as a *terrible* reason. It seems stupid that I can't do that though. I can use the alias in the HAVING clause, and also in an ORDER BY clause. Yes, but those are HAVING and ORDER BY clauses. You recognise that they are distinct parts of a SELECT statement, so ... I'm not saying it's a trivial change So far, i'd say that you're suggesting it is. http://dev.mysql.com/tech-resources/articles/mysql-views.html brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I still not use an ALIAS in the SELECT portion of an SQL statement?
[EMAIL PROTECTED] wrote:
Daevid Vincent wrote:
Nope. I've only ever use mySQL. I only ever care to use mySQL. mySQL
puts in
plenty of other features that no other RDBMS has or uses, and other RDBMS
have features that mySQL has, so what's the problem. Unless I was porting
to/from another RDBMS?
This strikes me as a *terrible* reason.
It seems stupid that I can't do that though. I can
use the alias in the HAVING clause, and also in an ORDER BY clause.
Yes, but those are HAVING and ORDER BY clauses. You recognise that they
are distinct parts of a SELECT statement, so ...
I'm not saying it's a trivial change
So far, i'd say that you're suggesting it is.
http://dev.mysql.com/tech-resources/articles/mysql-views.html
brian
I've looked for--but cannot find--a page online that explains this
nicely. Suffice to say that the problem is similar to that of trying to
use a column alias in the WHERE clause. In that case, it's because WHERE
is parsed before the SELECT clause. In your case, it's simply that the
column expressions are constructed more or less as one, not from left to
right or anything like that.
You could also use a derived table here, i should think:
SELECT foo.bar, UNIX_TIMESTAMP(foo.bar) FROM (
SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS bar
FROM end_user_groups
) AS foo;
brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.
Is there a way to know how many rows were used in a computation?
I tried this 'trick' but I still get 1, when I know that there are 3 rows
used...
SELECT SQL_CALC_FOUND_ROWS
MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours
HOUR)),
MAX(access_expire)
FROM
end_user_groups
JOIN end_user_group_links ON gid = id
WHERE
enabled = 1 AND uid = 16;
select FOUND_ROWS();
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_
found-rows
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.
Hi,
Daevid Vincent wrote:
Is there a way to know how many rows were used in a computation?
I tried this 'trick' but I still get 1, when I know that there are 3 rows
used...
SELECT SQL_CALC_FOUND_ROWS
MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours
HOUR)),
MAX(access_expire)
FROM
end_user_groups
JOIN end_user_group_links ON gid = id
WHERE
enabled = 1 AND uid = 16;
You can use COUNT(*). FOUND_ROWS() works a little differently, as you
know -- it lets you know how many rows would have been returned without
a LIMIT. But this query has no LIMIT of course.
select FOUND_ROWS();
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_
found-rows
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug?: SELECT *, Field vs. SELECT Field, *
Hi, When i try: SELECT *, id FROM table all is fine. But if i want the * at the end (because the table is large) i try: SELECT id, * FROM table and got: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM table' at line 1 I think this is a bug? It works when i try: SELECT id, table.* FROM table The Same thing is: SELECT *, * FROM table (which possibly makes no sense) -Ralf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deadlocks with High Concurrency SELECT FOR UPDATE
Hi William, William Newton wrote: Hello List, I have this table that has a single row in it: CREATE TABLE `quicktable` ( `x` int(11) NOT NULL auto_increment, `quick_id` int(11) NOT NULL default '0', PRIMARY KEY (`x`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 select * from quicktable; +---+--+ | x | quick_id | +---+--+ | 1 |0 | +---+--+ 1 row in set (0.00 sec) I have a large number of connections executing these queries: BEGIN: SELECT quick_id FROM quicktable FOR UPDATE; COMMIT; This works well until I hit a large number of concurrent connections (around 200), when I start getting deadlocks. Despite the fact, that I'm only selecting a single table. Here is the deadlock section from SHOW INNODB STATUS; LATEST DETECTED DEADLOCK 071015 20:22:35 *** (1) TRANSACTION: TRANSACTION 0 79790779, ACTIVE 2 sec, process no 7658, OS thread id 1185077584 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 368 MySQL thread id 3961, query id 2102790 10.1.10.122 bnewton statistics SELECT x, quick_id FROM quicktable WHERE x=1 FOR UPDATE *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790779 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) TRANSACTION: TRANSACTION 0 79790775, ACTIVE 2 sec, process no 7658, OS thread id 1191733584 2 lock struct(s), heap size 368 MySQL thread id 4094, query id 2102743 10.1.10.122 bnewton *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790775 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79791014 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH *** WE ROLL BACK TRANSACTION (2) Can anyone explain whats going on? Is there a limit for the number of concurrent transactions, before looking at the lock graph becomes too expensive? Is that documented somewhere? It's not documented, but yes there's both a maximum number of steps to check for a cycle in the waits-for graph, and a maximum depth of checking it: http://dev.mysql.com/sources/doxygen/mysql-5.1/lock0lock_8c-source.html#l00052 00046 /* Restricts the length of search we will do in the waits-for 00047 graph of transactions */ 00048 #define LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK 100 00049 00050 /* Restricts the recursion depth of the search we will do in the waits-for 00051 graph of transactions */ 00052 #define LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK 200 This is the price you pay for instant deadlock detection, as opposed to just lock wait timeout (the method a lot of other transactional systems take). You could recompile with a higher number if you want. It would be nice if this were configurable; you could submit a feature request for that. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deadlocks with High Concurrency SELECT FOR UPDATE
Hello List, I have this table that has a single row in it: CREATE TABLE `quicktable` ( `x` int(11) NOT NULL auto_increment, `quick_id` int(11) NOT NULL default '0', PRIMARY KEY (`x`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 select * from quicktable; +---+--+ | x | quick_id | +---+--+ | 1 |0 | +---+--+ 1 row in set (0.00 sec) I have a large number of connections executing these queries: BEGIN: SELECT quick_id FROM quicktable FOR UPDATE; COMMIT; This works well until I hit a large number of concurrent connections (around 200), when I start getting deadlocks. Despite the fact, that I'm only selecting a single table. Here is the deadlock section from SHOW INNODB STATUS; LATEST DETECTED DEADLOCK 071015 20:22:35 *** (1) TRANSACTION: TRANSACTION 0 79790779, ACTIVE 2 sec, process no 7658, OS thread id 1185077584 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 368 MySQL thread id 3961, query id 2102790 10.1.10.122 bnewton statistics SELECT x, quick_id FROM quicktable WHERE x=1 FOR UPDATE *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790779 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) TRANSACTION: TRANSACTION 0 79790775, ACTIVE 2 sec, process no 7658, OS thread id 1191733584 2 lock struct(s), heap size 368 MySQL thread id 4094, query id 2102743 10.1.10.122 bnewton *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790775 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79791014 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH *** WE ROLL BACK TRANSACTION (2) Can anyone explain whats going on? Is there a limit for the number of concurrent transactions, before looking at the lock graph becomes too expensive? Is that documented somewhere? Thanks, William Newton Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. http://farechase.yahoo.com/
LEFT JOIN (SELECT ...) -- no joy
using 5.0.24 mysql describe order_details; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | mediumint(8) unsigned | NO | PRI | NULL| auto_increment | | order_id | mediumint(8) unsigned | NO | MUL | | | | product_id | smallint(5) unsigned | NO | MUL | | | | quantity | smallint(5) unsigned | NO | | | | | unit_price | decimal(8,2) unsigned | NO | | | | ++---+--+-+-++ 5 rows in set (0.01 sec) mysql SELECT order_id, SUM(quantity * unit_price) AS subtotal - FROM order_details GROUP BY order_id; +--+--+ | order_id | subtotal | +--+--+ |1 | 101.94 | |2 |47.97 | +--+--+ 2 rows in set (0.00 sec) -- same query used in LEFT JOIN clause: mysql SELECT po.id, po.customer_id, po.delivered, od.subtotal - FROM purchase_order AS po - LEFT JOIN (SELECT order_id, SUM(quantity * unit_price) - AS subtotal FROM order_details GROUP BY order_id) - AS od ON od.order_id = po.id - ORDER BY po.id\G *** 1. row *** id: 10001 customer_id: 1 delivered: 2007-10-10 23:51:32 subtotal: NULL *** 2. row *** id: 10002 customer_id: 2 delivered: 2007-10-10 23:51:32 subtotal: NULL 2 rows in set (0.00 sec) So, i'm wondering why this inner query is returning NULL for subtotal here. I know that SUM() returns NULL on error but i can't see how that's the situation here. This works fine with Postgres. Is this a bug, possibly? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN (SELECT ...) -- no joy
Andrew Carlson wrote: Is there a purchase order (10002) with no orders in the order_details table? No, PO 10002 is related to the order_id = 2 here: mysql SELECT order_id, SUM(quantity * unit_price) AS subtotal - FROM order_details GROUP BY order_id; +--+--+ | order_id | subtotal | +--+--+ |1 | 101.94 | |2 |47.97 | +--+--+ But i'm getting NULL for subtotal on all of them, anyway. The same query works as exactly as expected in Postgres with some table modifications to allow for the different datatypes. The outer query should have no problem grabbing the correct subtotal from the joined query. b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to select second records in a group
I was a bit stumped on a good method to select the second record for each distinct group in a table. Say I have a table like the following: NAME DATE AMOUNT joe 2007-10-03 19:44:57 45 joe 2007-10-06 19:46:18 90 joe 2007-10-07 19:37:21 12 matt2007-10-03 19:36:54 23 matt2007-10-04 19:37:09 67 steve 2007-10-03 19:36:35 50 steve 2007-10-04 19:36:54 12 steve 2007-10-05 19:37:21 5 If I want the second date for each name in the table, how would I go about doing that? I've found it easy to get the first date for each name by a query like this: SELECT name,MIN(date) FROM table GROUP BY name; - or - SELECT name,date FROM table GROUP BY name ORDER BY date; I still am stumped on how I could get the record pertaining to the second date for each name in the table. Your thoughts? Thanks, Steve
Re: how to select second records in a group
Hi Steve, Steve Kiehl wrote: I was a bit stumped on a good method to select the second record for each distinct group in a table. Say I have a table like the following: NAME DATE AMOUNT joe 2007-10-03 19:44:57 45 joe 2007-10-06 19:46:18 90 joe 2007-10-07 19:37:21 12 matt 2007-10-03 19:36:54 23 matt 2007-10-04 19:37:09 67 steve 2007-10-03 19:36:35 50 steve 2007-10-04 19:36:54 12 steve 2007-10-05 19:37:21 5 If I want the second date for each name in the table, how would I go about doing that? I've found it easy to get the first date for each name by a query like this: SELECT name,MIN(date) FROM table GROUP BY name; - or - SELECT name,date FROM table GROUP BY name ORDER BY date; I still am stumped on how I could get the record pertaining to the second date for each name in the table. Your thoughts? You can use a variation on the techniques here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT ... LIKE and the Korean character set
Hello. I have a question about the behavior of SELECT ... LIKE and dealing with the Korean language. For those who don't know anything about the way the language's characters are formulated, I'll give you a quick crash course: Korean has its own alphabet, just like Japanese, Chinese and most other Asian languages. However, each Korean character in a word is actually a syllable composed of those alphabets. So ㅎ (H) + ㅏ (A) + ㄴ (N) = 한. (If the mailing list doesn't support Unicode/UTF-8, please tell me so I can direct you to a better example.) But with the way Unicode works, it's not exactly like H is in the same block that HAN is represented in, especially in the Korean character table. So here's the issue I'm having: I'm writing a Korean dictionary for myself. Basically, an application for adding words I've learned in my Korean lessons. But I'm having a problem with the LIKE part of the SELECT statement. Essentially, I'd like to do the Korean equivalent of LIKE 'A%'. However, doing LIKE 'ㅎ%', when a word like 'hangul' (한글) is in the database, I get no results. Obviously, creating a table of every combination of every character possible for each consonant in the Korean language is nonoptimal. So what can I do to solve this problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1
I have a table with a PRIMARY KEY on id field, whos evalue is populated usin auto_increment. CREATE TABLE `key` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `sid` smallint(4) unsigned NOT NULL DEFAULT '0', `email` varchar(128) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB; Question: 1) When I INSERT a row is there any way to be able in the same INSERT statement (without doing an UPDATE after the insert) to populate the field `sid`, which is base on the value that the field `id` gets (e.g. sid= MOD (id, 20)) Sounds like an excellent case for a TRIGGER. Sorry, I think I'm wrong here -- This would only work if NEW.ID already has a value when the BEFORE INSERT trigger get's called, but I think it has not, as the id column would only be filled when doing the actual insert, and not before. You might wanna try that out. AutoInc stinks. Always does :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
At 13:34 -0400 10/9/07, Baron Schwartz wrote: Looks like you've found the solution you need. The only other suggestion I have is to use UNION ALL if you don't need to eliminate duplicate rows in the UNION, because there's some overhead for checking for them. Hi Baron Thanks for this, and I did try it, but the difference in time taken to execute the query was negligible (I tested it multiple times) - it was around 0.02 seconds whichever way I did it, and when I used EXPLAIN, the results were identical except for one detail: The number of rows in the first row of the EXPLAIN result was lower with plain UNION than if I used UNION ALL. As far as I can tell from my relatively limited experience with all this, the first row refers to my outer 'wrapper' select from the derived table (the table in the first row is given as 'derived2' and the Extra column shows 'Using temporary'). For a given query, with UNION ALL that has 45 rows, with UNION it's 31. So I guess I'll stick to plain UNION. As far as my desire to cope with multiple search terms is concerned, I realise now that fulltext handles that anyway! So I've changed the few non-numeric fields that weren't indexed that way (fore, sur and topic) to fulltext and bingo! Not only that, but it all happens fully FOUR TIMES as quickly! So many thanks, Baron - mainly due to you, yesterday was a very good MySQL day for me. It's not often I get two 'lightbulb moments' on the same day! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Good people will do good things, and bad people will do bad things. But for good people to do bad things - that takes religion. -- Steven Weinberg, physicist and Nobel Laureate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Big SELECT: ordering results by where matches are found
I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful. I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this example - and I want to order my results according to where (if anywhere) matches are found. So... Let's say I have tables 'speakers', 'topics', 'speakers_topics', 'articles', 'other'. 'speakers' is a table of speakers, with id, name and some text fields. 'topics' is a list of topics they address 'speakers_topics' relates the above two by pairs of id numbers 'articles' and 'other' are further tables of text data with possibly more than one row for some speakers, identified by id. I want to search the data in the following order: name from 'speakers' topics text data from 'speakers' text data from 'articles' and 'other' ...and order the results according to where in that hierarchy a match is found. So, if the user's search term matches one speaker's name field, another's topic and someone else's text data, that's the order in which the results should be ordered. Also, if the same person is matched from, say, both name and text fields (which is very likely, as their name will almost certainly appear in some of the text), the name should take precedence in the ordering. To complicate matters further, I'd like if possible to extend this to an and/or situation. If the user enters two or more words, any results that match all the words should be ordered above those that match only some of the words. I can probably do this relatively easily with a series of separate queries (I'm doing all this from PHP, by the way), but that strikes me as inefficient. Can it all be done in one big query, perhaps with subqueries? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Revolution: an abrupt change in the form of misgovernment. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
Chris Sansom wrote: I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful. I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this example - and I want to order my results according to where (if anywhere) matches are found. So... Let's say I have tables 'speakers', 'topics', 'speakers_topics', 'articles', 'other'. 'speakers' is a table of speakers, with id, name and some text fields. 'topics' is a list of topics they address 'speakers_topics' relates the above two by pairs of id numbers 'articles' and 'other' are further tables of text data with possibly more than one row for some speakers, identified by id. I want to search the data in the following order: name from 'speakers' topics text data from 'speakers' text data from 'articles' and 'other' ...and order the results according to where in that hierarchy a match is found. So, if the user's search term matches one speaker's name field, another's topic and someone else's text data, that's the order in which the results should be ordered. Also, if the same person is matched from, say, both name and text fields (which is very likely, as their name will almost certainly appear in some of the text), the name should take precedence in the ordering. To complicate matters further, I'd like if possible to extend this to an and/or situation. If the user enters two or more words, any results that match all the words should be ordered above those that match only some of the words. I can probably do this relatively easily with a series of separate queries (I'm doing all this from PHP, by the way), but that strikes me as inefficient. Can it all be done in one big query, perhaps with subqueries? I've built similar systems with a series of UNION queries. Each UNION has a column for relevance, which can be a sum of CASE statements, such as IF(name matches, 1, 0) + IF(text matches, 1, 0) AS relevance... The entire UNION can then be ordered by relevance. You could also just add in an arbitrary number in each UNION, to get the effect of ordering by where in the hierarchy the match is found. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
At 11:01 -0400 10/9/07, Baron Schwartz wrote: I've built similar systems with a series of UNION queries. Each UNION has a column for relevance, which can be a sum of CASE statements, such as IF(name matches, 1, 0) + IF(text matches, 1, 0) AS relevance... The entire UNION can then be ordered by relevance. You could also just add in an arbitrary number in each UNION, to get the effect of ordering by where in the hierarchy the match is found. Oo-er. This sounds marvellous, and I /think/ I see what you're getting at, but it's a bit beyond anything I've done before - never used UNION for instance. Can you perhaps go into a little more detail? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The nice thing about standards is that there are so many of them to choose from. -- Andrew S. Tanenbaum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
At 11:01 -0400 10/9/07, Baron Schwartz wrote:
The entire UNION can then be ordered by relevance. You could also
just add in an arbitrary number in each UNION, to get the effect of
ordering by where in the hierarchy the match is found.
Actually, your pointing me towards UNION may have done the trick. I
read up on it on the MySQL docs site and I've ended up with this,
which actually covers more tables and fields than in my original post:
---
select distinct tb.speaker_id, tb.fore, tb.sur, tb.division from
(
(
select 1 as relevance, speaker_id, fore, sur, division
from speakers
where fore like '%education%' or sur like '%education%')
union
(
select 2 as relevance, s.speaker_id, fore, sur, division
from speakers s, speakers_topics st, topics t
where st.speaker_id = s.speaker_id and t.topic_id = st.topic_id and
topic like '%education%'
)
union
(
select 3 as relevance, speaker_id, fore, sur, division
from speakers where match (strap, shortbio, longbio) against ('education')
)
union
(
select 4 as relevance, s.speaker_id, fore, sur, division
from speakers s, articles a
where s.speaker_id = a.speaker_id and match (title, article) against
('education')
)
union
(
select 5 as relevance, s.speaker_id, fore, sur, division
from speakers s, other o
where s.speaker_id = o.speaker_id and match (title, article) against
('education')
)
union
(
select 6 as relevance, speaker_id, fore, sur, division
from speakers, books
where speaker_id = author and match (title, description) against ('education')
)
order by relevance, division, sur, fore
) as tb
---
First, I did it without the outer select, and I got speakers repeated
if they were matched in more than one block. One of the comments on
the MySQL docs site suggested the 'wrapper', which I did initially
like this:
select distinct speaker_id, fore, sur, division from... with nothing
after the final ')'. This gave me an error to the effect that derived
tables must always have an alias. What the hey, let's just try it
like this (the above)... and to my astonishment it worked!
So before I sign off on this thread, can you see any way I could improve this?
Naturally, I haven't yet incorporated the treatment of more than one
search term, but I'll try and work that out for myself. :-)
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Justice is incidental to law and order.
-- J. Edgar Hoover
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
Chris Sansom wrote:
At 11:01 -0400 10/9/07, Baron Schwartz wrote:
The entire UNION can then be ordered by relevance. You could also
just add in an arbitrary number in each UNION, to get the effect of
ordering by where in the hierarchy the match is found.
Actually, your pointing me towards UNION may have done the trick. I read
up on it on the MySQL docs site and I've ended up with this, which
actually covers more tables and fields than in my original post:
---
select distinct tb.speaker_id, tb.fore, tb.sur, tb.division from
(
(
select 1 as relevance, speaker_id, fore, sur, division
from speakers
where fore like '%education%' or sur like '%education%')
union
(
select 2 as relevance, s.speaker_id, fore, sur, division
from speakers s, speakers_topics st, topics t
where st.speaker_id = s.speaker_id and t.topic_id = st.topic_id and
topic like '%education%'
)
union
(
select 3 as relevance, speaker_id, fore, sur, division
from speakers where match (strap, shortbio, longbio) against ('education')
)
union
(
select 4 as relevance, s.speaker_id, fore, sur, division
from speakers s, articles a
where s.speaker_id = a.speaker_id and match (title, article) against
('education')
)
union
(
select 5 as relevance, s.speaker_id, fore, sur, division
from speakers s, other o
where s.speaker_id = o.speaker_id and match (title, article) against
('education')
)
union
(
select 6 as relevance, speaker_id, fore, sur, division
from speakers, books
where speaker_id = author and match (title, description) against
('education')
)
order by relevance, division, sur, fore
) as tb
---
First, I did it without the outer select, and I got speakers repeated if
they were matched in more than one block. One of the comments on the
MySQL docs site suggested the 'wrapper', which I did initially like this:
select distinct speaker_id, fore, sur, division from... with nothing
after the final ')'. This gave me an error to the effect that derived
tables must always have an alias. What the hey, let's just try it like
this (the above)... and to my astonishment it worked!
So before I sign off on this thread, can you see any way I could improve
this?
Naturally, I haven't yet incorporated the treatment of more than one
search term, but I'll try and work that out for myself. :-)
Looks like you've found the solution you need. The only other
suggestion I have is to use UNION ALL if you don't need to eliminate
duplicate rows in the UNION, because there's some overhead for checking
for them.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A select for a game ranking page.
Hi I have a table with: player_name, top_score, number_of_plays When I list them out I ORDER BY top_score DESC, number_of_plays DESC, player_name to help give some sort of order to the people with the same scores. What I would like to do is find out a players position without looping through all the records, so my plan was to do a SELECT count(*) and have WHERE top_score the players top score.. however when there are many scores the same I want to also do WHERE number_of_plays the players number of plays. Doing WHERE top_score 1000 AND number_of_plays 10 is no good as some players have higher scores but lower plays but should be counted as been higher ranked. I don't want to loop through the scores, that's not very elegant. Also creating a temp table where the scores are in order and then counting on that would also be overkill? I hope this makes sense and that there is a solution. -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A select for a game ranking page.
Hi, Critters wrote: Hi I have a table with: player_name, top_score, number_of_plays When I list them out I ORDER BY top_score DESC, number_of_plays DESC, player_name to help give some sort of order to the people with the same scores. What I would like to do is find out a players position without looping through all the records, so my plan was to do a SELECT count(*) and have WHERE top_score the players top score.. however when there are many scores the same I want to also do WHERE number_of_plays the players number of plays. Doing WHERE top_score 1000 AND number_of_plays 10 is no good as some players have higher scores but lower plays but should be counted as been higher ranked. I don't want to loop through the scores, that's not very elegant. Also creating a temp table where the scores are in order and then counting on that would also be overkill? I hope this makes sense and that there is a solution. This is a common problem with ranked data. It seems to be exactly the topic I wrote an O'Reilly article on: http://www.oreillynet.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
