Re: Combining 2 queries into 1?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



After crawling through the MySql reference I finally found the function I
was
looking for, the sollution for combining theese two queries would be :

SELECT count(  *  )  AS online_anonymous, sum( sign( uid )  )  AS
online_members
FROM  `database`

I also benchmarked this and the performance went just as I hoped, I run a
series of x200 and x500 queries within a loop and took the time. Tests are
done
at the live system on the production server som Im pleased with the result,

200x Test 1 in 0.2965 sekunder, 2x queries.
200x Test 2 in 0.1633 sekunder, combo query.
500x Test 1 in 0.8041 sekunder, 2x queries.
500x Test 2 in 0.3949 sekunder, combo query.

--
--
Kim Steinhaug
----------------------------------------------------------------------
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
----------------------------------------------------------------------
www.steinhaug.com - www.easywebshop.no - www.webkitpro.com
----------------------------------------------------------------------

"Kim Steinhaug" <kim@xxxxxxxxxxxxx> wrote in message
news:20040530172422.57933.qmail@xxxxxxxxxxxxxxx
> And ofcourse, one part I didnt take into the equation,
> one complex query doesnt always execute faster than
> two fast queries.
>
> So I might as well be sure that the environment is optimized
> for the two queries im already having...
>
> I still however would like to know if its possible to join the
> two into one.
>
> Thanks in advance.
> --
> --
> Kim Steinhaug
> ----------------------------------------------------------------------
> There are 10 types of people when it comes to binary numbers:
> those who understand them, and those who don't.
> ----------------------------------------------------------------------
> www.steinhaug.com - www.easywebshop.no - www.webkitpro.com
> ----------------------------------------------------------------------
>
> "Kim Steinhaug" <kim@xxxxxxxxxxxxx> wrote in message
> news:20040530172006.44102.qmail@xxxxxxxxxxxxxxx
> > I have theese two queries :
> >
> > select count(*) as online from online where uid='';
> > select count(*) as online from online where uid!='';
> >
> > Each online user has a uid (UserID), so if their logged
> > inn this field will represent their unique uid. If not logged
> > in its set to nothing.
> >
> > To detect how many online users the first query tells me
> > all the "anonymous" users and the second gives "members".
> >
> > Since theese two queries are fired on every page hit, I would
> > assume that if I could join theese queries into one I would
> > atleast save the mysql for a 50% of the queries.
> >
> > Ive veeb experimenting on the count(expression) without
> > any luck, probably since the server is running v4.0.17-standard
> >
> > So I wonder - is there any way I could join the above queries
> > into one single query? Ofcourse I could easily add another
> > field for the database, something like temp which is NULL for
> > anonymous and 1 for user if this makes is easier to group the
> > the query in some wmart way I havnt figured out at the moment.
> >
> > --
> > --
> > Kim Steinhaug
> > ----------------------------------------------------------------------
> > There are 10 types of people when it comes to binary numbers:
> > those who understand them, and those who don't.
> > ----------------------------------------------------------------------
> > www.steinhaug.com - www.easywebshop.no - www.webkitpro.com
> > ----------------------------------------------------------------------

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux