Geek Answers Handbook

Why is my MySQL query running fast when I do it this way and slowly when I do it?

I need to track users who are online on my site, so I can show the online icon next to avatars of users who are online.

The first step in detecting this is to track when the user was last seen, so I make an UPDATE request every time the user requests a page:

UPDATE `users`
SET `last_seen` = CURRENT_TIMESTAMP
WHERE `user_id` = '$user_id'

Now, right after that, I execute another request that receives all users of my site according to a certain criterion, so I can display them on the main page:

SELECT *,
(ACOS(SIN(0.7103989219783) * SIN(RADIANS(users.latitude)) + COS(0.7103989219783) * COS(RADIANS(users.latitude)) * COS(RADIANS(users.longitude) - -1.2894447135174)) * 6371) AS SearchRadius
FROM `users`
INNER JOIN `profiles` ON (
    users.user_id = profiles.user_id
)
WHERE (users.latitude > 38.904216788163 AND users.latitude < 42.501503211837)
AND (users.longitude > -76.252301637251 AND users.longitude < -71.507178362749)
AND (ACOS(SIN(0.7103989219783) * SIN(RADIANS(users.latitude)) + COS(0.7103989219783) * COS(RADIANS(users.latitude)) * COS(RADIANS(users.longitude) - -1.2894447135174)) * 6371) < 200 AND users.sex = '1' AND users.seeking = '2' AND users.user_id != '1' AND users.account_status = '1' LIMIT 0, 10

Do not pay attention to the craziness of this query, basically this is what I choose from the same table usersthat I previously updated.

2 , , :

1st query: 0.0392 seconds
2nd query: 1.5396 seconds

.

, - :

UPDATE `online_users`
SET `last_seen` = CURRENT_TIMESTAMP
WHERE `user_id` = '$user_id'

:

1st query: 0.0411 seconds
2nd query: 0.0008 seconds

!

? , - , , .

+5
3

SELECT * last_seen, MySQL . , last_seen.

( online_users .)

+1

, SELECT , , . . , .

0

querying the innoDB table is slower than querying on MyISAM. more rows in the table make the query slower. incorrect indexing in the table makes the query slower, check with the EXPLAIN command.

-4
source

More articles:


All Articles