vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   What This slow Query ? (https://vborg.vbsupport.ru/showthread.php?t=224230)

Draving 09-29-2009 08:22 PM
What This slow Query ?
 
Hi, sorry my bad english

There is a very slow query in my database:

Code:
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 09-29-2009 08:26 PM
Which query ? You have listed 9 in that post, also they are cutoff.

Draving 09-29-2009 08:53 PM
These 2 Query:

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

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

--------------- Added [DATE]1254262396[/DATE] at [TIME]1254262396[/TIME] ---------------

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 09-29-2009 09:19 PM
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 09-29-2009 10:10 PM
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 09-29-2009 10:21 PM
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

PHP Code:
define('DISABLE_HOOKS', true); 

Draving 09-29-2009 10:24 PM
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 [DATE]1254268321[/DATE] at [TIME]1254268321[/TIME] ---------------

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 10-01-2009 03:31 PM
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?

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

--------------- Added [DATE]1254416433[/DATE] at [TIME]1254416433[/TIME] ---------------

I found it :D

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

Code:
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 :)


All times are GMT. The time now is 05:52 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01045 seconds
  • Memory Usage 1,734KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (5)bbcode_code_printable
  • (1)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (8)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete