CODEWITHSUNDEEP
phpmysqlarrayssorting
Adam
Adam

Reputation: 28969

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

or should one rather

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.

Upvotes: 1

Views: 191

Answers (1)

Tom Wright
Tom Wright

Reputation: 2861

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.

Upvotes: 1

Related Questions