On Fri, Aug 29, 2008 at 4:38 AM, Chris Haensel <phpmailing@xxxxxxxxxxxxx> wrote: > > > -:- -----Original Message----- > -:- From: Thijs Lensselink [mailto:dev@xxxxxxxx] > -:- Sent: Friday, August 29, 2008 10:31 AM > -:- Cc: php-general@xxxxxxxxxxxxx > -:- Subject: Re: Braindead > -:- > -:- Chris Haensel wrote: > -:- > Hi guys, > -:- > > -:- > maybe I am too stupid, but I can not see a solution for > -:- this. Have been > -:- > cracking up my brain for the last hours, so I finally > -:- dare to ask. > -:- > > -:- > I have a database table with some columns > -:- > > -:- > > -:- > text1 | text2 | text3 | text4 > -:- > ---------------------------------------------- > -:- > > -:- > > -:- > Now, for some stats thingy, I am trying to get the COUNT > -:- of distinct entries > -:- > So, i would like to get the count of entries where text2 > -:- is foo OR text3 is > -:- > foo > -:- > > -:- > I have 20.863 entries at the moment, and it takes quite > -:- a lot of time > -:- > reading through that stuff. > -:- > I have tried > -:- > > -:- > SELECT COUNT(*) as mycount FROM mytable WHERE text2 = > -:- 'foo' and text3 = > -:- > 'foo' > -:- > > -:- > but I neither get an error nor any output. It just loads > -:- and loads without > -:- > any output... > -:- > > -:- > I am out of ideas :o( Help, anybody? :o)) > -:- > > -:- > Chris > -:- > > -:- > > -:- > > -:- > > -:- The amount of records is not that big to slow things down. > -:- Did you use > -:- indexes on your database? And did you try running the query through > -:- commandline or phpMyAdmin? > -:- > -:- > -:- -- > -:- PHP General Mailing List (http://www.php.net/) > -:- To unsubscribe, visit: http://www.php.net/unsub.php > -:- > -:- > > Hi Thijs, > > thanks for the reply. I got mixed up with my tables :o) It's got 178.456 > entries :o)) > > Anyhow, I tried it in a PHP script. > > $gq = "SELECT planned_dep_ap, COUNT(*) as apcount FROM flightdb > GROUP BY planned_dep_ap"; > $gd = mysql_query($gq) or die(mysql_error()); > while($ga = mysql_fetch_assoc($gd)) { > $icao = $ga['planned_dep_ap']; > $apcount = $ga['apcount']; > if($apcount >= 100) { > echo $icao." -> ".$apcount."<br>"; > } > } > > this should get me the count of all entries and output it. It looks like it > does it now, as I do get an output. It just takes like 25+ seconds to show > me the output :o( Any idea how I can get it faster? This isn't really the same thing you asked in your original post. The original included a WHERE clause and this version does not. (Granted, your original post seemed slightly confused as well, since you first said you wanted results "where text2 is foo OR text3 is foo", but then your example said "WHERE text2 = 'foo' and text3 = 'foo' ", so I'm not sure if you wanted a union (OR) or an intersection (AND) of the two matching sets.) I see in your script that you are only echoing rows where the value of $apcount is >= 100. Why not limit your query to that? Then you aren't waiting for a bunch of records to get passed from MySQL to PHP that you aren't even interested in seeing, and PHP doesn't have to waste the cycles to filter them out? > Cheers for your help! > > Chris > > P.S.: Is there a way to order the output by the apcount value? So I can get > highest numbers first? > Absolutely. SELECT planned_dep_ap, COUNT(*) as apcount FROM flightdb GROUP BY planned_dep_ap HAVING apcount >= 100 ORDER BY apcount DESC That should do it. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
