我目前正试图优化一个沉重的脚本(查询)，这是采取4.9452秒运行。然后有一个PHP while循环，根据返回的内容设置一些数组，但这是另一个问题。
EDIT - users表为~9000行，user_address表为~3000行，shop_countries表为~200行。
这是我的查询:
SELECT
users.id AS user_id,
users.unique_code AS user_unique_code,
users.first_name AS user_first_name,
users.surname AS user_surname,
users.organisation AS user_organisation,
users.telephone AS user_telephone,
users.email AS user_email,
users.password AS user_password,
users.newsletter AS user_newsletter,
user_address.id AS user_address_id,
user_address.user_id AS user_address_user_id,
user_address.nickname AS user_address_nickname,
user_address.address_type AS user_address_type,
user_address.address_line_1 AS user_address_line_1,
user_address.address_line_2 AS user_address_line_2,
user_address.address_line_3 AS user_address_line_3,
user_address.city AS user_address_city,
user_address.postcode AS user_address_postcode,
user_address.country_id AS user_address_country_id,
shop_countries.printable_name
FROM users
LEFT JOIN user_address ON users.id = user_address.user_id
LEFT JOIN shop_countries ON shop_countries.id = user_address.country_id
WHERE (users.surname != "" OR users.first_name != "")
ORDER BY users.surname ASC, users.first_name ASC;
我需要选择所有用户，无论他们是否有分配给他们的地址，然后附加任何国家，如果有可用的地址。
下面是EXPLAIN扩展
的结果http://imgur.com/a/UlVX3 到目前为止，似乎如果我将LEFT JOIN更改为JOIN，那么查询将在不到一秒的时间内执行。问题是我需要所有的用户，不管他们是否有地址。PHP脚本对于任何想要查看相应PHP函数以参考我的设计的人，请参阅下面:
function getAllUsersWithAddresses() {
$customers = array();
$sql = 'SELECT
users.id AS user_id,
users.unique_code AS user_unique_code,
users.first_name AS user_first_name,
users.surname AS user_surname,
users.organisation AS user_organisation,
users.telephone AS user_telephone,
users.email AS user_email,
users.password AS user_password,
users.newsletter AS user_newsletter,
user_address.id AS user_address_id,
user_address.user_id AS user_address_user_id,
user_address.nickname AS user_address_nickname,
user_address.address_type AS user_address_type,
user_address.address_line_1 AS user_address_line_1,
user_address.address_line_2 AS user_address_line_2,
user_address.address_line_3 AS user_address_line_3,
user_address.city AS user_address_city,
user_address.postcode AS user_address_postcode,
user_address.country_id AS user_address_country_id,
shop_countries.printable_name
FROM users
LEFT JOIN user_address ON users.id = user_address.user_id LEFT JOIN shop_countries ON shop_countries.id = user_address.country_id WHERE (users.surname != "" OR users.first_name != "") ORDER BY users.surname ASC, users.first_name ASC;';
$users_query = mysql_query($sql);
$customers = array();
while($row = getData($users_query)) {
if(!isset($customers[$row['user_id']])) {
$customers[$row['user_id']] = array(
'id' => $row['user_id'],
'unique_code' => $row['user_unique_code'],
'first_name' => $row['user_first_name'],
'surname' => $row['user_surname'],
'organisation' => $row['user_organisation'],
'telephone' => $row['user_telephone'],
'email' => $row['user_email'],
'password' => $row['user_password'],
'newsletter' => $row['user_newsletter']
);
}
if(isset($customers[$row['user_id']]) && !empty($row['user_address_type'])) {
$customers[$row['user_id']]['addresses'][$row['user_address_type']][] = array(
'id' => $row['user_address_id'],
'user_id' => $row['user_address_user_id'],
'nickname' => $row['user_address_nickname'],
'address_type' => $row['user_address_type'],
'address_line_1' => $row['user_address_line_1'],
'address_line_2' => $row['user_address_line_2'],
'address_line_3' => $row['user_address_line_3'],
'city' => $row['user_address_city'],
'postcode' => $row['user_address_postcode']
);
} else {
$customers[$row['user_id']]['addresses'] = array();
}
}
return $customers;
}
为外部id创建索引
- user_address.user_id
- user_address.country_id
我看不出你的解释计划——如果你把它贴在你的问题里就好了。然而，即使我可以，它没有太大的帮助，不知道数据库的结构-请包括创建表语法和索引。在理想情况下，您还应该包括索引基数。
只有两种方法可以使查询更快。减少数据库需要检查的行数，或者使DBMS更容易找到您想要返回的行。
我猜大部分开销来自查询中的2个LEFT join。你肯定有一份完整的国家名单吧?没有那么多。因此，您应该能够将第二个外部连接替换为内部连接。
(用户的地方。姓!= "或用户。First_name != ")
在mysql查询中使用'OR'可以从根本上改变它的行为。你有很多客户的名字不存在吗?如果小于5%，则....
WHERE LENGTH(users.surname) AND LENGTH(users.first_name)
…超过5%且索引....
WHERE users.surname LIKE '_%' AND users.first_name LIKE '_%'
但是最大的好处来自于确保主键和外键上都有索引。