Re: which solution is better for $count and @cols
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]
Re: which solution is better for $count and @cols
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]
Re: which solution is better for $count and @cols
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]