#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    0
    Rep Power
    0
    I want to develop the result next/prev/1-10/11-20 etc thing.Say we have N records. We limit it to display M records at a time. So I have a question which is better, performance wise:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    METHOD A
    do 2 query.
    1.get the total result no. using "select count(*) from tbl where etc.."
    2.perform the actual query but limiting it to M result, like: "select * from tbl where ..limit startoffset,M";
    and go from there...
    OR
    METHOD B.
    do 1 query.
    1. perform actual query without limit: "select * from tbl where ....";
    2. use mysql_num_rows($result);
    3. display it but limit to M.
    [/code]
    Say N=1,000,000
    Will there be performance degradation for method A? how 'bout method B? Will $result=mysql_query($query) makes the system ran out of memory? (since it stores the resultset in a buffer). Or if you have a better solution, please share it here.
    Thank you.


  2. #2
  3. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    <<
    1.get the total result no. using "select count(*) from tbl where etc.."
    >>


    Get the total number of record first...then in a second query you can limit the result..

    your first query can be;

    SELECT count(*) as count from tablename where someconditions.

    see the following article:
    http://www.phpbuilder.com/columns/rod20000221.php3

    ------------------
    SR -
    webshiju.com
    www.jobxyz.com-IT Career Portal
    ezipindia.com--WebStudio


    "The fear of the LORD is the beginning of knowledge..."

    [This message has been edited by Shiju Rajan (edited October 18, 2000).]
  4. #3
  5. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,640
    Rep Power
    4476
    I don't know how official this is, but here's what I did for you.

    I created a database with around 104K rows of a random number, using

    $result = mysql_query("insert into test99 values (rand())");

    I then ran the two methods you described, getting 1000 rows out of the database.

    Here is the code I used:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    <?php
    function getmicrotime()
    {
    $mtime = microtime();
    $mtime = explode(" ",$mtime);
    $mtime = $mtime[1] + $mtime[0];
    return ($mtime);
    }

    $link_id = mysql_connect("localhost","user","pass");
    $db = mysql_select_db("test");

    $start_time = getmicrotime();
    $result = mysql_query("SELECT COUNT(*) AS count FROM test99");
    list($count) = mysql_fetch_row($result);
    echo "<p ";
    $result = mysql_query("SELECT * FROM test99 LIMIT 1000");
    while (list($id) = mysql_fetch_row($result))
    {
    echo $id;
    }
    echo "><BR>";
    $end_time = getmicrotime();
    $total = $end_time - $start_time;

    echo "Method A: count(*)<BR>n";
    echo "Start Time: $start_time<BR>n";
    echo "There are $count records.<BR>n";
    echo "End Time: $end_time<BR>n";
    echo "Total Time: $total<BR>n";

    echo "<HR>n";

    $start_time2 = getmicrotime();
    echo "<p ";
    $result = mysql_query("SELECT * FROM test99");
    $count2 = mysql_num_rows($result);
    for($x=0;$x<1000;$x++)
    {
    list($id) = mysql_fetch_row($result);
    echo $id;
    }
    echo "><BR>";
    $end_time2 = getmicrotime();
    $total2 = $end_time2 - $start_time2;

    echo "Method B: select *<BR>n";
    echo "Start Time: $start_time2<BR>n";
    echo "There are $count2 records.<BR>n";
    echo "End Time: $end_time2<BR>n";
    echo "Total Time: $total2<BR>n";

    ?>
    [/code]
    I put the output between <p and > just so it wouldn't show on the screen, it makes for cleaner output.

    Here is a sample result. I ran it a few times and it always came out something similar.
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    Method A: count(*)
    Start Time: 971877650.99624
    There are 104323 records.
    End Time: 971877651.06424
    Total Time: 0.06800103187561

    Method B: select *
    Start Time: 971877651.06476
    There are 104323 records.
    End Time: 971877652.33897
    Total Time: 1.2742110490799
    [/code]

    You can see the result at
    http://www.sepodaticreations.com/test/test_db.php3


    So, in finale, it looks like Method A is faster....

    ---John Holmes...
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    0
    Rep Power
    0
    Thanks for the input. Anyway I ran a similar test and the result are quite consistent.
    I also discovered that if you index the key that will be used in the "where" clause, Method A is way much faster than Method B (3 times faster). But without index, Method B is slightly faster (1 time faster).

    Here's the code for the test:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    <?

    function getm($mtime){
    $mtime = explode(" ",$mtime);
    $mtime = $mtime[1] + $mtime[0];
    return ($mtime);
    }

    include('dbinc.php3');
    $cnx=opendb();

    /* Populate DB with rand data.
    echo "start:";
    for($i=0;$i<1200000;$i++) {

    $rano=mt_rand(1,5000);

    mysql_db_query("rnd","insert into test(wiw) values($rano)");


    }
    echo "<br>finish:";
    */

    echo "<br>Method A:<br>";
    // Method A
    $startA=microtime();
    $result=mysql_db_query("rnd","select count(*) from test where wiw=320");
    $temp=mysql_fetch_array($result);
    $totnum=$temp[0];
    $result=mysql_db_query("rnd","select * from test where wiw=320 limit 20,10");
    while($temp=mysql_fetch_array($result)) {

    echo $temp[0]."<br>";
    }
    echo "Total Rec: $totnum<br><br>";
    $endA=microtime();
    echo "Method B:<br>";
    // Method B
    $startB=microtime();
    $result=mysql_db_query("rnd","select * from test where wiw=320");
    $totnum=mysql_num_rows($result);
    $count=0;
    mysql_data_seek($result,20);
    while(($temp=mysql_fetch_array($result)) && $count<10) {

    echo $temp[0]."<br>";
    $count++;
    }
    $endB=microtime();
    echo "Total Rec: $totnum<br><br>";

    $timeA=getm($endA)-getm($startA);
    $timeB=getm($endB)-getm($startB);

    echo "Method A: ".$timeA;
    echo "<br>Method B: ".$timeB;
    ?>
    [/code]
    Here's the SQL statement for the table:
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    CREATE TABLE test (
    entah int(11) NOT NULL auto_increment,
    wiw int(11) NOT NULL,
    PRIMARY KEY(entah),
    INDEX(wiw) );
    [/code]
    The results (with index):
    Method A: 0.0035500526428223
    Method B: 0.010253071784973
    Total Rec Searched: 248

    The results (without index):
    Method A: 0.94411909580231
    Method B: 0.85051798820496
    Total Rec Searched: 248

    Total Rec in Table: 1,200,000



    [This message has been edited by mizrael (edited October 18, 2000).]

Similar Threads

  1. Frames hijacked after remote search
    By Telejoke in forum Antivirus Protection
    Replies: 2
    Last Post: September 26th, 2007, 09:32 AM
  2. Highlight search result from mysql database search
    By Gurt in forum PHP Development
    Replies: 3
    Last Post: February 17th, 2004, 03:39 AM
  3. Replies: 5
    Last Post: January 4th, 2004, 10:36 AM
  4. echo search result into tables
    By liamtrop in forum PHP Development
    Replies: 1
    Last Post: January 4th, 2004, 01:04 AM
  5. Is there any alternative to fulltext search?
    By irishairpics in forum MySQL Help
    Replies: 2
    Last Post: December 10th, 2003, 02:40 PM

IMN logo majestic logo threadwatch logo seochat tools logo