Jump to content

Slow Page Load Large SQL Table - Help


coolshrimp
 Share

Recommended Posts

I have a php page that loads a table with value from a MYSQL Table but it has almost 5000 rows. witch makes the page load very slow somtimes times freeze browser.

$SQL = "SELECT ID, First_Name, Last_Name, DOB, Phone FROM Customer_Info";$result = mysql_query($SQL);$fields_num = mysql_num_fields($result);$row_num = mysql_num_rows($result);echo <<<EOT<table class="datatable"><thead><tr>EOT;// printing table headersfor($i=0; $i<$fields_num; $i++){$field = mysql_fetch_field($result);echo <<<EOT<th>{$field->name}</th>EOT;}echo <<<EOT<th>Action</th></tr></thead><tbody>EOT;// printing table rowswhile($row = mysql_fetch_assoc($result)){    echo "<tr>";    // $row is array... foreach( .. ) puts every element    // of $row to $cell variable    foreach($row as $cell)        echo "<td>$cell</td>";        echo "<td style='text-align:center'><a href='ViewCustomer.php?id=" . $row['ID'] . "' style='color:#FFF'><button class='blue'>View</button></a> | <button onclick='var rowID=" . $row['ID'] . "; var string="includes/mysql/DeleteCustomer.php?id=" . $row['ID'] . ""; var result="alertBanner"; if (confirm("Are you sure you want to delete this customer?")) {ajaxLoad(string, result); $("#dt1").find("td:contains(" . $row['ID'] . ")").parent().remove(); }' style='color:#FFF' >Delete</button></td>";    echo "</tr>n";}echo <<<EOT</tr></tbody></table>EOT;mysql_free_result($result);mysql_close($connnect);
Link to comment
Share on other sites

SQL is the language you use to limit the amount of results you get and to order them. You use the LIMIT and ORDER BY clauses.

 

Like I said, search for PHP pagination tutorials.

Link to comment
Share on other sites

The reason your page is loading so slowly is that you're sending the data of 5000 records to the client. That takes some time to load. jQuery can only work with data that has already be sent from the server, so it cannot be used as a solution to the slow loading times.

 

You could reduce the content length a little bit by removing the inline style attributes from the elements you're generating and using a stylesheet to apply it instead (something you should be doing anyways), it would reduce the data by over 200 kilobytes.You can move all that Javascript you're generating into a generalized function and assign event handlers from a script tag somewhere else on the page instead of using event attributes. These changes probably would not decrease the download time enough.

 

What would really solve the problem is to reduce the amount of data you're sending. 5000 records is a whole lot, sending 50 at a time would be more reasonable.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...