+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Your opinion about this query . (/showthread.php?tid=27556)
Your opinion about this query . - El Forum - 02-14-2010
[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,20i 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)Thx.
Your opinion about this query . - El Forum - 02-14-2010
[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.
Your opinion about this query . - El Forum - 02-14-2010
[eluser]Twisted1919[/eluser]
Thank you for the reply , the status column is indexed also , before that is true, i had a slower query .
Your opinion about this query . - El Forum - 02-14-2010
[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...
Your opinion about this query . - El Forum - 02-14-2010
[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 .
Your opinion about this query . - El Forum - 02-14-2010
[eluser]Unknown[/eluser]
There are many details available here so please read care fully and take more information .............