Ive been using the same way of creating queries for the mySQL
database the last years now, but as my databases keep growing
Its time to start thinking of performance.
My question however, is more if the way im doing this is the best way,
or if I should reprogram all my queries in a more optimal way.
The thing Im wondering about is the way I create my
<prev page> 1 2 3 4 <next page> scripts. I will show the way I do it,
and if you know a way that is more optimal or better, or you see any
really "dumb" stuff here talking prosessing power please let me know.
(And im using some abstraction layers for the accual queries, but they
should not need to be optimized any further)
1. I build the query, and assign it to $sql
> $sql = "select * from mydatabase";
2. I perform the query against the mySQL database
> $result = query($sql);
3. I check to see if I have any results at all, to verify if I will show
content at all
> $num=mysql_num_rows($result);
> if(!$num){ // tough luck buster page! }
> else { // continue with the prev/next page routine }
4. Then I feed my prev/next routine with things as page, limit and such.
> $pn_putput=showNavigation($sql,$limit,$page);
The showNavigation then, based on the page, limit and mysql_num_rows()
then
calculates out the correct mySQL limit statement to add to the existing
SQL statement,
for example we have :
page=2, limit=10, mysql_num_rows=50, after computed the $sql is like
this :
> $sql = "select * from mydatabase limit 20,10";
Then I query the mySQL database again to get the final result set, which
I return together
with the buildt prev/next htmlkode
5. I "for($i=0..." loop thought the result set...
> for($i=0;$i<mysql_num_rows($result);$i++){
> $unit=mysql_fetch_object($result);
> // blablabla }
6. If there were any output from the navigation routine, I enter this at the
bottom.
That would be my process of doing this. I do have some questions, and
remember that im
referring to working with large mySQL tables >10.000 records.
a) In point 2 I query out the whole database, and with a wildcard. I would
guess if I
compact this abit, say like "select id from...", pulling only 1 field
from the result set.
This would spare alot of processing power for both mySQL and PHP?
b) Maby I could just do a mySQL query that would result the total amount of
entries,
just the accuall number - since all I need is the max number to compute
the final sql.
c) Point 5, I preferr using objects compared with arrays, atleast for
database work. I
find it much more portable, since altering the datbabase structure means
I dont have to
enter all my code aswell "$unit->name;" will always work even if I
insert a new field
infront of it, "$unit[3];" however will fail dramatically since the
array has become larger.
Is it just the same what U use, Object or Array, or are there particular
reasons to
use them for surtain work other than what you preferr yourself?
Why repair things that already work is the thing I say all day long, but I
do think however its
time to start optimizing my work before my machinery are all buildt upon old
rusty cogs, :)
--
Kim Steinhaug
---------------------------------------------------------------
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
---------------------------------------------------------------
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
