Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Which is faster / more efficient - lots of little MySQL queries or one big PHP array?

I have a PHP/MySQL based web application that has internationalization support by way of a MySQL table called language_strings with the string_id, lang_id and lang_text fields.

I call the following function when I need to display a string in the selected language:

public function get_lang_string($string_id, $lang_id)
    {
        $db = new Database();
        $sql = sprintf('SELECT lang_string FROM language_strings WHERE lang_id IN (1, %s) AND string_id=%s ORDER BY lang_id DESC LIMIT 1', $db->escape($lang_id, 'int'), $db->escape($string_id, 'int'));
        $row = $db->query_first($sql);

        return $row['lang_string'];
    }

This works perfectly but I am concerned that there could be a lot of database queries going on. e.g. the main menu has 5 link texts, all of which call this function.

Would it be faster to load the entire language_strings table results for the selected lang_id into a PHP array and then call that from the function? Potentially that would be a huge array with much of it redundant but clearly it would be one database query per page load instead of lots.

Can anyone suggest another more efficient way of doing this?

like image 317
Pandy Legend Avatar asked Aug 21 '12 08:08

Pandy Legend


People also ask

What is faster one big query or many small queries in SQL?

However, in the context of the question, a single large query will be faster that, let's say -in the worse possible scenario- a SELECT inside a programming loop (no matter the RDBMS used).

Which query is faster in MySQL?

MySQL full-text search (FTS) is far much faster than queries using wildcard characters.

Are views faster than queries MySQL?

No, a view is simply a stored text query. You can apply WHERE and ORDER against it, the execution plan will be calculated with those clauses taken into consideration.

Is MySQL the fastest?

Ultimately, speed will depend on the way you're using the database. PostgreSQL is known to be faster for handling massive data sets, complicated queries, and read-write operations. Meanwhile, MySQL is known to be faster with read-only commands.


1 Answers

There isn't an answer that isn't case sensitive. You can really look at it on a case by case statement. Having said that, the majority of the time, it will be quicker to get all the data in one query, pop it into an array or object and refer to it from there.

The caveat is whether you can pull all your data that you need in one query as quickly as running the five individual ones. That is where the performance of the query itself comes into play.

Sometimes a query that contains a subquery or two will actually be less time efficient than running a few queries individually.

My suggestion is to test it out. Get a query together that gets all the data you need, see how long it takes to execute. Time each of the other five queries and see how long they take combined. If it is almost identical, stick the output into an array and that will be more efficient due to not having to make frequent connections to the database itself.

If however, your combined query takes longer to return data (it might cause a full table scan instead of using indexes for example) then stick to individual ones.

Lastly, if you are going to use the same data over and over - an array or object will win hands down every single time as accessing it will be much faster than getting it from a database.

like image 173
Fluffeh Avatar answered Sep 21 '22 02:09

Fluffeh