!! Help with query: Where Date = MAX(Date)
Could someone please offer a little help. I have a table like: Year, Month, Start_date 20041020041102 20041120041203 20041220050104 20050120050204 20050220050303 I need to get the latest Year,Month for a given date, so for example today (20050204) I should retrieve 2005,01. As I'm using 4.0.20 I can't use subqueries so how can I create a query that does this? SELECT year, month FROM `dc_months` WHERE start_date = (SELECT MAX(start_date) from dc_months where start_date = '20050204') Any help much appreciated Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: !! Help with query: Where Date = MAX(Date)
snip As I'm using 4.0.20 I can't use subqueries so how can I create a query that does this? SELECT year, month FROM `dc_months` WHERE start_date = (SELECT MAX(start_date) from dc_months where start_date = '20050204') Any help much appreciated Graham have a look here : http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html it should be useful for you. Thanks Philippe that could do it. Graham. -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a query using multiple LEFT JOINS
Thanks Tom Doing as you suggested as well as applying the st.group = 'B' in the tbl4 ON achieved what I wanted. Graham -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: 01 February 2005 00:03 To: Graham Cossey Cc: mysql@lists.mysql.com Subject: RE: Help with a query using multiple LEFT JOINS If you mean that you want to get a row even if tbl2 does not have a matching row for dcode, then move the conditions into the ON clause. Example based off of what you had: SELECT FROM tbl1 as d LEFT JOIN tbl2 as r ON (d.dcode=r.dcode AND r.mcode='AB' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE d.status!='X' AND d.region='1A' AND st.group = 'B' GROUP BY d.dcode, r.code You may want to do the same for tbl4 depending on the behavior you are looking for. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Graham Cossey Sent: Monday, January 31, 2005 5:48 PM To: mysql@lists.mysql.com Subject: Help with a query using multiple LEFT JOINS I'm hoping someone can help with a little problem I'm having with a query. In the query below I wish to return as least one row per tbl1, however I am only getting rows where there is at least an entry for tbl2 : SELECT ... FROM tbl1 as d LEFT JOIN tbl2 as r ON d.dcode=r.dcode LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE r.mcode='AB' AND d.status!='X' AND d.region='1A' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12) AND st.group = 'B' GROUP BY d.dcode, r.code Can anyone help me see the light and show me where I'm being stupid? TIA Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a query using multiple LEFT JOINS
I'm hoping someone can help with a little problem I'm having with a query. In the query below I wish to return as least one row per tbl1, however I am only getting rows where there is at least an entry for tbl2 : SELECT ... FROM tbl1 as d LEFT JOIN tbl2 as r ON d.dcode=r.dcode LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE r.mcode='AB' AND d.status!='X' AND d.region='1A' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12) AND st.group = 'B' GROUP BY d.dcode, r.code Can anyone help me see the light and show me where I'm being stupid? TIA Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sqlyog
snip You're not serious are you? You don't really mean that you want a restore to be automatic do you? snip If you really do mean that you want to completely automate the restore, what event is going to trigger the restore? Rhino I believe what the OP is after is restoring a database dump that has been sent from elsewhere (via email). His previous post (restoring database) stated that was his intention. In a reply to that post I did say that he could use: mysql [-p] [-u] [-h] db_name dumpfile.sql Graham - Original Message - From: PRATHIMA RAO [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, January 15, 2005 10:53 AM Subject: sqlyog hai, am using sqlyog for mysql i have taken back up in batch ie .sql i want to restore automatically can it be possible to restore by writing a code in vb using sqlyog i have written a programme in vb for taking back up automatically need help to restore the same automatically regards prathioma rao -- -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- 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: backup of database
-Original Message- From: N. Kavithashree [mailto:[EMAIL PROTECTED] Sent: 18 January 2005 05:35 To: mysql@lists.mysql.com Subject: backup of database hello, how to take a backup of database? i have a database with name1 now i want to move all the tables into another database? how to do? Could be mysqldump that you need: http://dev.mysql.com/doc/mysql/en/mysqldump.html examples: mysqldump [--no-data] name1 | mysql name2 mysqldump [--no-data] name1 name1.sql HTH Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: restoring database
snip i have written a bat file in which the data databackup has been taken in sql it runs automatically every 1 hour and sends a automatically through email now at the other end i have to manually restore the database can any one give me the codes to restore the database automatically in vb or as a bat file the databack up say for a database test is test.sql I am assuming you used mysqldump to create your test.sql mysql [-u user] [-ppassword] new_db_name test.sql If new_db_name already contains data within test.sql you may need to use something like --add-drop-table when you create test.sql. mysqldump --add-drop-table my_db test.sql HTH Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlcc
Hi Robin Have you checked you have INSERT authorities to the database as the user you connected as? I have mysqlcc 0.9.2-beta (on Win2k) connecting to a mySQL 4.0.20 (Linux) database and it's been working fine for months. Graham -Original Message- From: Robin Lynn Frank [mailto:[EMAIL PROTECTED] Sent: 11 December 2004 01:23 To: MySQL Mailing List Subject: mysqlcc I'm obviously missing something. I can use mysqlcc to change data in a row of a table and save it. But, if I try to insert a row, add data and save it, it never gets saved. What am I overlooking? -- /\ ASCII RIBBON Robin Lynn Frank \ / CAMPAIGN Director of Operations X AGAINST Paradigm-Omega, LLC / \ HTML MAILhttp://www.paradigm-omega.com/ = Spambots visit http://paradigm-omega.net/cgi-bin/custmail.cgi = Don't mind me. I am having a bad (day|week|month|year). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select member when it meets two requirements
Am I missing something? Will this not do the trick: SELECT DISTINCT member_id FROM table WHERE specialty_id IN(6,33); Graham -Original Message- From: Johan Höök [mailto:[EMAIL PROTECTED] Sent: 30 November 2004 19:56 To: Mike Zornek Cc: [EMAIL PROTECTED] Subject: Re: Select member when it meets two requirements Hi Mike, you should be able to do: SELECT DISTINCT t.member_id FROM table t INNER JOIN table t2 ON t2.member_id = t.member_id AND t2.speciality_id = 2 WHERE t.speciality_id = 6 /Johan Mike Zornek wrote: I'm very much a noob when it comes to MySQL .. Historically I've only used it for storage. I need help. I have a table: ++---+--+-+--- --+--- -+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+--- --+--- -+ | memberspecialty_id | int(10) unsigned | | PRI | NULL| auto_increment | | member_id | smallint(10) unsigned | | MUL | 0 | | | specialty_id | tinyint(3) unsigned | | MUL | 0 | | ++---+--+-+--- --+--- -+ How would I select all distinct member_id that have a specialty_id of 6 and 33? Thanks! ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- 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: Help with query performance anomaly
Thanks for the advice Steven, I'll bear it in mind and do some reading. Graham -Original Message- From: Steven Roussey [mailto:[EMAIL PROTECTED] Sent: 13 November 2004 02:52 To: 'Graham Cossey' Cc: [EMAIL PROTECTED] Subject: RE: Help with query performance anomaly For production systems, I would never let the mysql optimizer guess a query plan when there are joins of big tables and you know exactly how it should behave. Once you think a query is finished, you should optimize it yourself. Use STRAIGHT_JOIN and USE INDEX as found here in the manual: http://dev.mysql.com/doc/mysql/en/JOIN.html STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order. http://dev.mysql.com/doc/mysql/en/SELECT.html The use of USE INDEX, IGNORE INDEX, FORCE INDEX to give the optimizer hints about how to choose indexes is described in section 14.1.7.1 JOIN Syntax. -steve-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly (SOLVED)
It turns out that it appears to be a data discrepancy that caused the query optimiser to, well, not optimise. I thought the main table (r) with 3million records would be the problem, but it was table p with 3100 records on the live server and 3082 records on my dev pc that caused the problem. Although the results of show create table etc were identical on both machines, uploading the data from dev to live has solved the problem. Thanks to all that offered advice. Graham snip size=big/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query performance anomaly
Hi Can someone offer any advice on a strange problem I have at present... If I run a certain query (see below) on my local development PC using mysqlcc it returns in 3.7s. If I run the exact same query on my live webserver (again using mysqlcc) I have yet to get a result !! Both databases have the same table definitions (live db originally created from mysqldump of dev PC) and have exactly the same [number of] records in each table. Both machines are running MySQL 3.23.58. Dev PC if Fedora Core2, live is RedHat 9. Other than this one query all else appears normal, any suggestions? Let me know if you need more info and I'll attempt to supply it... Many thanks Graham Query: SELECT code, sum(qty) as total FROM table1 as d, db2.table2 as r, table3 as p WHERE year=2004 AND month=10 AND r.col1=p.col1 and d.code=p.code and from_period = 200410 and to_period 200410 and d.col3!='6' GROUP BY code -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly
Thanks Shaun EXPLAIN shows the same 'possible keys' for each table but 'key' and 'key-len' columns are different, as are the 'rows' as well of course. I guess this points to a probable difference in key definitions? Can 2 installations with the same table definitions produce different results like this? Maybe something in the configs? Thanks Graham -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 11 November 2004 16:28 To: Graham Cossey Cc: [EMAIL PROTECTED] Subject: Re: Help with query performance anomaly What does EXPLAIN show for the query on both systems? (I am wondering if you may have an index on your development system that you do not have on your production server.) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 11:19:08 AM: Hi Can someone offer any advice on a strange problem I have at present... If I run a certain query (see below) on my local development PC using mysqlcc it returns in 3.7s. If I run the exact same query on my live webserver (again using mysqlcc) I have yet to get a result !! Both databases have the same table definitions (live db originally created from mysqldump of dev PC) and have exactly the same [number of] records in each table. Both machines are running MySQL 3.23.58. Dev PC if Fedora Core2, live is RedHat 9. Other than this one query all else appears normal, any suggestions? Let me know if you need more info and I'll attempt to supply it... Many thanks Graham Query: SELECT code, sum(qty) as total FROM table1 as d, db2.table2 as r, table3 as p WHERE year=2004 AND month=10 AND r.col1=p.col1 and d.code=p.code and from_period = 200410 and to_period 200410 and d.col3!='6' GROUP BY code -- 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: Help with query performance anomaly
Thanks Shaun EXPLAIN shows the same 'possible keys' for each table but 'key' and 'key-len' columns are different, as are the 'rows' as well of course. I guess this points to a probable difference in key definitions? Can 2 installations with the same table definitions produce different results like this? Maybe something in the configs? [snip] What does EXPLAIN show for the query on both systems? (I am wondering if you may have an index on your development system that you do not have on your production server.) [snip] Can someone offer any advice on a strange problem I have at present... If I run a certain query (see below) on my local development PC using mysqlcc it returns in 3.7s. If I run the exact same query on my live webserver (again using mysqlcc) I have yet to get a result !! Both databases have the same table definitions (live db originally created from mysqldump of dev PC) and have exactly the same [number of] records in each table. Both machines are running MySQL 3.23.58. Dev PC if Fedora Core2, live is RedHat 9. Other than this one query all else appears normal, any suggestions? Let me know if you need more info and I'll attempt to supply it... Many thanks Graham [snip] I've done mysqldumps of the tables involved on both machines and the create table definitions and key definitions are identical. The results of my EXPLAINs are pasted below. Thanks Graham DEV BOX: EXPLAIN SELECT d.dcode, sum(qty) as total FROM table1 as d, db2.table2 as r, table3 as p WHERE year=2004 AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode and from_period = 200410 and to_period 200410 and d.region!='6' GROUP BY dcode +---+---+--- -+--+-+-+--+ --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+--- -+--+-+-+--+ --+ | d | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 322 | Using where; Using temporary; Using filesort | | p | index | PRIMARY | PRIMARY | 19 | [NULL] | 6082 | Using where; Using index | | r | ref | PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st | yr_mn_pc | 13 | const,const,p.pcode | 41 | Using where | +---+---+--- -+--+-+-+--+ --+ LIVE SERVER: EXPLAIN SELECT d.dcode, sum(qty) as total FROM table1 as d, db2.table2 as r, table3 as p WHERE year=2004 AND month=10 AND r.pcode=p.pcode and d.dcode=p.dcode and from_period = 200410 and to_period 200410 and d.region!='6' GROUP BY dcode +---+--+ +-+-+-+---+- -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+ +-+-+-+---+- -+ | d | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 322 | Using where; Using temporary; Using filesort | | r | ref | PRIMARY,yr_mn_mk_rng_dr,yr_mn_st,yr_mn_pc,yr_mn_f,yr_mn_mk_st | PRIMARY | 8 | const,const | 89618 | Using where | | p | ref | PRIMARY | PRIMARY | 4 | r.pcode | 2 | Using where; Using index | +---+--+ +-+-+-+---+- -+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly
[big snip] These are two different plans. Your development machine is using the index yr_mn_pc on the r table and is joining that table last. On your production server, the r table is joined second and is joined by the index PRIMARY. Let me know how the ANALYZE TABLE I suggested in a previous message works out to help the statistics. I have run ANALYZE on all tables on the live server and the result of EXPLAIN is the same as before. Any further suggestions? I'm off to double check the create table stuff once more... Thanks for your help (and patience!!) Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly
Everything seems fine except for this one query. I'm not sure quite what the results of top will say or what they mean if it's a Virtual Private Server environment? Nothing untoward in /var/log/messages or var/log/httpd/error_log or virtual host httpd logs. Have just run 'top' on the live server... Before running the query I get: 13:56:09 up 45 days, 11:47, 1 user, load average: 0.00, 0.28, 0.44 24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle CPU1 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle CPU2 states: 0.0% user 0.1% system0.0% nice 0.0% iowait 99.4% idle CPU3 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle Mem: 6203744k av, 6194148k used,9596k free, 0k shrd, 304848k buff 1948476k active,3601304k inactive Swap: 4192956k av, 1876604k used, 2316352k free 4081216k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 7622 15 0 904 904 748 R 0.1 0.0 0:00 2 top 1 root 9 0 468 440 420 S 0.0 0.0 2:58 1 init 1733 root 9 0 548 516 480 S 0.0 0.0 0:35 1 syslogd 2242 root 8 0 808 736 684 S 0.0 0.0 0:11 0 xinetd 3393 root 8 0 576 552 512 S 0.0 0.0 0:21 1 crond 15329 root 9 0 1284 1156 1088 S 0.0 0.0 0:27 2 sshd 3264 root 8 0 3676 1548 1488 S 0.0 0.0 0:37 2 httpd 15296 apache 9 0 9904 8872 4752 S 0.0 0.1 0:00 2 httpd 4576 apache 9 0 9876 8804 4344 S 0.0 0.1 0:01 1 httpd 8992 root 9 0 1000 1000 848 S 0.0 0.0 0:00 1 mysqld_safe 10433 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 11360 mysql 8 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 11395 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 0 mysqld 11425 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 11456 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 3 mysqld 11491 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 12128 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 12162 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 0 mysqld 12193 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 12224 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 1 mysqld 32418 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 5284 9 0 2288 2244 2068 S 0.0 0.0 0:00 3 sshd 5538 9 0 1292 1292 984 S 0.0 0.0 0:00 1 bash Now, I don't know if it's coincidental or not but after setting the query running and re-issuing the top command I get: 13:59:49 up 45 days, 11:51, 1 user, load average: 0.98, 0.61, 0.53 26 processes: 24 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 75.0% user 25.0% system0.0% nice 0.0% iowait 0.0% idle CPU1 states: 76.0% user 24.0% system0.0% nice 0.0% iowait 0.0% idle CPU2 states: 63.0% user 36.0% system0.0% nice 0.0% iowait 0.0% idle Floating point exception Does not look good to me !! Comments? Advice? Thanks Graham -Original Message- From: Jamie Kinney [mailto:[EMAIL PROTECTED] Sent: 11 November 2004 19:25 To: Graham Cossey Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Help with query performance anomaly How do the OS statistics look on both boxes. Do top, sar, vmstat or iostat show any CPU, memory or I/O performance issues? Does anything odd appear in the /var/log/messages file? -Jamie On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey [EMAIL PROTECTED] wrote: [big snip] These are two different plans. Your development machine is using the index yr_mn_pc on the r table and is joining that table last. On your production server, the r table is joined second and is joined by the index PRIMARY. Let me know how the ANALYZE TABLE I suggested in a previous message works out to help the statistics. I have run ANALYZE on all tables on the live server and the result of EXPLAIN is the same as before. Any further suggestions? I'm off to double check the create table stuff once more... Thanks for your help (and patience!!) Graham -- 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: Which PHP for MySQL 4.1
[snip] of course, YMMV ... [snip] Sorry, YMMV? What does that mean? Is there a reference somewhere for all these acronyms? Thanks Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Which PHP for MySQL 4.1
[snip] Sorry, YMMV? What does that mean? Is there a reference somewhere for all these acronyms? Yes it's called Google. Yep, as soon as I posted I knew I should not have :) It's late and I'm getting lazy... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query performance anomaly
[snip] Have just run 'top' on the live server... Before running the query I get: 13:56:09 up 45 days, 11:47, 1 user, load average: 0.00, 0.28, 0.44 24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle CPU1 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle CPU2 states: 0.0% user 0.1% system0.0% nice 0.0% iowait 99.4% idle CPU3 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0% idle Mem: 6203744k av, 6194148k used,9596k free, 0k shrd, 304848k buff 1948476k active,3601304k inactive Swap: 4192956k av, 1876604k used, 2316352k free 4081216k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 7622 15 0 904 904 748 R 0.1 0.0 0:00 2 top 1 root 9 0 468 440 420 S 0.0 0.0 2:58 1 init 1733 root 9 0 548 516 480 S 0.0 0.0 0:35 1 syslogd 2242 root 8 0 808 736 684 S 0.0 0.0 0:11 0 xinetd 3393 root 8 0 576 552 512 S 0.0 0.0 0:21 1 crond 15329 root 9 0 1284 1156 1088 S 0.0 0.0 0:27 2 sshd 3264 root 8 0 3676 1548 1488 S 0.0 0.0 0:37 2 httpd 15296 apache 9 0 9904 8872 4752 S 0.0 0.1 0:00 2 httpd 4576 apache 9 0 9876 8804 4344 S 0.0 0.1 0:01 1 httpd 8992 root 9 0 1000 1000 848 S 0.0 0.0 0:00 1 mysqld_safe 10433 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 11360 mysql 8 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 11395 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 0 mysqld 11425 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 11456 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 3 mysqld 11491 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 12128 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 12162 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 0 mysqld 12193 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 12224 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 1 mysqld 32418 mysql 9 0 17036 16M 2012 S 0.0 0.2 0:00 2 mysqld 5284 9 0 2288 2244 2068 S 0.0 0.0 0:00 3 sshd 5538 9 0 1292 1292 984 S 0.0 0.0 0:00 1 bash Now, I don't know if it's coincidental or not but after setting the query running and re-issuing the top command I get: 13:59:49 up 45 days, 11:51, 1 user, load average: 0.98, 0.61, 0.53 26 processes: 24 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 75.0% user 25.0% system0.0% nice 0.0% iowait 0.0% idle CPU1 states: 76.0% user 24.0% system0.0% nice 0.0% iowait 0.0% idle CPU2 states: 63.0% user 36.0% system0.0% nice 0.0% iowait 0.0% idle Floating point exception Does not look good to me !! [snip] I have now managed to get a top while the query is running: 14:29:52 up 45 days, 12:21, 1 user, load average: 0.69, 0.28, 0.39 25 processes: 23 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 71.1% user 28.0% system0.0% nice 0.0% iowait 0.0% idle CPU1 states: 68.0% user 31.0% system0.0% nice 0.0% iowait 0.0% idle CPU2 states: 71.0% user 28.0% system0.0% nice 0.0% iowait 0.0% idle CPU3 states: 80.0% user 19.0% system0.0% nice 0.0% iowait 0.0% idle Mem: 6203744k av, 5764148k used, 439596k free, 0k shrd, 257900k buff 1839520k active,3282316k inactive Swap: 4192956k av, 1881496k used, 2311460k free 3687672k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 19462 mysql 14 0 19968 19M 2016 R95.4 0.3 0:38 0 mysqld 25248 10 0 1004 1004 748 R 0.3 0.0 0:00 3 top 1 root 9 0 468 440 420 S 0.0 0.0 2:58 1 init 1733 root 9 0 548 516 480 S 0.0 0.0 0:35 3 syslogd 2242 root 8 0 808 736 684 S 0.0 0.0 0:11 3 xinetd 3393 root 9 0 576 552 512 S 0.0 0.0 0:21 2 crond 15329 root 9 0 1284 1156 1088 S 0.0 0.0 0:27 0 sshd 3264 root 9 0 3676 1548 1500 S 0.0 0.0 0:37 1 httpd 15296 apache 9 0 10632 9608 4768 S 0.0 0.1 0:01 0 httpd 4576 apache 9 0 10036 8964 4344 S 0.0 0.1 0:01 3 httpd 8992 root 9 0 1000 1000 848 S 0.0 0.0 0:00 1 mysqld_safe 10433 mysql 9 0 19968 19M 2016 S 0.0 0.3 0:00 1 mysqld 11360 mysql 9 0 19968 19M 2016 S 0.0 0.3 0:00 2 mysqld 11395 mysql 9 0 19968 19M 2016 S 0.0 0.3 0:00 0 mysqld 11425 mysql 9 0 19968 19M 2016 S 0.0 0.3 0:00 2 mysqld 11456 mysql
RE: Commercial Licensing Question
http://www.mysql.com/company/legal/licensing/commercial-license.html : With a commercial non-GPL MySQL server license, one license is required per database server (single installed MySQL binary). There are no restrictions on the number of connections, number of CPUs, memory or disks to that one MySQL database server. The MaxDB server is licensed per CPU or named user. -Original Message- From: Sujith Manuel [mailto:[EMAIL PROTECTED] Sent: 20 October 2004 07:26 To: [EMAIL PROTECTED] Subject: Commercial Licensing Question Hi, We intend to use MySQL database in one of our commercial applications. We would like to know whether we need to buy MySQL license for each of our product installations. Or How many installations can we make with one commercial license of MySQL database. Expecting the reply at the earliest. Thanks in advance, Sujith Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
