Re: which solution is better for $count and cols

From: Fayland Lam (faylandgmail.com)
Date: Thu Oct 23 2008 - 23:01:25 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Thanks very much.

D. Dante Lorenso wrote:
> Fayland Lam wrote:
>> well, we have a where $where, and I want some cols depends on $start,
>> $rows. besides, I want $count too.
>> so we have two solution here.
>> A, two SQLs.
>> 1, SELECT COUNT(*) FROM table WHERE $where
>> 2, SELECT col FROM table WHERE $where LIMIT $start, $rows.
>>
>> B one SQLs with some operation
>> SELECT col FROM table WHERE $where
>> while $count is scalar cols and real cols is splice(cols, $start,
>> $rows)
>>
>> which solution is better? or it depends on the $count, big count A is
>> better and small is B?
>
> Use A always. You might get away with using SQL_CALC_FOUND_ROWS, but
> I've always found that I need to know the total row count before I run
> the query because if you are asking for a $start which is beyond the
> $count, I want to modify $start before running the second query.
>
> It ends up being like this:
>
> A, two SQLs
> 1, SELECT COUNT(*) FROM table WHERE $where
> 1.5, if ($count < $start) { $start = 1; }
> 2, SELECT col FROM table WHERE $where LIMIT $start, $rows.
>
> Option B is horrible for large result sets. Only drawback to A is the
> tediousness of having 2 queries, but you get over that once you
> develop a pattern for writing them that way.
>
> -- Dante
>
> ----------
> D. Dante Lorenso
> dantelarkspark.com
>
>

--
Fayland Lam // http://www.fayland.org/
Foorum based on Catalyst // http://www.foorumbbs.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]