[PHP] Most efficient way of fetching 1,000 records from MySQL ?
Hello,
In my script I have generated an array of 1,000 userIDs for example. Now for
I need to fetch each user's record and I am wondering what is the best way
to go about it:
1) 1000 sql queries
// code is sort of pseudo for clarity
foreach($users as $userID) {
$user = sql(select * from users where userID = '$userID');
// bla bla
}
2) 1 query
$users = sql(select * from users where userID='1' or userID='2' or
userID='5' or userID='10');
I imagine the 2nd one would be a bit of a nightmare for MySQL to parse, if
it gets too long?
Or am I missing a more efficient 3rd / 4th option?
Many thanks in advance for your help.
Steve McGill
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Most efficient way of fetching 1,000 records from MySQL ?
I think you'll find that your first approach is not only more correct,
it's the only manageable one.
Ian
On Mon, 2004-10-25 at 10:15 +0200, Steve McGill wrote:
Hello,
In my script I have generated an array of 1,000 userIDs for example. Now for
I need to fetch each user's record and I am wondering what is the best way
to go about it:
1) 1000 sql queries
// code is sort of pseudo for clarity
foreach($users as $userID) {
$user = sql(select * from users where userID = '$userID');
// bla bla
}
2) 1 query
$users = sql(select * from users where userID='1' or userID='2' or
userID='5' or userID='10');
I imagine the 2nd one would be a bit of a nightmare for MySQL to parse, if
it gets too long?
Or am I missing a more efficient 3rd / 4th option?
Many thanks in advance for your help.
Steve McGill
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Most efficient way of fetching 1,000 records from MySQL ?
Ian Firla wrote:
I think you'll find that your first approach is not only more correct,
it's the only manageable one.
No, it will be very slow. The biggest overhead is in transfering data to
and from sql server. It's always better to get the results in one sql query.
Use this aproach:
$users =
sql('select * from users where userID IN ('. implode(', ',$users) .')');
Ian
On Mon, 2004-10-25 at 10:15 +0200, Steve McGill wrote:
Hello,
In my script I have generated an array of 1,000 userIDs for example. Now for
I need to fetch each user's record and I am wondering what is the best way
to go about it:
1) 1000 sql queries
// code is sort of pseudo for clarity
foreach($users as $userID) {
$user = sql(select * from users where userID = '$userID');
// bla bla
}
2) 1 query
$users = sql(select * from users where userID='1' or userID='2' or
userID='5' or userID='10');
I imagine the 2nd one would be a bit of a nightmare for MySQL to parse, if
it gets too long?
Or am I missing a more efficient 3rd / 4th option?
Many thanks in advance for your help.
Steve McGill
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Most efficient way of fetching 1,000 records from MySQL ?
I would certainly agree with Marek.
I recently changed a query from using 20 or so 'OR' conditions to use the
'IN' statement and it drastically improved the performance of the query.
Graham
-Original Message-
From: Marek Kilimajer [mailto:[EMAIL PROTECTED]
Sent: 25 October 2004 11:37
To: Ian Firla
Cc: Steve McGill; [EMAIL PROTECTED]
Subject: Re: [PHP] Most efficient way of fetching 1,000 records from
MySQL ?
Ian Firla wrote:
I think you'll find that your first approach is not only more correct,
it's the only manageable one.
No, it will be very slow. The biggest overhead is in transfering data to
and from sql server. It's always better to get the results in one
sql query.
Use this aproach:
$users =
sql('select * from users where userID IN ('. implode(', ',$users) .')');
[snip]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Most efficient way of fetching 1,000 records from MySQL ?
I think you'll find that your first approach is not only more correct,
it's the only manageable one.
No, it will be very slow. The biggest overhead is in transfering data to
and from sql server. It's always better to get the results in one sql
query.
Use this aproach:
$users =
sql('select * from users where userID IN ('. implode(', ',$users) .')');
That is precisely what I wanted, many thanks for your help guys.
Steve
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
