GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Please explain me why this is taking so long.

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Please explain me why this is taking so long.

    Hi , i have the following query :

    SELECT STRAIGHT_JOIN u.user_id,u.username,u.email,u.birth_date,u.descri ption,u.registration_ip, i.name AS image, ci.name AS city,c.name AS country FROM users u INNER JOIN user_images i ON i.user_id=u.user_id AND i.zone="primary" INNER JOIN cities ci ON ci.city_id=u.city_id INNER JOIN countries c ON c.country_id=ci.country_id WHERE u.status="inactive" ORDER BY u.username ASC LIMIT 0,20

    This executes in : 0.0298 which i think is way too much .
    i have 174,792 total users, everyone has 1 image , so 174,792 images .
    I also have only 1 country with 141 cities .
    The output of explain is like :

    mysql> +----+-------------+-------+--------+---------------------------------+----------------------+---------+-------------------+------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------------------------+----------------------+---------+-------------------+------+-----------------------------+| 1 | SIMPLE | u | ref | PRIMARY,fk_users_cities1,status | status | 2 | const | 9368 | Using where; Using filesort || 1 | SIMPLE | i | ref | fk_user_images_users,zone | fk_user_images_users | 4 | dev.u.user_id | 1 | Using where || 1 | SIMPLE | ci | ref | PRIMARY,fk_cities_countries1 | PRIMARY | 4 | dev.u.city_id | 1 | || 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | dev.ci.country_id | 1 | |+----+-------------+-------+--------+---------------------------------+----------------------+---------+-------------------+------+-----------------------------+4 rows in set (0.00 sec)

    Could you tell me if it's ok or not ?
    Because i reach a point in where i don't know what to think anymore ...
    Thank you .

  • #2
    Using filesort is definitely wrong, consider using good indices. This query should execute at least 10 times faster.

    Comment


    • #3
      Could you please provide me a modified version of it that runs faster ? At least in this case i find out what am i doing wrong .
      I have indexes on the columns on which i use where and more as you can see in the explain statement .

      Comment


      • #4
        The query itself is fine, look into multi column indices, unless u.status='inactive' is not restrictive in which case an index on username suffices.

        Comment


        • #5
          Makes sense to you ?

          users 0 PRIMARY 1 user_id A 174792 NULL NULL BTREE users 0 PRIMARY 2 city_id A 174792 NULL NULL BTREE users 0 PRIMARY 3 country_id A 174792 NULL NULL BTREE users 0 PRIMARY 4 interest_id A 174792 NULL NULL BTREE users 1 users_username1 1 username A 174792 NULL NULL YES BTREE users 1 fk_users_cities1 1 city_id A 6 NULL NULL BTREE users 1 fk_users_cities1 2 country_id A 6 NULL NULL BTREE users 1 fk_users_interests1 1 interest_id A 6 NULL NULL BTREE users 1 status 1 status A 8 NULL NULL YES BTREE

          Comment


          • #6
            I would like to ask you the same question.

            Comment


            • #7
              From my perspective it does , but i might be wrong that's why i am asking )

              Comment


              • #8
                http://dev.mysql.com/doc/refman/5.0/...l-indexes.html

                Comment


                • #9
                  Yeah sure , thanks )

                  Comment


                  • #10
                    I did some progress, maybe you wanna take a look :

                    SELECT STRAIGHT_JOIN u.user_id, u.username, u.email, u.birth_date, u.description, u.registration_ip, i.name AS image, ci.name AS city, c.name AS countryFROM users uINNER JOIN user_images i ON i.user_id = u.user_idAND i.zone = "primary"INNER JOIN cities ci ON ci.city_id = u.city_idAND ci.country_id = u.country_idINNER JOIN countries c ON c.country_id = ci.country_idWHERE u.status = "inactive"ORDER BY u.user_id DESCLIMIT 0 , 20



                    And the explain:


                    id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE u ref PRIMARY,fk_users_cities1,status status 2 const 473 Using where1 SIMPLE i ref fk_user_images_users,zone fk_user_images_users 4 dev.u.user_id 1 Using where1 SIMPLE ci eq_ref PRIMARY,fk_cities_countries1 PRIMARY 8 dev.u.city_id,dev.u.country_id 1 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 dev.ci.country_id 1 Using where


                    Time

                    Query took 0.0007 sec

                    Comment


                    • #11
                      You change the query, that is cheating. Try an index on (status,username) or just (username).

                      Comment


                      • #12
                        This is what i am saying , i have indexes on that columns )

                        Comment


                        • #13
                          you don't, please read about multi column indices.

                          Comment


                          • #14
                            I need a little advice from you .
                            Let's say i have :
                            Where username="John" and status="active"

                            In this case , a index on username,status would be better than one on username and another on status ?

                            Answering this q will hellp me on the previous query .
                            Thanks for your help so far .

                            Comment


                            • #15
                              The answer is yes. But if the user John is unique, an index on user could be better.

                              Comment

                              Working...
                              X