How to optimize jquery chosen plugin for loading more than 50k results

Question

I am using class 'chosen-select' for loading all product names.When the user clicks on product_select , it takes lot of time to load.What can be done to improve performance.

<select class='chosen-select' data-placeholder="Choose a product..." id="product_select">
  <option></option>
         <?php
          include 'connection.php';

           $query = "SELECT * FROM " . $table_name;

           $result = mysqli_query($con, $query);

           $results = array();
           while ($line = mysqli_fetch_assoc($result))
           {
                   $results[] = $line;
           }
           $i = 0;
           foreach ($results as $r)
           {
               $i++;
               echo "<option value=" . $r['id'] . ">" . $r['Product_name'] . "</option>";
           }
          ?>
</select>

<script>
     $('#product_select').chosen({max_selected_options: 1});

</script>

Design the UI properly so you don't try to load 50,000 options at once, that would be a good start to improving your performance. — Thomas W, Aug 26 '13 at 09:31

score 2 · Accepted Answer · edited May 23 '17 at 10:24

what you should do

It's probably not a good idea to be serving up so many options in one go, just for the sanity of your users. If it were me I'd probably switch to an auto-complete model or a way for the user to narrow the option request down to a subset before requesting, however, here are some tips on what you can use to optimise.

what you can do

The only real point you can optimise — without rewriting the jQuery plugin yourself — is to improve things at the server-side. This could include (as stated by Samuel Liew) caching the servers response, but also improving your connection to your database i.e. make sure you are dealing with a local database rather than remote. Some simple steps that can help towards faster queries:

By specifying your target fields rather than using * it may help with a large number of rows.

15 tips on optimising mysql queries

Even changing the type of database/table can help — for example, if your hardware has low disk write speed/efficiency then try running your table fully from memory. However these kinds of options require that you must have a lot of control over your database server and your database config, if you are using a shared host it is unlikely you can do much of anything... you may have a choice between using MySQL or SQLite though, which could be enough to switch between a disk-based database to a more memory-based one. If you are running a dedicated server however there is a whole host of things you can do to optimise MySQL, but it takes a lot of reading (it's a complicated subject), this SO thread hints at some of the things you can do for MySQL:

Optimal MySQL-configuration (my.cnf)

One thing that may help, but would depend on how much memory your database instance has, would be to build your HTML in your query. I wouldn't advise this for complicated queries, but what you are generating is quite simple. The following assumes MySQL usage:

SELECT 
  GROUP_CONCAT(
    '<option value="',id,'">', name, '</option>' SEPARATOR ''
  ) AS html
  FROM $table_name 
  GROUP BY NULL

Once you've got the database bringing back the HTML you want to your PHP, you could either cache this result in another db table, or as a file in the local filesystem; then on the next request for this option list you should serve from the cached location instead — and continue to do so until a prefixed expiry time (or the cache is deleted).

The following link explains the rough process, for your needs you would just write the result brought back from the database; rather than ob_get_contents() as in their example — this is just one way to server-side cache however.

Easy server-side caching in php

client-side caching

Depending on how often you are displaying this input or how often your users might encounter it, you could also optimise on the client-side (for browsers that support it) using the window.localStorage object. This would allow you to put a check of the localStorage just before making your AJAX select request, if you already have the options html stored there you can use that instead and cut out the request entirely. There seems to be an unofficial storage limit of 5M for localStorage but this should cover your 50000 items. This obviously wouldn't speed things up for a user's first visit, but subsequent visits should be improved quite a bit. You should also bear in mind a way for the cache to be cleared if your database table is updated.

How to optimize jquery chosen plugin for loading more than 50k results

1 Answers1

what you should do

what you can do

client-side caching