MySQL Query Speed Up

Question

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:

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 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. :)

What indexes have you defined (use `SHOW INDEXES FROM table1`)? — Salman A, May 03 '12 at 11:12
This question is addressed here: http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema — srini.venigalla, May 03 '12 at 12:42

score 2 · Accepted Answer · answered May 03 '12 at 11:17

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

This works great! Only problem is that it won't work if I wanted to add a WHERE clause using the meta data. I guess I should have mentioned I was planning on doing that, too! For example, I can't run "WHERE metaUserLevel = 'student'". I've amended the original description to include this requirement. — Luke Carbis, May 03 '12 at 12:36

ypercubeᵀᴹ · Answer 2 · 2012-05-03T12:57:37.650

1

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?

edited May 03 '12 at 12:57

answered May 03 '12 at 12:43

ypercubeᵀᴹ

113,259
19
174
235

Thanks guys - this does help a bit :) Still running quite slow though. – Luke Carbis May 03 '12 at 13:06
@Curbis: Can you add the `EXPLAIN` plan after this index was added? – ypercubeᵀᴹ May 03 '12 at 13:22

MySQL Query Speed Up

2 Answers2