Geek asks and answers

MySQL Query Speed ​​Up

I have a problem with the request speed - it works for about 16 seconds, and I need to speed it up!

My table layout is as follows:

Users

  • id (int 10, primary key)
  • username (varchar 100)
  • password (varchar 100)

Users_meta:

  • id (int 10, primary key)
  • user (int 10)
  • meta (varchar 100)
  • value (longtext)

I need to return data from different rows to the user's meta-table (e.g. first name, last name, etc.) in the form of columns. This request does the job, but runs too slowly:

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')

I also need to be able to add WHERE and ORDER BY clauses to the query.

Thank you for your help.:)

+3
source share
2 answers

I do not know if it is faster. But there might be something like this:

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
+2
source

meta: (meta, user, value). (user, meta, value). , WHERE.

() Users_meta, .

longtext - . , ?

+1

More articles:


All Articles