有几个主题我已经尝试过,我自己的尝试和错误,但我不能让下面的查询加快。查询工作正常,但速度非常慢:)
我读过并尝试过的主题:
需要帮助优化mysql查询与很少的连接吗
mysql从键/值对表中选择
基于完全匹配的所有“键/值”对的两个表的内部连接
在sql server中将同一表的两个内部联接的结果合并到一个临时表中
以下是我的疑问:
SELECT `users`.`id`,`users`.`name`,`users`.`email`
FROM `test_users` AS `users`
LEFT JOIN `test_fields_values` AS `fields_values` ON
`fields_values`.`item_id` = `users`.`id`
INNER JOIN `test_fields_values` AS `fields_values_lastname` ON
`users`.`id` = `fields_values_lastname`.`item_id`
WHERE
(`fields_values`.`field_id` IS NOT NULL) AND
(`fields_values_lastname`.`field_id` = 13)
GROUP BY `users`.`id`
ORDER BY `fields_values_lastname`.`value`
表格如下所示。用户表:
id | name | email
--------------------------------
1 | Myself | myself@example.com
字段值表,而不是我在字段名称中输入的实际值,以便于理解。
field_id | item_id | value
------------------------------------------------------------
4 | 1 | Address
5 | 1 | Phone
6 | 1 | City
7 | 1 | Zipcode
8 | 1 | Email
9 | 1 | Country
10 | 1 | Lastname
11 | 1 | H
12 | 1 | I
13 | 1 | J
14 | 1 | K
15 | 1 | L
16 | 1 | M
17 | 1 | N
这个 item_id
中的字段 fields_values
表匹配 id
中的字段 users
table。
我的目标如下:
这个 user
表中有大约5500个条目 field_values
表中每个用户有13个条目。我目前正在做的是获取一个按姓氏排序的用户列表,并在列表中显示它们。此列表有一个筛选器,因此可以按国家/城市和常规搜索筛选用户列表。
过滤器适用于 value
的字段 fields_values
table。所以我可以让所有居住在比利时的用户举例。
对于每个过滤器,我添加另一个innerjoin,因此查询如下:
SELECT `users`.`id`,`users`.`name`,`users`.`email`
FROM `test_users` AS `users`
LEFT JOIN `test_fields_values` AS `fields_values` ON
`fields_values`.`item_id` = `users`.`id`
INNER JOIN `test_fields_values` AS `fields_values_lastname` ON
`users`.`id` = `fields_values_lastname`.`item_id`
INNER JOIN `test_fields_values` AS `fields_values_country` ON
`users`.`id` = `fields_values_country`.`item_id`
WHERE
(
(`fields_values`.`field_id` IS NOT NULL) AND
(`fields_values_lastname`.`field_id` = 13)) AND
(`fields_values_country`.`value` = 'belgium')
GROUP BY `users`.`id`
ORDER BY `fields_values_lastname`.`value`
即使没有应用过滤器,列表也需要2,5分钟来加载和更新 EXPLAIN
显示它检索到73979条记录,大约是5500*13条。
欢迎任何提示/提示/改进。谢谢您。
编辑: users
table
CREATE TABLE `test_users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL DEFAULT '',
`username` VARCHAR(150) NOT NULL DEFAULT '',
`email` VARCHAR(100) NOT NULL DEFAULT '',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`usertype` VARCHAR(25) NOT NULL DEFAULT '',
`block` TINYINT(4) NOT NULL DEFAULT '0',
`sendEmail` TINYINT(4) NULL DEFAULT '0',
`registerDate` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastvisitDate` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`activation` VARCHAR(100) NOT NULL DEFAULT '',
`params` TEXT NOT NULL,
PRIMARY KEY (`id`),
INDEX `usertype` (`usertype`),
INDEX `idx_name` (`name`),
INDEX `idx_block` (`block`),
INDEX `username` (`username`),
INDEX `email` (`email`)
)
COLLATE='utf8_general_ci';
``` `fields_values` table
CREATE TABLE test_fields_values
(field_id
INT(10) UNSIGNED NOT NULL,item_id
VARCHAR(255) NOT NULL COMMENT 'Allow references to items which have strings as ids, eg. none db systems.' COLLATE 'utf8mb4_unicode_ci',value
TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
INDEX idx_field_id
(field_id
),
INDEX idx_item_id
(item_id
(191))
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
特此说明:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
"1" | "SIMPLE" | "fields_values_lastname" | "ref" | "idx_field_id,idx_item_id" | "idx_field_id" | "4" | "const" | "3513" | "Using temporary; Using filesort"
"1" | "SIMPLE" | "users" | "eq_ref" | "PRIMARY" | "PRIMARY" | "4" | "cms_inter.fields_values_lastname.item_id" | "1" | "Using where"
"1" | "SIMPLE" | "fields_values" | "ALL" | "id_id,idx_item_id" | NULL | NULL | NULL | "73979" | "Using where; Using join buffer (flat, BNL join)"
暂无答案!
目前还没有任何答案,快来回答吧!