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