Home Ask Login Register

Developers Planet

Your answer is one click away!

Adam February 2016

sorting in mysql VS already sorted array

I have a MySQL table with about 10000 entries. I want to represent these entries sorted by name. The page should only show 20 entries at a time.

My question is, is it more efficient to

  • Let the database sort it, this means load corresponding 20 entries by using a sorting, limit, select query.

or should one rather

  • Sort the list of entries once, save them in an array in a file, load file and only look at the 20 indices of interest.

Both seems terrible to me. I do not want to sort a database with 10000 entries each time a user loads the page just to show 20 entries, nor do I want to load an array with more then 10000 entries to have access to corresponding 20 entries.

Remark: I am not asking is php sort better than mysql "order by"? or database sort vs. programmatic java sort - I want to know if it is better to presort a database, save it in an array and then load the complete sorted array including all entries.

Answers


Tom Wright February 2016

It depends what you mean by "better".

What makes things better? Speed, simplicity, versatility?

What happens if you save the file and then the table is updated? You will be missing some rows in your file. You also can't guarantee that storing the rows in a file is actually going to be faster. MySQL can get quite good at caching if the table isn't being updated much.

That being said, if speed is that important to you I would look at Memcached or Redis. Both of these are storage solutions for key-pair data in which the data is stored in memory. You could do something like this to implement memcached:

function getTableRows()
{
    $memcached = & get_memcached_instance();
    $result = $memcached->get("myTableRows");
    if (! is_array($result)) {
        $result = $this->model->fetchSortedRowsFromDb();
        $memcached->put("myTableRows", $result);
    }
    return $result;
}

You can then use only the required indexes for your pagination. Keep in mind you would have to delete the cache every time the table is updated.

Is that much of a speed improvement required though? As your table gets more and more rows it will put more strain on PHP and may eventually lead to memory issues. You can quite easily use LIMIT and OFFSET to deal with this kind of thing and assuming your tables are indexed properly it shouldn't give much of a performance hit.

Post Status

Asked in February 2016
Viewed 3,589 times
Voted 11
Answered 1 times

Search




Leave an answer


Quote of the day: live life