Geek Answers Handbook

What could cause the connection to be slower than splitting into two queries?

I am doing JOIN from multiple tables to do a facet search. When you avoid JOINs and split the query into two different ones, I noticed a big performance boost, so I assume my JOIN is not optimized.

Structure:

-- tags userId | tagId 1 3 1 4 2 3 2 9 -- search userId | number | countryId | stateId ... 1 13 221 55 -- countries countryId | countryName 221 Somewhere -- users userId | profileImageLink 1 | <photo link>

I am trying to extract all users that have a tag, order according to the search.number number, and transfer metadata from other tables. Request:

 SELECT search.*, users.a, users.b, users.c, users.d, users.e, users.f, countries.location_country, states.location_state, cities.location_city FROM search RIGHT JOIN tags ON search.user_id = tags.user_id LEFT JOIN users ON users.user_id=search.user_id LEFT JOIN countries ON countries.countryId=search.countryId LEFT JOIN states ON states.countryId=search.countryId AND states.stateId=search.stateId LEFT JOIN cities ON cities.countryId=search.countryId AND cities.stateId=search.stateId AND cities.cityId=search.cityId WHERE tags.skillId =52772 ORDER BY search.number DESC LIMIT 0,200

I noticed that removing the JOIN in the users table (and after that) makes the query much faster. How can I optimize it to work in a single request? I tried changing FROM to tags instead of searching, but that didn't work ...

Here is what EXPLAIN shows:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tags ref skill_user,skillId skill_user 4 const 184854 Using index; Using temporary; Using filesort 1 SIMPLE search eq_ref user_id user_id 4 tags.user_id 1 1 SIMPLE countries eq_ref PRIMARY PRIMARY 2 search.countryId 1 1 SIMPLE states eq_ref PRIMARY,state PRIMARY 3 search.stateId 1 1 SIMPLE cities eq_ref PRIMARY,city PRIMARY 3 search.cityId 1 1 SIMPLE users eq_ref user_id user_id 4 search.user_id 1 EXPLAIN without the LEFT JOIN users: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tags ref skill_user,skillId skill_user 4 const 155870 Using index 1 SIMPLE search eq_ref user_id user_id 4 tags.user_id 1 1 SIMPLE countries eq_ref PRIMARY PRIMARY 2 search.countryId 1 1 SIMPLE states eq_ref PRIMARY,state PRIMARY 3 search.stateId 1 1 SIMPLE cities eq_ref PRIMARY,city PRIMARY 3 search.cityId 1

DESCRIPTION of the request proposed in response:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tags index NULL userid_skill 8 NULL 22689539 Using where; Using index; Using temporary; Using filesort 1 SIMPLE search eq_ref user_id user_id 4 tags.user_id 1 1 SIMPLE users eq_ref user_id user_id 4 search.user_id 1 1 SIMPLE countries eq_ref PRIMARY PRIMARY 2 search.countryId 1 1 SIMPLE states eq_ref PRIMARY,state PRIMARY 3 search.stateId 1 1 SIMPLE cities eq_ref PRIMARY,city PRIMARY 3 search.cityId 1
+4
source share
1 answer

Create indexes in the tables to execute the query below:

 Table ColumnName ------ ---------- tags user_id, skillid (Both column in one index)

Try the following:

 SELECT s.*, ua, ub, uc, ud, ue, uf, c.location_country, st.location_state, ct.location_city FROM tags t LEFT JOIN search s ON t.user_id = s.user_id LEFT JOIN users u ON t.user_id = u.user_id LEFT JOIN countries c ON s.countryId = c.countryId LEFT JOIN states st ON s.stateId = st.stateId LEFT JOIN cities ci ON s.cityId= ct.cityId WHERE t.skillId =52772 ORDER BY s.number DESC LIMIT 0,200

EDIT

Try these two queries with the appropriate index and let me know that the below queries work for you or not.

 SELECT s.*, ua, ub, uc, ud, ue, uf, c.location_country, st.location_state, ct.location_city FROM (SELECT user_id FROM tags WHERE t.skillId = 52772) AS t LEFT JOIN search s ON t.user_id = s.user_id LEFT JOIN users u ON t.user_id = u.user_id LEFT JOIN countries c ON s.countryId = c.countryId LEFT JOIN states st ON s.stateId = st.stateId LEFT JOIN cities ci ON s.cityId= ct.cityId ORDER BY s.number DESC LIMIT 0,200;

OR

 SELECT s.*, ua, ub, uc, ud, ue, uf, c.location_country, st.location_state, ct.location_city FROM (SELECT t.user_id, s.* FROM tags t LEFT JOIN search s ON t.user_id = s.user_id WHERE t.skillId = 52772 ORDER BY s.number DESC LIMIT 0,200) AS t LEFT JOIN users u ON t.user_id = u.user_id LEFT JOIN countries c ON s.countryId = c.countryId LEFT JOIN states st ON s.stateId = st.stateId LEFT JOIN cities ci ON s.cityId= ct.cityId ORDER BY s.number DESC LIMIT 0,200;
+2
source

More articles:


All Articles