|
|
Posted by Sandman on 11/24/06 15:38
In article <79udm2dclimq6ambad1kdo5mu8j8qc111k@4ax.com>,
Michael Fesser <netizen@gmx.de> wrote:
> .oO(Sandman)
>
> >> Clear.
> >> Just to be sure, you should of course NOT running seperate queries for
> >> each
> >> result you need.
> >> eg BAD:
> >> SELECT username FROM tbluser WHERE (userid=23);
> >> SELECT username FROM tbluser WHERE (userid=36);
> >> SELECT username FROM tbluser WHERE (userid=123);
> >
> >No, that's stupid.
> >
> >> eg GOOD:
> >> SELECT userid, username FROM tbluser WHERE
> >> (userid IN (23,36,123));
> >
> >But BAD:
> >SELECT userid, username FROM tbluser WHERE
> >(userid IN (<500 id numbers>));
>
> I did a little test with a single table containing 100000(!) entries,
> all with an ID and a random name. The task was to select 1000 randomly
> chosen records. I did two tests:
>
> 1) a foreach loop, running 1000 queries
> time: 0.90s
>
> 2) a single query with an IN operator and 1000 given IDs
> time: 0.04s
>
> Tested on a P3 550MHz/192MB RAM, MySQL 4.1.11
Yes, I apologize, I tested it the wrong way. My mistake. Sorry.
Doing it the same way as your way:
1. Fetching 1000 members, randomly chosen, one at a time:
0.254 seconds
2. Fetching all 1000 members in one really big IN():
0.007 seconds
So, where does this lead me...
A. Use placeholders, substitute them in post-processing. This may or
may not be a good idea due to lack of control, or a more demanind
replace process (i.e. should I replace the placeholder with the member
online status, shold the nick be in it, should the members label be in
it?)
or
B. Fetch each one as a single SQL query when requested. As you see,
making 1000 SQL queries took 0.254 seconds, which is less than the 0.8
seconds I'm spending om moving data today. And that's for 1000
members. I think I'm leaning towards this since it requires the least
amount of changes to my code. Hmmm
Anyway, thanks for helping me find my error in my benchmarks! :-D
--
Sandman[.net]
Navigation:
[Reply to this message]
|