CODEWITHSUNDEEP
phpmysql
user8892462
user8892462

Reputation:

How to select top 100 (table) then limit the display

I have 22,000 entries in my table, so I used limit when displaying my table, Then a user can define how many rows to be displayed:

enter image description here

My problem is, even when I type smaller digit like 10 it displays 10 entries only but it loads my entire table row per row, taking almost 30 seconds to load.

$rpp = 10;
$page = 1;
$temp = ($page-1)*$rpp;
$query = " SELECT * FROM table_me ORDER by date_sourced desc LIMIT $temp, $rpp ";
$page_result = mysqli_query($conn, $query);

I tried this, and followed the instructions but this doesn't work giving me error: SELECT TOP 300 * FROM table_me LIMIT 0, 25

$query = " SELECT  TOP 300 * FROM table_me ORDER by date_sourced desc LIMIT $temp, $rpp ";

How can I load 300 only from my table but still gives me access for the remaining bottom values in table?

Then this is my code to echo values from table:

<?php while($row = mysqli_fetch_assoc($page_result)) { $currsamp_id = $row['id'];?>
                                <tr>
                                    <td><input class="ui checkbox" type="checkbox" name="check_box" value="<?php echo $row['id'] ?>"></td>
                                    <td onclick="$('#options_<?php echo $currsamp_id ?>').dropdown('show')" style="overflow: visible !important;"> 
                                        <div class="ui dropdown item" id="options_<?php echo $currsamp_id ?>">
                                            <i class="settings icon"></i><i class="dropdown icon"></i>
                                            <div class="menu">
                                                <?php
                                                    if(isset($_SESSION['username'])) {
                                                        $temp = $row['id'];
                                                        $ds = $row['date_sourced']; $sh = $row['sha1']; $vsdt = $row['vsdt']; $tx = $row['trendx'];  $n = str_replace('\"', '\\\"', $row['notes']);
                                                        echo "<a class='item' style='color: black; width: 100%;' onclick='open_update(\"" . $temp . "\", \"" . $ds . "\", \"" . $sh . "\", \"" . $vsdt . "\",  \"" . $tx . "\",  \"" . $n . "\")'>Update</a>";
                                                    } else {
                                                        echo '<a class="item modal_trigger" style="color: black; width: 100%;" data-target="#modal_login">Update</a>';
                                                    }
                                                ?>
                                                <?php
                                                    if(isset($_SESSION['username'])) {
                                                        $temp = $row['id']; $sha1 = $row['sha1'];
                                                        echo '<a class="item" style="color: black;" onclick="open_delete(\''.$temp.'\', \''.$sha1.'\', \'open\')">Delete</a>';
                                                    } else {
                                                        echo '<a class="item modal_trigger" style="color: black;" data-target="#modal_login">Delete</a>';
                                                    }
                                                ?>
                                            </div>
                                        </div>
                                    </td>
                                    <td nowrap title="Date Sourced"><?php echo $row['date_sourced'] ?></td>
                                    <td nowrap title="SHA-1"><?php echo $row['sha1'] ?></td>
                                    <td nowrap title="VSDT"><?php echo $row['vsdt'] ?></td>
                                    <td nowrap title="TrendX"><?php echo $row['trendx'] ?></td>
                                    <td nowrap title="Notes"><?php echo $row['notes'] ?></td>
                                </tr>
                            <?php } ?>

Upvotes: 1

Views: 674

Answers (3)

Mike Robinson
Mike Robinson

Reputation: 8945

I'll make this another "answer" ...

Use the EXPLAIN verb to see exactly how the RDBMS will run the query, e.g.:

EXPLAIN SELECT * FROM table_me ORDER BY date_sourced DESC LIMIT 100

This will give you the so-called "execution plan," which is what actually drives the query engine. (The RBMS "compiles" the SQL, considers various statistics, and comes up with a plan. It does this for every query.)

EXPLAIN causes the RDBMS to return the plan to you instead of subsequently executing it. This tells you exactly how the RDBMS intended to get the answer that you seek.

If a query runs "unexpectedly slowly," this is a good way to find out why. (The format of the plan-data varies considerably from one RDBMS to another.)

Upvotes: 0

Mike Robinson
Mike Robinson

Reputation: 8945

Also – if you need to find the top ten date_sourced quickly, you should create an index on that column.

Otherwise, yes, the DBMS has no choice but to scan the entire table, finding the top entries, before delivering the first n of those rows to you!

In the presence of an index, such a query will be virtually instantaneous.

Upvotes: 1

Erik Kalkoken
Erik Kalkoken

Reputation: 32737

As mentioned in the comments you are mixing the syntax from two different DBMS.

For mysql use LIMIT, not TOP. Also, LIMIT has only one parameter, not two.

Here is your corrected query:

$query = " SELECT * FROM table_me ORDER BY date_sourced DESC LIMIT 100 ";

This will return the top 100 rows only.

Upvotes: 1

Related Questions