Your opinion about this query .

[eluser]Twisted1919[/eluser]
I've posted this on a mysql forum but no luck , so i want to try here too :
I have the following query :

Code:

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(virtuals as i am doing tests), everyone has 1 image , so 174,792 images(in real web app they can have unlimited) .
I also have only 1 country with 141 cities .
The output of explain is like :

Code:

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)

Is 0.0298 too much or not ? Is the query built as it should ?
Thx.

[eluser]richthegeek[/eluser]
0.0298 on 174,792 rows is good.

Selection on an indexed column is O(log n), on an unindexed is O(n) or as it seems to presort the column, O(n log n), so the bulk of the cost (174,792 of about 175,000) is from searching the "status" column in the "users" table. If you add "status" to the indexed list you can cut the query cost from about 175,000 to about 50.

tl;dr: your query is fine, index the users.status column.

[eluser]Twisted1919[/eluser]
Thank you for the reply , the status column is indexed also , before that is true, i had a slower query .

[eluser]richthegeek[/eluser]
Then it's about as fast as it can be... 0.0298 is quite fast for a database of this size.

I can't imagine why you would want to return all 170k users and images at once though? Try a limit and order on the users table, this will make it search only the ~10k rows rather than 170k...

[eluser]Twisted1919[/eluser]
In the real application , i will never have so much users to activate , that's for sure .
But , as i said , i am doing all kind of tests before i move on to other section and if something takes more time than i think it should take , then i ask other people their opinion and see if i can do something about , but , in this case i think you are right and this is the fastest way .
Thank you for your time .

[eluser]Unknown[/eluser]
There are many details available here so please read care fully and take more information .............