Re: which solution is better for $count and @cols

[Perrin Harkins]

On Thu, Oct 23, 2008 at 10:31 PM, Fayland Lam [EMAIL PROTECTED] wrote:
 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)

If you're talking about Perl/DBI, doing that normally loads the entire
result set into your program's memory.  Using a LIMIT avoids that.

- Perrin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

which solution is better for $count and @cols

[Fayland Lam]

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?

Thanks.

-- 
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
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: which solution is better for $count and @cols

[D. Dante Lorenso]

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
[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: which solution is better for $count and @cols

[Fayland Lam]

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
[EMAIL PROTECTED]





--
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
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]