3 queries to improve!!!

Hello,

SELECT `id`, `user_id`, `data` FROM `sys_sessions` WHERE `id`='z8aQ/TKJyCcDDVvfBqG=t!e&!uzaiwny' LIMIT 1;
# Time: 101130 sec

SELECT /*!40001 SQL_NO_CACHE */ * FROM `sys_banners_shows`;
# Time: 101130 sec

SELECT
`Profiles`.* , if(`DateLastNav` > SUBDATE(NOW(), INTERVAL 1 MINUTE ), 1, 0) AS `is_online`
FROM
`Profiles`
LEFT JOIN
`sys_profile_rating`
ON
`Profiles`.`ID` = `sys_profile_rating`.`pr_id`
WHERE
`Profiles`.`Status` = 'Active' and (`Profiles`.`Couple` = 0 or `Profiles`.`Couple` > `Profiles`.`ID`)

ORDER BY
`sys_profile_rating`.`pr_rating_sum` DESC
LIMIT 290, 5;
# Time: 101130 sec

Are you saying that these queries are taking you 101130 seconds to run? (# Time: 101130 sec)

You do realize that is about 28 hours right?

Exactly in a dedicated server with less than 10 users online :D

It's slow queries log saying that not me, seriously so many queries need to be improved. The big number of count queries in dolphin make it really heavy when there is a big number of users.

Hmm.. Im on a Shared Hosting with a whole lot more users than that and I do not see any issues with queries myself.

Check out:

http://www.boonex.com/unity/blog/entry/Speed_Increase_Part_2_Web_Server_Configuration_

And read AlexT's suggestions.

May be there is problems with my server i really don't know but as you see these queries take long time to finish more than 1 day.

You might want to see how many records are in your sys_sessions table.  You might be surprised.

More than 256 ko. And what is the solution?????

What's a 'ko' ?

It's the size of the table : > 256 kilo octet

Its a Knock Out... LOL .. sorry just messing

Check number of records in sys_banners_shows and sys_sessions tables, if the number of records is too high >100K then these tables are not pruning, you need to check your cronjobs.

Refer to this changeset regarding 3rd query optimization:

http://www.boonex.com/trac/dolphin/changeset/14711

Yes it's more than 100K:

Here is the size:

sys_banners_shows: 13.8 Mo

and

sys_sessions: > 256 Ko

How to check cronjobs ?

i make this changes : http://www.boonex.com/trac/dolphin/changeset/14711

It's a serious problem.

Make sure that this changeset is applied:
http://www.boonex.com/trac/dolphin/changeset/14533

Also compare your file with latest one from Dolphin 7.0.4:
inc/classes/BxDolCronCmd.php

If pruning is working, then you should receive email like this every day:

-----------------------

- Membership expiration letters -
Send membership expire letters: 50 letters

- Temporary files check -
Total temp files: 17555
Deleted temp files: 5920

- Database cleaning -
Deleted profiles:
Deleted virtual kisses: 0
Deleted messages: 0
Deleted sessions: 8071

-----------------------