SELECT statement
I'm attempting to collate a webpage showing results by various football teams in various cup competitions, and am trying to minimise the number of selects as best I can. What I'm trying to get out in one statement is the number of home matches played by Burnley, how many they've won, drawn and lost, and the totals goals scored by them and against them. In an ideal world it would be something like the following, but I know this will not work: SELECT COUNT(*) AS matchesplayed, COUNT(result='H') AS homewins, COUNT(result='D') AS drawngames, COUNT(result='A') AS awaywins, SUM(homescore) AS homegoalsscored, SUM(awayscore) AS awaygoalsscored FROM matchstats WHERE (homeTeam = 'Burnley') AND (competition = 'F.A.Cup') Effectively it's like a grouping, but the goals columns are calculations of the entire number of matches. I can achieve it with two statements, but wondered if there was a way of combining the two? Many thanks Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: mysqld-nt claims all available CPU
MySQLd-nt (v4.0.14) is eating up all the available CPU resources on a Windows 2000 server (dual 933 Pentium, 1GB RAM), with a maximum of 40 users so far. I have tried each combination of the default configuration files, and each variety of the executable (nt, max-nt etc.), and it's always the same. The webpage to access the database is written is ASP, and all connections are closed when finished with before the page is finished. Has anyone any ideas why it shoots up to the top like this? Many thanks Gary
mysqld-nt claims all available CPU
MySQLd-nt (v4.0.14) is eating up all the available CPU resources on a Windows 2000 server (dual 933 Pentium, 1GB RAM), with a maximum of 40 users so far. I have tried each combination of the default configuration files, and each variety of the executable (nt, max-nt etc.), and it's always the same. The webpage to access the database is written is ASP, and all connections are closed when finished with before the page is finished. Has anyone any ideas why it shoots up to the top like this? Many thanks Gary
RE: Mysql processlist sleep time
The PHP one is indeed used less, probably by about 10% of the users while it's being tested. I was simply wondering if the idle timeouts were possibly responsible for the CPU usage problems, and I thought (rightly or wrongly?), that setting the 'xxx_timeout' options would close those persistent connections after the set number of seconds. It's just so bizarre that the mysqld program eats up all the available CPU most of the time, inevitably almost grinding things to a halt. I've searched high and low for a solution, asking advice in lots of places, tweaking loads of things here and there, and nothing seems to make any difference whatsoever. I appreciate that Windows, MySQL and PHP is not really the combination of choice though! :-) Many thanks for your reply. Gary -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 07 August 2003 22:55 To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: > Hi all > > I continue to have problems with the CPU usage with MySQL and PHP > under IIS 5 (Win2000). I recently rewrote our messageboards in PHP > (from ASP). I now have both online separately, and if I look at the > processlist, the times on the ASP version rarely hit double figures, > but those on the PHP version often reach several hundred (wait and > inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. > I am at a real loss as to why the processes are not being cleared. I > am using a persistent connection at the top of the webpage, and every > MySQL query is ended with a 'mysql_free_result()' statement, including > before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- 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]
A final Windows MySQL PHP plea
Hi all Is there anybody out there who has managed to successfully configure Win2000, IIS5, MySQL 4.0.14 and PHP 4.3.2 (ISAPI) to work with a couple of hundred users at any one time? I have chucked absolutely everything I can think of at this, but the MySQL (it seems) simply eats all the available CPU within a short space of time (regardless of users) and brings the site to a halt. My last throw of the dice today was to install all on a new Dual 1.8Ghz Pentium, with three hard disks in a RAID array, and 2GB memory, but it's achieved pretty much nothing. I am now desperate, and if anyone has any flash of inspiration for me, I'm all ears. The previous ASP version of the site runs like a dream, but there's something I'm either doing wrong, or this new combination of software simply doesn't like. Many thanks Stressed Gary
FW: A final Windows MySQL PHP plea
Thanks to everybody for all your help and advice. It seems Linux is (Bgoing to HAVE to be the next step, but while I know sod all about it, I (Bhave enlisted the help of a colleague to assist with that side of (Bthings. So I shall crack on with that, and subsequently let you know (Bwhat the outcome is. :-) (BThanks again (BGary (B (B-Original Message- (BFrom: Nils Valentin [mailto:[EMAIL PROTECTED] (BSent: 14 August 2003 02:35 (BTo: Andrew Rothwell (BCc: [EMAIL PROTECTED] (BSubject: Re: A final Windows MySQL PHP plea (B (B (BHi Andrew, (B (BI guess your reply was meant for Gary (the original poster of this (Be-mail). (B (BI will foward your request to the mailing list. (B (BBest regards (B (BNils Valentin (BTokyo/Japan (B (B (B (B (B> WOW!!! (B> That kind of System Power and you are wasting it on Windows and IIS (B> E! (B> (B> Sorry - but Dual Proc Support, with 2gigs of RAM would Love Redhat 9.0 (B (B> like a kid loves chocolate. (B> (B> I don$B!G(Bt know about the more than 100 concurrent users, but bear in (B> mind (as I understand it) that means that you can have up to 100 (B> queries at 1 time, as soon as the query is over, the next user is (B> available for his query. (B> (B> Linux itself can support 1000's of users at one time - (B> You might be surprised. (B> (B> Andrew (B> (B> -Original Message- (B> From: Nils Valentin [mailto:[EMAIL PROTECTED] (B> Sent: Tuesday, August 12, 2003 10:04 PM (B> To: Gary Broughton; [EMAIL PROTECTED] (B> Subject: Re: A final Windows MySQL PHP plea (B> (B> (B> Hi Gary, (B> (B> I understood that the packages provided by MySQL are set to 100 (B> concurrent users by default, so what you ae asking is actually if (B> somebody successfully (B> compiled a version for more than 100 concurrent users and was able to (B> use it (B> in a production environment ? (B> (B> Do I understand that correct ? (B> (B> My guess would be that you are more likely to find Linux users having (B> done such a setup. Unfortunately I haven'Tt had such an experience (B> yet, but as you (B> probably now Dell has made a study (which is also announced on (Bwww.mysq. (B> com) (B> which describes their experience, perhaps it contains the one or the (B> other (B> useful tip. (B> (B> http://www.dell.com/us/en/biz/topics/power_ps2q03-jaffe.htm (B> (B> Best regards (B> (B> Nils Valentin (B> Tokyo/Japan (B> (B> 2003$BG/(B 8$B7n(B 13$BF|(B $B?eMKF|(B 01:21$B!"(BGary Broughton (B> $B$5$s$O=q$-$^$7$?(B: (B> > Hi all (B> > (B> > Is there anybody out there who has managed to successfully configure (B (B> > Win2000, IIS5, MySQL 4.0.14 and PHP 4.3.2 (ISAPI) to work with a (B> > couple of hundred users at any one time? I have chucked absolutely (B> > everything I can think of at this, but the MySQL (it seems) simply (B> > eats all the available CPU within a short space of time (regardless (B> > of (B> > (B> > users) and brings the site to a halt. My last throw of the dice (B> > today (B> > (B> > was to install all on a new Dual 1.8Ghz Pentium, with three hard (B> > disks (B> > (B> > in a RAID array, and 2GB memory, but it's achieved pretty much (B> > nothing. I am now desperate, and if anyone has any flash of (B> > inspiration for me, I'm all ears. The previous ASP version of the (B> > site runs like a dream, but there's something I'm either doing (B> > wrong, or this new combination of software simply doesn't like. (B> > (B> > Many thanks (B> > (B> > Stressed Gary (B> (B> -- (B> --- (B> Valentin Nils (B> Internet Technology (B> (B> E-Mail: [EMAIL PROTECTED] (B> URL: http://www.knowd.co.jp (B> Personal URL: http://www.knowd.co.jp/staff/nils (B (B-- (B--- (BValentin Nils (BInternet Technology (B (B E-Mail: [EMAIL PROTECTED] (B URL: http://www.knowd.co.jp (B Personal URL: http://www.knowd.co.jp/staff/nils (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe: (Bhttp://lists.mysql.com/[EMAIL PROTECTED] (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL, IIS and PHP
Hi Dan Thanks for your reply. No, the process list was showing say 20 or 30 users with just milliseconds of activity, the only one on for any great length of time was myself as root. Stopping the service does return the machine to normality, but then it shoots right back up to 100% within seconds of restarting. I've used the mysqld-nt, mysql-max-nt and mysqld-opt executables, but the result is just the same. I can't understand why this problem has occurred though - in it's current environment of one IIS site using the ASP board (95% of users - live) and one IIS site using the new PHP (5% - test) it runs like a dream, but when I enable PHP on the live site and direct all to that, it just collapses. I'm pretty sure all the settings for the two sites are identical, and the my.ini and php.ini obviously are not changed! Regards Gary -Original Message- From: Dan [mailto:[EMAIL PROTECTED] Sent: 04 August 2003 21:59 To: Gary Broughton; [EMAIL PROTECTED] Subject: Re: MySQL, IIS and PHP Gary Broughton wrote: >Hi all > >I've just recoded a website in PHP from ASP, running off IIS 5. It has >been tested by a dozen users over the weekend, but now I have put it >live the CPU utilisation is up at 100%, mainly swallowed up by >mysql-nt.exe. I wondered if anyone could offer any advice as to why >this could be (the MySQL settings have not changed, and are as they >were when it was accessed via the ASP code). > >Regards > >Gary > > > > What does 'mysqladmin processlist -p PASSWORD' give? Are there any processes that have been running for ages? What about if you shut down MySQL and restart it? -- 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 processlist sleep time
Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Has anybody any ideas on why this can be? I cannot find out how to tell what is causing the long sleep period. Many thanks Gary
RE: Mysql processlist sleep time
I don't believe Windows services can be started with any priority types. MySQL is on it's own box with the ASP version, which works like a dream. It's simply that whenever the PHP version is used (either as a solitary website on another box or as another website on the same box), that's when mysqld goes mad. The odd thing is that the software is effectively identical between the two languages, and has simply had functions changed as and where appropriate. I have also used the programming methods as used in the book "PHP and MySQL Web Development" too, as well as reading advice from "MySQL Second Edition" by Paul Dubois. I just wonder if this is a problem that is unable to be solved?! -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED] Sent: 08 August 2003 17:16 To: 'Gary Broughton'; [EMAIL PROTECTED] Subject: RE: Mysql processlist sleep time I think I see the problem. Mysql really needs to be on it's own box. It's designed to just use as much power as it can find. This is a good thing for those with dedicated machines. I don't know if there's a configuration setup that tell mysql that it's not the head honcho. Does Windows have a way to start a process (mysql) in low priority? > -Original Message- > From: Gary Broughton [mailto:[EMAIL PROTECTED] > Sent: Friday, August 08, 2003 4:20 AM > To: [EMAIL PROTECTED] > Subject: RE: Mysql processlist sleep time > > > The PHP one is indeed used less, probably by about 10% of the users > while it's being tested. I was simply wondering if the idle timeouts > were possibly responsible for the CPU usage problems, and I thought > (rightly or wrongly?), that setting the 'xxx_timeout' options would > close those persistent connections after the set number of seconds. > > It's just so bizarre that the mysqld program eats up all the available > CPU most of the time, inevitably almost grinding things to a halt. > I've searched high and low for a solution, asking advice in lots of > places, tweaking loads of things here and there, and nothing seems to > make any difference whatsoever. I appreciate that Windows, MySQL and > PHP is not > really the combination of choice though! :-) > > Many thanks for your reply. > Gary > > -Original Message- > From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] > Sent: 07 August 2003 22:55 > To: Gary Broughton > Cc: [EMAIL PROTECTED] > Subject: Re: Mysql processlist sleep time > > > On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: > > Hi all > > > > I continue to have problems with the CPU usage with MySQL and PHP > > under IIS 5 (Win2000). I recently rewrote our messageboards in PHP > > (from ASP). I now have both online separately, and if I > look at the > > processlist, the times on the ASP version rarely hit double > figures, > > but those on the PHP version often reach several hundred (wait and > > inactivity timeouts are set to 300 - I thought this would > stop it?!). > > I'm not sure what the problem is. From your description, it sounds as > if the PHP one is either used less or is more efficient about using > connections, since they're idle more often. > > > I am at a real loss as to why the processes are not being > cleared. I > > am using a persistent connection at the top of the webpage, > and every > > MySQL query is ended with a 'mysql_free_result()' > statement, including > > > before any redirects using the 'header' command. > > Hang on. You're using *persistent* connections, so why would > you expect > them not to persist? > > Jeremy > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL, IIS and PHP
Hi all I've just recoded a website in PHP from ASP, running off IIS 5. It has been tested by a dozen users over the weekend, but now I have put it live the CPU utilisation is up at 100%, mainly swallowed up by mysql-nt.exe. I wondered if anyone could offer any advice as to why this could be (the MySQL settings have not changed, and are as they were when it was accessed via the ASP code). Regards Gary
UNION or not?
Hi all I want to provide a list of up to 20 online users on our network of football forums, but would like to list those live on the current team first, before "filling" any remainder with those online using a different team. I couldn't see any way of getting it all into one select (which in English 'speak' would be like "order by team 380, then get the rest" I suppose?), and saw only the UNION function as the possible solution. All I'm after, if possible, is to know if I'm using the most efficient method of retrieving the data, and also whether putting the extra "LIMIT 20" outside the UNION would indeed pick up the first 20 records only, even though there's a potential for 40. (SELECT user_id, username, last_login FROM users WHERE unix_timestamp(last_access) > unix_timestamp()-1440 AND user_id <> '9' AND last_team = '380' ORDER BY last_login LIMIT 20) UNION (SELECT user_id, username, last_login FROM users WHERE unix_timestamp(last_access) > unix_timestamp()-1440 AND last_team <> '380' ORDER BY last_login LIMIT 20) LIMIT 20 Incidentally, the "user_id <> '25'" is only there to prevent display of the name of the current online user in the list. Many thanks as always Gary
WHERE x IN (SELECT x ...
I believe the multiple 'SELECT' statements will not be included until version 4.1 is released? If this is the case, is there a crude workaround method of attempting to perform the following until such a time as it is? SELECT COUNT(*) FROM messages WHERE forum_id IN (SELECT forum_id FROM forums WHERE team_no = 400) Many thanks Gary
More COUNT GROUP ORDER woes
I'm having a problem similar to one I mentioned last week. I'm trying to retrieve the number of posts a selected user has made on a selected forum, plus details of the record of his/her latest post (date and subject). SELECT COUNT(*) AS postcount, m.user_id, m.subject, m.posting_date, u.username, u.registered FROM messages m, users u WHERE m.forum_id = '297' AND m.user_id = '4910' AND m.user_id = u.user_id GROUP BY m.user_id ORDER BY m.posting_date DESC Basically this statement ignores anything to do with the ordering, and brings out a post (seemingly at random) which is not the latest (i.e. I'm hoping and expecting for 30th July, but get a record from 19th May). If I use "MAX(m.posting_date)" I can retrieve the latest date, but any other data from the 'message' fields (i.e. the subject) relates to this record from 19th May. Is there a way of getting the count and this latest record in the one statement. Any help, as always, would be much appreciated. Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Repair table
It may or may not help, but if you connect to the database using MySQLCC, you can highlight all the tables and repair, optimise or check in one go (in the Windows version at least!). Gary -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: 24 July 2003 16:15 To: MySQL LIST Subject: Repair table Is there a way to issue a "REPAIR TABLE table_name" command to all tables at once? Something like "REPAIR TABLE *" Thanks, Jeff McKeon -- 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: GROUP BY ORDER BY
Hi Many thanks to one and all for your time and assistance with my question. I used the 'AS cnt' method and it works brilliantly. Simple isn't it? :-) Cheers Gary -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: 24 July 2003 14:01 To: [EMAIL PROTECTED] Subject: Re: GROUP BY ORDER BY "Gary Broughton" <[EMAIL PROTECTED]> wrote: > > I wonder if someone could help with what I assume is a simple query > using GROUP and/or ORDER statements (something I struggle to get to > grips with). I am trying to get a list of users who have posted to a > forum by number of posts descending, but am unable to find the right > statement to do it. > > > > At the moment I have: "select count(*), user_id FROM messages WHERE > forum_id = 294 GROUP BY user_id" . which gets me what I want, but in a > random user order. > > > > I have looked through the MySQL documentation, but have been unable to > hit on the combination of functions needed to get what I need (which is > effectively 'ORDER BY count(*) DESC'). SELECT COUNT(*) AS cnt, user_id FROM messages WHERE forum_id = 294 GROUP BY user_id ORDER BY cnt DESC -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY ORDER BY
Hi I wonder if someone could help with what I assume is a simple query using GROUP and/or ORDER statements (something I struggle to get to grips with). I am trying to get a list of users who have posted to a forum by number of posts descending, but am unable to find the right statement to do it. At the moment I have: "select count(*), user_id FROM messages WHERE forum_id = 294 GROUP BY user_id" . which gets me what I want, but in a random user order. I have looked through the MySQL documentation, but have been unable to hit on the combination of functions needed to get what I need (which is effectively 'ORDER BY count(*) DESC'). Can anybody help? Many thanks Gary
SQL statement dilemna
I'm attempting to write one SQL statement to retrieve data in a particular way, and don't seem to be able to do it despite dozens of attempts (indeed maybe it cannot be done), but wondered if anyone could suggest anything, such as a function I may have missed that can do it, or that it simply isn't possible! I have to list player's histories in a football team throughout his career, and only have the fixture table and appearance table to go off: Fixtures Fixid, Fixdate, Hometeam, Awayteam 1, 10 May 2003, Lancashire, Sussex 2, 12 May 2003, Sussex, Northants 3, 15 May 2003, Essex, Durham 4, 16 May 2003, Durham, Leicestershire 5, 20 May 2003, Sussex, Derbyshire Appearances Playerid, Fixid, Teamid, Substitute? Anderson, 1, Sussex, 0 Anderson, 2, Sussex, 1 Anderson, 3, Durham, 0 Anderson, 4, Durham, 0 Anderson, 5, Sussex, 1 What I am trying to do is retrieve a count of how many games a player has started, or been substitute for, per team, per chronological spell at the team (i.e. in this instance he's played for Sussex in two separate spells, so I need that information grouped in two different returned records), such as: Playerid, Teamid, count(not a substitute), count(substitute), first_game_for_team Anderson, Sussex, 1, 1, 10 May 2003 Anderson, Durham, 2, 0, 15 May 2003 Anderson, Sussex, 0, 1, 20 May 2003 My latest SQL statement is: SELECT COUNT(*), a.playerid, a.substitute, a.teamid, f.fixdate, t.name FROM fixture f, apps a, team t WHERE a.player_id = 'Anderson' AND a.fixid = f.fixid AND a.teamid = t.teamid GROUP BY a.teamid, a.substitute ORDER BY f.fixdate DESC . but this simply creates two records per team, one for substitute appearances, one for starting appearances. Any pointers would be greatly appreciated, and if I'm asking an inappropriate question for the group please accept my apologies in advance. Many thanks Gary Broughton
MySQL 4 maintenance tasks on Windows
Hi all I've recently moved a database to MySQL from MSSQL, and am running it on a Windows 2000 Server. Can anybody advise me on the best method for running regular maintenance tasks (i.e. optimize and analyze)? I assumed I would be best creating a batch file containing and running 'myisamchk ...' line-by-line, via Windows scheduled tasks, but I don't appear to be having much luck. Any assistance or pointers in the right direction would be very much appreciated. Regards Gary Broughton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto decrement
Does anyone know if there is a way of setting an auto numbered field that starts at say 2,000,000 and decreases by one on each insert? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
