How to retrieve data fast from mysql database which has 10k records

Question

I am have a page in which i have to show all the records from my data base but its taking alot of time when i refresh it. The html part loads fast but where the cursor reaches my mysql query it takes a lot of time to load.. here is my query

$srchresult = mysql_query("SELECT `date`,`chalno`,`custcode`,`vessel`,`rankcode`,`crew`,`ppno`,`cdcno` FROM `$maindb` WHERE `series`='$zseries' Order By `date`, `chalno` desc")or die(mysql_error());
                                while ($row = mysql_fetch_array($srchresult)) {
                                    $mdate = strtotime("d-m-Y", $row[date]);
                                    echo "<tr>";
                                    echo "<td align='left'>$mdate</td>";
                                    echo "<td align='left'>$row[chalno]</td>";
                                    echo "<td align='left'>$row[custcode]</td>";
                                    echo "<td align='left'>$row[vessel]</td>";
                                    echo "<td align='left'>$row[rankcode]</td>";
                                    echo "<td align='left'>$row[crew]</td>";
                                    echo "<td align='left'>$row[ppno]</td>";
                                    echo "<td align='left'>$row[cdcno]</td>";
                                    echo "<td align='left'><a href=\"$file?mode=edit&$keyfld=$row[$keyfld]&series=$zseries\"><i class='fa fa-pencil' style='font-size:1.3em;color:#00C0EF;cursor:pointer;'></i></a></td>";
                                    echo "</tr>";
                                }
                            }

Make sure that you have sensible indexes on your database tables, e.g. on the `series` column — Mark Baker, Jan 12 '16 at 10:41
@MarkBaker i do have my indexing on both series and chalno as well — mitesh_gohil, Jan 12 '16 at 10:52
http://stackoverflow.com/questions/15547040/mysql-fetch-time-optimization — Manikiran, Jan 12 '16 at 11:00
Then do an EXPLAIN on the query to see how MySQL is executing it, and whether it's using those indexes or not — Mark Baker, Jan 12 '16 at 11:02

score 1 · Answer 1 · answered Jan 12 '16 at 10:53

1

I strongly recommend using pagination in order to be able to render/browse trough 10k records on a page.

If you must do it without a visible pagination block in your layout then use an infinite scroll solution. Here is a rather simple one: http://www.infotuts.com/ajax-infinite-scroll-using-jquery-php-mysql/

Your mysql query is fine, even though you should use at least PHP's mysqli extension (http://php.net/manual/en/intro.mysqli.php) to retrieve the data.

answered Jan 12 '16 at 10:53

sw0rdfishhhhh

55
5

actually i am showing my data in a modal using dataTables , so i dont think infinite scroll is the perfect solution. – mitesh_gohil Jan 12 '16 at 11:07
And i if i write msqli_query its giving me an error mysqli_query() expects exactly 2 parameters, 1 – mitesh_gohil Jan 12 '16 at 11:08

How to retrieve data fast from mysql database which has 10k records

1 Answers1