这是更快/更有效的 - 许多小的MySQL查询或一个大PHP数组的？ [英] Which is faster / more efficient - lots of little MySQL queries or one big PHP array?

问题描述

我曾借一个MySQL表language_strings与string_id，LANG_ID，lang_text领域的方式多语言支持PHP / MySQL的基于Web应用程序。然后，我调用下面的函数，当我需要在所选语言显示的字符串...

I have a PHP/MySQL based web application which has multiple language support by way of a MySQL table "language_strings" with string_id, lang_id, lang_text fields. I then 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'];
} 

这完美的作品，但我担心，有可能是一个很大的数据库查询请求回事。例如主菜单中有5个链接文本，所有这一切都调用这个函数。

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.

难道是更快所选LANG_ID整个language_strings表结果加载到一个PHP数组，然后调用从功能？可能这将是与大部分是多余的一个巨大的数组但很明显，这将是每个页面加载，而不是大量的一个数据库查询。

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 pageload instead of lots.

任何人都可以提出这样做​​的另一种更有效的方式？

Can anyone suggest another more efficient way of doing this?

推荐答案

确定 - 我做了一些基准测试，惊讶地发现，把东西放进一个数组，而不是使用单独的查询是，平均而言，速度较慢的10-15％。

OK - I did some benchmarking and was surprised to find that putting things into an array rather than using individual queries was, on average, 10-15% SLOWER.

我觉得这样做的原因是因为，即使我过滤掉了罕见的元素，难免有总是将是未使用的元素作为理所当然的事。

I think the reason for this was because, even if I filtered out the "uncommon" elements, inevitably there was always going to be unused elements as a matter of course.

通过个人查询，我永远只能走出什么，我需要和查询是如此简单，我认为我是最好的与该方法坚持。

With the individual queries I am only ever getting out what I need and as the queries are so simple I think I am best sticking with that method.

这对我的作品，当然在个别查询更复杂的其他情况的，我觉得在阵列中存储常用的数据会变成更有效率的方法。

This works for me, of course in other situations where the individual queries are more complex, I think the method of storing common data in an array would turn out to be more efficient.

这篇关于这是更快/更有效的 - 许多小的MySQL查询或一个大PHP数组的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！