CODEWITHSUNDEEP
mysqldatabaseperformanceleft-join
Luke Carbis
Luke Carbis

Reputation: 355

MySQL Query Speed Up

I'm having a problem with the speed of a query - it's running at about 16 seconds at the moment, and I need to speed it up!

My table scheme is as follows:

Users:

Users_meta:

I need to return data from various rows in the user meta table (such as first name, last name etc.) as columns. This query does the job, but runs too slow:

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.

Thanks for your help. :)

Upvotes: 3

Views: 196

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

I would first add a compound index on table meta: (meta, user, value). Or (user, meta, value). These would sure help if you had additional WHERE conditions in your query.

The query has now to use (almost) all the data in table Users_meta so these indexes may not be used.

The longtext datatype is another problem. Are you sure you need so wide column there?

Upvotes: 1

Arion
Arion

Reputation: 31239

I don't know if this is faster. But maybe 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

Upvotes: 2

Related Questions