I need help optimizing the query. I have a query that takes 12 seconds to run for too long, and I would be very pleased if I could get some help trying to optimize it, since I am not a sql guru. There he is:
SELECT ID ,
user_login ,
user_nicename ,
user_registered ,
user_status ,
display_name ,
t1.meta_value AS account_type ,
1 t2.meta_value AS views ,
GROUP_CONCAT(t4.term_id) AS interests_skills,
GROUP_CONCAT(t4.taxonomy) AS taxonomyComb ,
t4.term_id ,
t4.taxonomy
FROM wp_users
INNER JOIN wp_usermeta AS t1
ON (
t1.user_id = wp_users.ID
AND
(
t1.meta_key = 'account_type'
AND t1.meta_value = 'individual'
)
)
LEFT JOIN wp_usermeta AS t2
ON (
t2.user_id = wp_users.ID
AND t2.meta_key = 'views'
)
LEFT JOIN wp_term_relationships AS t3
ON (
t3.object_id = (1000000+wp_users.ID)
)
INNER JOIN wp_term_taxonomy AS t4
ON (
(
t3.term_taxonomy_id = t4.`term_taxonomy_id`
AND t4.taxonomy = 'category'
AND t4.term_id IN (396,410,411,416,142,417)
)
OR
(
t3.term_taxonomy_id = t4.`term_taxonomy_id`
AND t4.taxonomy = 'skill'
AND t4.term_id IN (461,463,464,466,490,468,470,491,473,474,475)
)
)
WHERE t4.term_id IS NOT NULL
GROUP BY ID LIMIT 0,10
Here is an explanation
1 SIMPLE range t4 PRIMARY, term_id_taxonomy, taxonomy term_id_taxonomy 106 NULL 17 Use where; The use of temporary; Using filesort
1 SIMPLE t1 ref user_id, meta_key meta_key 768 const 3773 Use where
1 SIMPLE wp_users eq_ref PRIMARY PRIMARY 8 jasper_gi.t1.user_id 1
1 SIMPLE t2 ref user_id, meta_key meta_key 768 const 2
1 SIMPLE t3 eq_ref PRIMARY, term_taxonomy_id PRIMARY 16 func, jasper_gi.t4.term_taxonomy_id 1 Use where; Index usage