slow output from mysql database

Question

I only have a single table in my database with only 5 columns , it has around 13000 rows of data into it , when i fetch it on my php page it outputs data very slowly . Can anyone advice a solution for it .

code is as below :

<?php 
    $sno = 1 ; 
    $form = 0 ; 
    $data = selectloop('data') ; 
    foreach($data as $datalist){ 
        $form = $form + 1 ; ?>
            <tr class="odd gradeX">
                <td ><?php echo $sno ; ?></td>
                <td ><?php echo $datalist['domain_name'] ; ?></td>
                <td ><?php echo $datalist['registrant_name'] ; ?></td>
                <td ><?php echo $datalist['registrant_city'] ; ?></td>
                <td ><?php echo $datalist['registrant_state'] ; ?></td>
                <td ><?php echo $datalist['registrant_email'] ; ?></td>
                <td ><?php echo $datalist['registrant_phone'] ; ?></td>
                <td>
                <?php if(isset($_POST['submit'.$form])) { 
                 mysql_query("update data set remarks='".$_POST['remarks']."' where id='".$_POST['id']."' ") ;
                 header("location:datalist.php") ; } ?>

                <form method="post">
                <input type="hidden" class="form-control" name="id" value="<?php echo $datalist['id'] ; ?>" >
                <input type="text" class="form-control" name="remarks">
                <button  class="btn btn-primary btn-xs" name="submit<?php echo $form ;?>" > Submit </button>
                </form>
                </td>
            </tr>
<?php $sno = $sno + 1 ; } ?>   
        </tbody>

is the table indexed? plus, without seeing what your code looks like... — Funk Forty Niner, Apr 04 '16 at 14:03
Can you provide the code with query and routine to display records? Also note that if you display entire table in one page, the problem can be in HTML, not in database. — fusion3k, Apr 04 '16 at 14:05
Show the schema for this table and also the code you are using to retrieve data from it. Otherwise all this question will attract are downvotes and close votes — RiggsFolly, Apr 04 '16 at 14:11
Provide some example code on what you're doing in PHP and what sort of MySQL query you have. It would help. — apokryfos, Apr 04 '16 at 14:13
Please edit question adding **table structure** and php code with **mysql query** and **output** routine. Small pieces of code in comments don't help. — fusion3k, Apr 04 '16 at 14:16
Now of course we need to know what is the code inside the `selectloop()` function — RiggsFolly, Apr 04 '16 at 14:23
Please dont use [the `mysql_` database extension](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), it is deprecated (gone for ever in PHP7) Especially if you are just learning PHP, spend your energies learning the `PDO` or `mysqli_` database extensions, [and here is some help to decide which to use](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — RiggsFolly, Apr 04 '16 at 14:24
Your code is Wide Open to SQL Injection attacks. [Please read this](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — RiggsFolly, Apr 04 '16 at 14:25
Advice: The PHP mysql_ interface functions are deprecated, and are removed in PHP 7. New development should use mysqli_ or PDO. The posted code appears to have SQL Injection vulnerabilities. — spencer7593, Apr 04 '16 at 14:25
Your code is all in the wrong order. Why process a SELECT and then in the loop that processes the results, do an update and THEN redirect to another script with a `header()` Take a deep breath and think about what you are writing, then re-write the script — RiggsFolly, Apr 04 '16 at 14:27
I would bet that this script is called `datalist.php` so what you are doing is running it twice for each time the user clicks the submit button — RiggsFolly, Apr 04 '16 at 14:36

score 1 · Answer 1 · edited May 23 '17 at 11:59

You could add pagination, meaning you use LIMIT and a Auto-increment column as a primary key which in this case is id.

PHP source code:

$start = $_POST['start'];
$db = mysqli_connect("localhost","user","pass","dbName"); // MYSQLi API
$sql = "SELECT * FROM data WHERE id >= '$start' LIMIT 10"; // Default limit
foreach ($db->query($sql) as $row):
    echo '<tr>';
        [...]
             echo $row['column'];
        [...]
    echo '</tr>';
endif;

You could then add a Client-side handler which sends a POST request to the PHP file when a 'next page' button is pressed. This integrates real time pagination, neater pages and less page load time.

jQuery POST example:

$(document).ready(function () {
    var count = 1; // starting id
    $('#nextpage').click(function () {
        count = count +10;
        $.post('phpfile.php', { start: count })
            .done(function (data)
            {
                 $('#output').innerHTML = data;
            });
    });
});

HTML example:

<div class='display-content'>
    <div id='output'></div>
    <div id='nextpage'>--Next-Page-></div>
</div>

Hope this helped you but remember, you need to strip $start in PHP before passing it into the query since you're not using PDO.

Update: Ensure to read a lot of these comments, MYSQL is depreciated and will not be available in newer PHP version's - especially PHP 7+ - so I suggest for security, learn PDO or at-least MYSQLi.

Further Helpful Resources:

MYSQLi Manual
PDO Manual
LIMIT Query
Preventing SQLi Injections

score 0 · Answer 2 · answered Apr 04 '16 at 14:38

0

My strong intuition says you're trying to output ~13,000 rows to your browser. Naturally, this will slow things down. It's not the fault of MySQL, although it's most likely due to no LIMIT clause in your query.

Add a LIMIT to your query:

$query = "SELECT * FROM `table` LIMIT x"; // where x is the number of records to be outputted

answered Apr 04 '16 at 14:38

mferly

1,646
1
13
19

thank you every one , but i want all the data to be accessed , so i cannot use limit – Dream Web Solutions Apr 04 '16 at 15:40
Are you willing to use pagination to offset the initial output load? If not, and you're adamant on outputting ~13,000 records to the page in one shot, then you're going to have to live with a slow page load. – mferly Apr 04 '16 at 15:50

slow output from mysql database

2 Answers2