MySQL 查询加速

Question

我的查询速度有问题 - 它目前运行大约 16 秒，我需要加快速度!

我的表方案如下:

用户:

• id (int 10, 主键)
• 用户名 (varchar 100)
• 密码(varchar 100)

用户元:

• id (int 10, 主键)
• 用户(int 10)
• 元数据(varchar 100)
• 值(长文本)

我需要将用户元表中各行的数据(例如名字、姓氏等)作为列返回。此查询可以完成工作，但运行速度太慢:

SELECT
Users.id as id,
Users.username as username,
firstName.value as metaFirstName,
lastName.value as metaLastName,
userLevel.value as metaUsername,
employer.value as metaEmployer,
gto.value as metaGTO
FROM Users
LEFT JOIN (Users_meta as firstName) ON (firstName.user = Users.id AND firstName.meta = 'first_name')
LEFT JOIN (Users_meta as lastName) ON (lastName.user = Users.id AND lastName.meta = 'last_name')
LEFT JOIN (Users_meta as userLevel) ON (userLevel.user = Users.id AND userLevel.meta = 'user_level')
LEFT JOIN (Users_meta as employer) ON (employer.user = Users.id AND employer.meta = 'employer')
LEFT JOIN (Users_meta as gto) ON (gto.user = Users.id AND gto.meta = 'gto')

我还需要能够向查询中添加 WHERE 和 ORDER BY 子句。

感谢您的帮助。 :)

Answer 1

我不知道这样会不会更快。但也许是这样的:

SELECT
    Users.id as id,
    Users.username as username,
    MAX(CASE WHEN Users_meta.meta = 'first_name' THEN Users_meta.value ELSE NULL END) AS metaFirstName,
    MAX(CASE WHEN Users_meta.meta = 'last_name' THEN Users_meta.value ELSE NULL END) AS metaLastName,
    MAX(CASE WHEN Users_meta.meta = 'user_level' THEN Users_meta.value ELSE NULL END) AS metaUsername,
    MAX(CASE WHEN Users_meta.meta = 'employer' THEN Users_meta.value ELSE NULL END) AS metaEmployer,
    MAX(CASE WHEN Users_meta.meta = 'gto' THEN Users_meta.value ELSE NULL END) AS metaGTO
FROM
    Users
    LEFT JOIN Users_meta
        ON Users_meta.user = Users.id
GROUP BY
    Users.ID,
    Users.username