0

I have 25,000 entries in my table and when I retrieve data from my table.. it almost take 30 secs or 1 min. I tried using DESC LIMIT and added index to my column but still doesn't work. How can I get my data in a faster way?

this is my table_structure

id (int 255)
date_sourced (date)
sha1 (varchar 255)
vsdtt (varchar 255)
trendx (varchar 255)
notes (varchar 255)

My desc limit is like this, user can Input how many values can be displayed

enter image description here

Here is my query:

$rpp = 10;
$page = 1;
$conn = mysqli_connect("localhost", "root", "", "jeremy_db");
$query_count = " SELECT * FROM jeremy_table_trend ORDER by date_sourced desc ";
$result_count = mysqli_query($conn, $query_count);
$total = mysqli_num_rows($result_count);
$temp = ($page-1)*$rpp;
$query = " SELECT * FROM jeremy_table_trend ORDER by date_sourced desc LIMIT $rpp ";
$page_result = mysqli_query($conn, $query);

Then my table to display the values from db:

<?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;"> </td>
          <div class="ui dropdown item" id="options_<?php echo $currsamp_id ?>">
            <i class="settings icon"></i><i class="dropdown icon"></i>
          </div>
     <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 } ?>
  • 2
    One possibility - Do read: [Why is SELECT * considered harmful?](https://stackoverflow.com/q/3639861/2469308) – Madhur Bhaiya Nov 07 '18 at 05:14
  • My first suggestion will be, as you don't have any `where` clause in your query, try to ORDER BY `id` instead of `date_sourced` in your first query (you will use `$total` for pagination, right?). and is your date_sourced indexed? Is all of the `date_sourced` same? – Ataur Rahman Nov 07 '18 at 05:17
  • Try `SELECT COUNT(*) FROM jeremy_table_trend` as your first query to get total rows. – Ataur Rahman Nov 07 '18 at 05:19
  • yes im using pagination, and my date_sourced is indexed, I need my table to be ordered by date_sourced –  Nov 07 '18 at 05:19
  • But you don't need to order by a date column to get total count. Use `COUNT(*)` instead. It will improve your first query. – Ataur Rahman Nov 07 '18 at 05:21
  • yes i tried that, it lessen the loading time but still loads 30sec - 40 secs –  Nov 07 '18 at 05:25
  • 2
    Can you check both of the query loading time separately? And please provide how you indexed the table columns. – Ataur Rahman Nov 07 '18 at 05:28
  • `id (int 255)` why is this column integer should have `255`? how big is your id actually? I think `11` will be good enough. – Ataur Rahman Nov 07 '18 at 05:29
  • 1
    `select count(*) FROM jeremy_table_trend` on an indexed table (and for 25k rows, maybe even without) should take about 0.05s (in contrast to transfering 25MB to do a single `mysqli_num_rows` with that), `select ... limit 10` with a proper index should not take more than 1s either. You are either missing information (maybe the table is a view, you didn't index correctly, $rpp >> 10, ...) or the bottleneck isn't the queries. As Ataur suggested, please run and time your queries separately on e.g. phpmyadmin, and also add both explain plans (the output of `explain select ...`) to your question. – Solarflare Nov 07 '18 at 07:17

0 Answers0