CODEWITHSUNDEEP
mysql
voyeger
voyeger

Reputation: 139

Query taking long time to execute

I have the following query, I am using the betwen operators to fetch the records who have the age between the specific numbers, but the query is taking a hell lot of time to display me the results, its benn more than 5 mins it is still running 

SELECT
  *,
  (SELECT COUNT(*)
   FROM users
   WHERE
     1=1 OR
     LOWER(age) BETWEEN 18 AND 20 OR
     LOWER(age) BETWEEN 20 AND 25 OR
     LOWER(age) BETWEEN 25 AND 30 OR
     LOWER(age) BETWEEN 30 AND 35
  ) AS totalcount
FROM users, states, countries, user_types, media
WHERE
  users.id_user_type = user_types.id AND
  users.id_state = states.id AND
  users.id_country = countries.id AND
  media.id_user = users.id AND
  media.profile_photo = 1 AND
  users.id_user_type = 3 OR
  LOWER(age) BETWEEN 18 AND 20 OR
  LOWER(age) BETWEEN 20 AND 25 OR
  LOWER(age) BETWEEN 25 AND 30 OR
  LOWER(age) BETWEEN 30 AND 35
ORDER BY users.id

The troubling Part is: 

LOWER(age) BETWEEN 18 AND 20 OR
LOWER(age) BETWEEN 20 AND 25 OR
LOWER(age) BETWEEN 25 AND 30 OR
LOWER(age) BETWEEN 30 AND 35

Upvotes: 0

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Your or statements are parsed incorrectly. You need parentheses:

FROM users, states, countries, user_types, media
WHERE (users.id_user_type = user_types.id AND
       users.id_state = states.id AND
       users.id_country = countries.id AND
       media.id_user = users.id AND
       media.profile_photo = 1 AND
       (users.id_user_type = 3 OR
        LOWER(age) BETWEEN 18 AND 20 OR
        LOWER(age) BETWEEN 20 AND 25 OR
        LOWER(age) BETWEEN 25 AND 30 OR
        LOWER(age) BETWEEN 30 AND 35
       )

As written, your query has to do a full cartesian product among the five tables and then do some weird filtering.

This would be basically a non-issue if you used proper join syntax. Simple rule: NEVER use commas in the from clause.

In addition, the query doesn't seem to make sense. Why would you store age as a string (as suggested by the use of lower()? Then, why would you compare these to integers? Why would you split consecutive ranges into four different comparisons, instead of just saying age between 18 and 35?

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

Avoid using the or clause you have (it does not make sense). Your query is probably doing a lot of CROSS JOIN operations and that's why it's taking too long (your resultset is HUGE).

 select *,
        (
            select count(*) 
              from users 
             where 1=1 
                or lower(age) between 18 and 20 
                or lower(age) between 20 and 25 
                or lower(age) between 25 and 30 
                or lower(age) between 30 and 35
        ) as totalcount 
   from users, states, countries, user_types, media 
  where users.id_user_type = user_types.id 
    and users.id_state = states.id 
    and users.id_country = countries.id 
    and media.id_user = users.id 
    and media.profile_photo = 1 
    and users.id_user_type = 3 
  order by users.id

Also, it's not clear what you are trying to accomplish with this query.

Answering these question might help us help you:

  1. What's the age column type
  2. What exactly are you expecting from your inner query

Upvotes: 1

Related Questions