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