MYSQL - Left joins, optimize 2 sec query

I try to optimize my MySQL query, I merged already all tables to one table where it seems useful.

But the query still takes over 2 seconds... Is there a way to make it faster?

On user_id is always an index. MySQL 5.5.12, all tables except cities are InnoDB tables.

SELECT b.user_id,b.firstname,b.lastname,b.address,b.zipcode,b.city
  , ($calculatings) AS Distance
FROM `user_profiles` `b`
LEFT JOIN `cities` `a` ON `a`.`postal`=`b`.`zipcode` 
JOIN `users` `u` ON `b`.`user_id`=`u`.`id` 
JOIN `user_consultants` `c` ON `b`.`user_id`=`c`.`user_id` 
WHERE ($calculatings) <= 25 
      AND c.incorporated='1' 
      AND u.typ='1' 
      AND u.activated='1' 
      AND u.banned='0' 
ORDER BY Distance ASC, c.zsum_score DESC 
LIMIT 30

In the var $calculatings are the math operations for the distance calculation (already optimized).

$calculatings example:

6368 * SQRT(2*(1-cos(RADIANS(`a`.`lat`)) * cos(0.840105508801) * 
(sin(RADIANS(`a`.`lon`)) * sin(0.201952047748) + cos(RADIANS(`a`.`lon`)) * 
cos(0.201952047748)) - sin(RADIANS(`a`.`lat`)) * sin(0.840105508801)))

Why so many Left Joins?

1. user_profiles is the detail_table of the user informations
2. cities is the city table with all cities of my country with la开发者_如何学Got and lon and more informations
3. users is the main user table for username & password, hashes, login tries, bans etc...
4. user_consultants is an additional table for a special group of users

Sizes

1. user_profiles 112.000 rows
2. cities 68.000 rows
3. users 246.000 rows
4. user_consultants 98.000 rows

Explain SQL

I have no time to write full details but to optimize spatial search, here's a quick quide:

Store the (latitude, longitude) pair in the (MyISAM) table as a spatial field: POINT (a variant of GEOMETRY type).

Add a spatial index on this field.

Use the MBRContains() or the MBRWithin() function in your query, with something like this, that wil use the spatial index to narrow the search inside a square that contains the circle with Radius 25 from your base point:

WHERE MBRWithin( cities.myPointField
               , Polygon( @lat-25 @long-25
                        , @lat+25 @long-25
                        , @lat+25 @long+25
                        , @lat-25 @long+25
                        ) 
               )
  AND (yourDistanceCalculation) < 25

You can check the MySQL docs: Spatial Extensions

You should have (at least) indexes on: cities.postal, users.typ, users.activated, users.banned, user_consultants.incorporated

SELECT 
    b.user_id,b.firstname,b.lastname,b.address,b.zipcode,b.city,
    ($calculatings) AS Distance
FROM 
    `user_profiles` `b`
    JOIN `users` `u` ON `b`.`user_id`=`u`.`id`
     AND `u`.`typ`=1
     AND `u`.`activated`=1
     AND `u`.`banned`=0
    LEFT JOIN `cities` `a` ON `b`.`zipcode`=`a`.`postal` 
    LEFT JOIN `user_consultants` `c` ON `b`.`user_id`=`c`.`user_id` 
WHERE Distance <= 25
ORDER BY Distance ASC, c.zsum_score DESC
LIMIT 0,30

... although, if the value of $calculatings is always the same and as I see that it only depends on the data in table cities - you should just put another column in it, containing the pre-calculated distance value.

A few notes on the changes I've made:

• I just assume that typ, activated and banned are of type int (guessing by the values in your query) - you shouldn't put them in quotes.
• I also assume that since users is your main users table, every user_id from user_profiles should have an existing id in users, so you don't need LEFT.
• JOINs are faster than WHERE clauses (and WHERE is faster than HAVING, as I see another answer that utilizes it).
• As Tudor Constantin has answered - you should take care of indexing all columns that you use as references for join.

I think you are going about it slightly off (but could be wrong). You are starting your query based on the user profiles, but all your criteria are at the lowest level of users and user consultants level. I would do a STRAIGHT_JOIN (tell optimizer to do in the order YOU declare). Then, on your joins, doing a LEFT JOIN doesn't necessarily make sense unless you have missing link ID values between the tables which would allow for some records to not have a given city or user profile. So, that being said, I would put your Users table up front since that would probably have the most restrictive result set via criteria. In addition, have an index on (typ, activated, banned). Next, your user_consultants table and have an index on that for (user_id, incorporated). Cities should have an index on postal, and user_profiles, an index on zipcode.

Here's the final query I would try

select STRAIGHT_JOIN
      b.user_id,
      b.firstname,
      b.lastname,
      b.address,
      b.zipcode,
      b.city, 
      ($calculatings) AS Distance
   from 
      (select u.id, c.zsum_score
          from 
             users u
                join user_consultants c
                   on u.id = c.user_id
                  and c.incorporated = '1'
          where
                 u.typ = '1'
             and u.activated = '1'
             and u.banned = '0' ) PreQuery
      join user_profiles b
         on PreQuery.ID = b.user_id
         join cities a on b.zipcode = a.postal
   where
      ($calculatings) <= 25 
   ORDER BY
      Distance ASC, 
      PreQuery.zsum_score DESC 
   LIMIT 30

Since the join between user and user consultants was on user ID, then user consultants and user profiles was on user ID, the join between the "PreQuery's" ID is the same thing, so no need to re-join to BOTH tables.

SELECT 
    b.user_id,b.firstname,b.lastname,b.address,b.zipcode,b.city, 
    ($calculatings) AS Distance
FROM 
    `user_profiles` `b`
    JOIN `cities` `a` ON `a`.`postal`=`b`.`zipcode` 
    JOIN `users` `u` ON `b`.`user_id`=`u`.`id` 
    JOIN `user_consultants` `c` ON `b`.`user_id`=`c`.`user_id` 
WHERE     
    c.incorporated='1'  AND 
    u.typ='1' AND 
    u.activated='1' AND 
    u.banned='0' 
HAVING
    Distance <= 25
ORDER BY 
    Distance ASC, c.zsum_score DESC 
LIMIT 30