Please explain me why this is taking so long.

MySQL & MariaDB Other MySQL® Questions
1

Hi , i have the following query :

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 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.

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 .

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.

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

6

I would like to ask you the same question.

7

From my perspective it does , but i might be wrong that’s why i am asking )

8

[URL]http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html[/URL]

9

Yeah sure , thanks )

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

11

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

12

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

13

you don’t, please read about multi column indices.

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 .

15

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

16

Yes , the username is unique and is marked as being an index .
Also i have another index on status .
That’s because there are few times when i use the username/status together to obtain some data, i mean i use status when i check for accounts to be activated , in the rest i use the username to obtain the user_id(bc i have a site having the url’s like Custom Application Development Software for Business - Salesforce.com ).
I did some tests with an index on username/status then erase it and made an index on username and another one on status and these one was faster than the first one .