MySQL 查询优化 prestashop

Question

我目前正在编写一条 SQL 语句，用于在 Prestashop 设置数据库中获取特定国家/地区的时区。我正在使用的表是:

• ps_timezone:时区(加拿大/大西洋、美洲/埃达克等)
• ps_country_lang:国家/地区名称(加拿大、美国、法国等)
• ps_country:国家/地区表(我使用它是因为我知道该国家/地区，但我想知道它的区域)。
• ps_zone:区域(美洲、欧洲、亚洲等)

我编写了以下查询(id_country =4 是加拿大):

SELECT tz.id_timezone, tz.name FROM ps_timezone tz WHERE tz.name LIKE CONCAT((SELECT cl.name FROM ps_country_lang cl WHERE cl.id_country=4 and cl.id_lang = 1) ,'%') OR tz.name LIKE CONCAT((SELECT CASE WHEN INSTR(z.name, 'Europe') > 0 THEN 'Europe' WHEN INSTR(z.name, 'America') > 0 THEN 'America' ELSE z.name END as zone_name FROM ps_zone z JOIN ps_country c ON z.id_zone = c.id_zone WHERE c.id_country =4), '%') ORDER BY CASE WHEN tz.name LIKE CONCAT((SELECT cl.name FROM ps_country_lang cl WHERE cl.id_country=4 and cl.id_lang = 1) ,'%') THEN 1 ELSE 2 END, tz.name ASC;

查询工作正常，但我希望前辈就我的个人进步和文化的优化提出建议。

首先，我使用 LIKE 来验证时区是否以国家/地区名称开头:

tz.name LIKE CONCAT((SELECT cl.name FROM ps_country_lang cl WHERE cl.id_country=4 and cl.id_lang = 1) ,'%')

或区域名称:

tz.name LIKE CONCAT((SELECT CASE WHEN INSTR(z.name, 'Europe') > 0 THEN 'Europe' WHEN INSTR(z.name, 'America') > 0 THEN 'America' ELSE z.name END as zone_name FROM ps_zone z JOIN ps_country c ON z.id_zone = c.id_zone WHERE c.id_country =4), '%')

我怀疑 LIKE 关键字不是性能最好的关键字...提取时区的第一部分并直接进行比较是否会更高效:

WHERE LEFT(tz.name, INSTR(tz.name, "/") - 1) = (SELECT cl.name FROM ps_country_lang cl WHERE cl.id_country=4 and cl.id_lang = 1)

其次，我在 ORDER BY 中使用 SELECT，因此首先显示带有国家/地区名称的时区。

ORDER BY CASE WHEN tz.name LIKE CONCAT((SELECT cl.name FROM ps_country_lang cl WHERE cl.id_country=4 and cl.id_lang = 1) ,'%') THEN 1 ELSE 2 END, tz.name ASC

当然，这是一个美观问题，但我担心性能问题。查询是否只运行一次，因为它使用固定值(在将查询发送到数据库之前我知道国家/地区 ID，并且语言应始终为英语 1)，还是会为每一行运行？

此外，我不太喜欢它，因为我在 WHERE 子句和 ORDER BY 子句中都有完全相同的子查询。我无法使用 SELECT 子句中的别名，因为首先执行 WHERE 子句(无论如何，它会迫使我无用地返回一行)。

当然，如果我有该国家/地区的时区，最好的做法是排除带有大陆名称的时区...

我意识到这些都不是简单的问题，但我认为对于许多像我这样认真学习正确编程的初学者来说，这些都是有趣的事情。

谢谢，

来自蒙特利尔的 Jonathan Parent-Lévesque

Answer 1

我将利用 DateTimeZone 来简化此查询PHP 5.2 中引入的 PHP 组件，返回给定 ISO 3166-1 国家/地区代码的时区标识符(例如，加拿大的“CA”)。

示例:

<?php

$country_iso_code = 'CA';
$timezone = DateTimeZone::listIdentifiers(DateTimeZone::PER_COUNTRY, $country_iso_code);

echo $timezone[0]; /* Multiple timezones per country, get the 1st one */