That was just an example for Michael.
Dec 5, 2001, 5:32 PM
Administrator (9387 posts)
Dec 5, 2001, 5:32 PM
Post #27 of 29
Views: 3116
Also, this is much faster:
SELECT * from users limit 20
SELECT COUNT(*) FROM users
So if you are only displaying 20 users at a time, but need the total to get paging working, it's better to do two queries (much much faster on large tables).
Cheers,
Alex
--
Gossamer Threads Inc.
SELECT * from users limit 20
SELECT COUNT(*) FROM users
So if you are only displaying 20 users at a time, but need the total to get paging working, it's better to do two queries (much much faster on large tables).
Cheers,
Alex
--
Gossamer Threads Inc.
Dec 5, 2001, 5:49 PM
User (378 posts)
Dec 5, 2001, 5:49 PM
Post #28 of 29
Views: 3149
Paul:
The difference in size of the queries is so small that it doesn't seem to make sense to rearrange the query for that reason.
It is easy to format the data how you like as well.
Rearranging the query is going to slow down the script. Instead of just passing the SQL statement in a clear fashion to the subroutine/function - The SQL module has to look at it, get the arrays from it and construct the query.
When you say you have to write "whole" queries, you'll find they aren't that much bigger...
Alex:
Using the extra query to do count(*) would be faster when there is a lot of rows. If you are doing a simple thing such as verifying a user/password combo then you can save the need to do a count(*) by searching for the user/pass in a select statement and then doing a num_rows on it. That way you can check to see if the user exists, and get their details in the $USER array in one query instead of two which would be faster as there would be either 1 row or 0 rows in the result.
Cheers,
Michael Bray
The difference in size of the queries is so small that it doesn't seem to make sense to rearrange the query for that reason.
It is easy to format the data how you like as well.
Rearranging the query is going to slow down the script. Instead of just passing the SQL statement in a clear fashion to the subroutine/function - The SQL module has to look at it, get the arrays from it and construct the query.
When you say you have to write "whole" queries, you'll find they aren't that much bigger...
Alex:
Using the extra query to do count(*) would be faster when there is a lot of rows. If you are doing a simple thing such as verifying a user/password combo then you can save the need to do a count(*) by searching for the user/pass in a select statement and then doing a num_rows on it. That way you can check to see if the user exists, and get their details in the $USER array in one query instead of two which would be faster as there would be either 1 row or 0 rows in the result.
Cheers,
Michael Bray
Dec 5, 2001, 11:54 PM
Administrator (9387 posts)
Dec 5, 2001, 11:54 PM
Post #29 of 29
Views: 3075
Removing SQL from your code is a good thing. You can abstract out a lot of the SQL dependencies between databases (and there are lots), and just program in a consistent fashion. You can not worry about quoting as your module handles it, you can add methods for common tasks (like returning a single value), you can automate fetching of an auto increment id, etc.
As for count(*), yes of course, if you are only expecting one result, there's no reason you would do a count(*). However, if you are paging through a list of users twenty five at a time, it will be significantly slower if you fetch all your rows (as you can't depend on ->rows) to do your count, rather then run a second query to do the count.
Cheers,
Alex
--
Gossamer Threads Inc.
As for count(*), yes of course, if you are only expecting one result, there's no reason you would do a count(*). However, if you are paging through a list of users twenty five at a time, it will be significantly slower if you fetch all your rows (as you can't depend on ->rows) to do your count, rather then run a second query to do the count.
Cheers,
Alex
--
Gossamer Threads Inc.