What This slow Query ?

[Draving]

Hi, sorry my bad english

There is a very slow query in my database:


Matar el proceso 68882 DB localhost DB Query 1627 Sending data SELECT user . * , usertextfield . * , userfield . * , user.userid, options, IF( displaygroupid =0, user.usergr
Matar el proceso 69228 DB localhost DB Query 1604 Sending data SELECT user . * , usertextfield . * , userfield . * , user.userid, options, IF( displaygroupid =0, user.usergr
Matar el proceso 69618 DB localhost DB Query 1577 Sending data SELECT user . * , usertextfield . * , userfield . * , user.userid, options, IF( displaygroupid =0, user.usergr
Matar el proceso 69979 DB localhost DB Query 1552 Sending data SELECT user . * , usertextfield . * , userfield . * , user.userid, options, IF( displaygroupid =0, user.usergr
Matar el proceso 71301 DB localhost DB Query 1319 Sending data SELECT user . * , usertextfield . * , userfield . * , user.userid, options, IF( displaygroupid =0, user.usergr
Matar el proceso 73978 DB localhost DB Query 885 Sending data SELECT SUM( IF( lastvisit >=1251665384, 1, 0 ) ) AS active, COUNT( * ) AS users, MAX( userid ) AS max
Matar el proceso 74715 DB localhost DB Query 755 Sending data SELECT SUM( IF( lastvisit >=1251665427, 1, 0 ) ) AS active, COUNT( * ) AS users, MAX( userid ) AS max
Matar el proceso 74976 DB localhost DB Query 727 Sending data SELECT SUM( IF( lastvisit >=1251665441, 1, 0 ) ) AS active, COUNT( * ) AS users, MAX( userid ) AS max
Matar el proceso 75078 DB localhost DB Query 718 Sending data SELECT SUM( IF( lastvisit >=1251665448, 1, 0 ) ) AS active, COUNT( * ) AS users, MAX( userid ) AS max



How can I optimize or eliminate this Query?

Thanks

[Paul M]

Which query ? You have listed 9 in that post, also they are cutoff.

[Draving]

These 2 Query:


SELECT user . * , usertextfield . * , userfield . * , user.userid, options, IF( displaygroupid =0, user.usergr


and


SELECT SUM( IF( lastvisit >=1251665427, 1, 0 ) ) AS active, COUNT( * ) AS users, MAX( userid ) AS max



Thanks

--------------- Added 1254262396 at 1254262396 ---------------

My Forum is Great has over 1 million users, over 3 Million Post and 3k users online ...

Mysql get started to go wrong for 7 days and always works well in 2 years, someone can help me?

[Lynne]

What version of vb are you using? I did a search in the files for "user.*, usertextfield.*" and got one hit and that is not the query. That query looks like an added query because it then has user.userid in the select statement and yet that field is already in the select statement. So, I'd say that one is from a modification.

The second qury looks to be from includes/functions_databuild.php - build_user_statistics. It should not be running often at all since the description of the function is "Save user count & newest user into template" unless you have a really busy forum with lots of new users.

[Draving]

Upgrade from 3.6.8 to latest version 3.8.4 this week to see if it solved the problem, but even this foot wrong.

I have many records per second, is there any way to optimize the second Query?

[Lynne]

Do you still get the problem if you disable your modifications?
Note: To temporarily disable the plugin system, edit config.php and add this line right under <?php

define('DISABLE_HOOKS', true);

[Draving]

If the problem is that I use HIDE, and I have many records of users per second ....

4500 + per day

Is there any way to optimize the registry?

--------------- Added 1254268321 at 1254268321 ---------------

I've stopped Register Vbulletin users and the problem was solved ...

There any way to optimize the registry for many endurance records per second?

[Draving]

The solution to the second query is going to:

VBulletin Options - Home of the forum and put 0 in the Cropping Members

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

The First Query is not yet that is, someone knows what is?

SELECT user . * , usertextfield . * , userfield . * , user.userid, options, IF( displaygroupid =0, user.usergr

Thanks

--------------- Added 1254416433 at 1254416433 ---------------

I found it :D

If anyone has the same problem, the solution to clear the query:

SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid

,avatar.avatarpath,NOT ISNULL(customavatar.userid) AS hascustomavatar,customavatar.dateline AS avatardateline, customavatar.width AS avwidth, customavatar.height AS avheight

, lastactivity AS lastvisittime


Is disabling the Members List :)