Long queries time

Regular Joined: Mar 11, 2005 Posts: 67

I just found out that some quries in the NukeSentinel are loading quite long. Here is the query witch took 4 seconds to load:

# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 82543
SELECT `c2c` FROM `nuke_nsnst_ip2country` WHERE `ip_lo`<='3557959793' AND `ip_hi`>='3557959793' LIMIT 0,1;

Does anyone have the same problem?

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Hmmm. You might want to find another host. My results:
Query took 0.7775 sec

Worker Joined: Aug 26, 2007 Posts: 236

For me it took 0.24 sec.

Posted: Fri Sep 12, 2008 12:14 am

I should add that your results will vary due to server load and processes running at the time but 4 seconds is WAY off the curve.

Posted: Fri Sep 12, 2008 12:15 am

Well if I run this query manualy query took 0.3170 sec... looks like this is happening only when there is rush on my site.

Guess this isn't the problem in table.

Thx!

Posted: Fri Sep 12, 2008 12:18 am

hehe raven, just 1 min ahead of me Smile

Why does this query actualy do? Is it for tracking visitors?

Posted: Fri Sep 12, 2008 12:38 am

It's used to track IP countries in a couple of scripts.

Try profiling it under phpMyAdmin to see where it's hanging. My last try showed this:

Code:

Status     Time


(initialization)    0.000023


Opening tables    0.000037


System lock    0.000009


Table lock    0.000015


init    0.000011


optimizing    0.000009


statistics    0.000013


preparing    0.000012


executing    0.0000699


Sending data    0.000027


end    0.000009


query end    0.000009


freeing items    0.000014


closing tables    0.000008


removing tmp table    0.000023


closing tables    0.000012


logging slow query    0.000009





id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra


1    SIMPLE    nuke_nsnst_ip2country    range    PRIMARY    PRIMARY    4    NULL    82969    Using where

Posted: Fri Sep 12, 2008 4:43 am

I searched for profiling in phpMyAdmin but I can't find it Sad

Posted: Fri Sep 12, 2008 7:34 am

Profiling was added in v2.11.0.0 (2007-08-21)

In phpMyAdmin
1. Select your database
2. Select the SQL tab
3. Submit your query
4. The result screen should have a Profiling checkbox

5. Check it
6. Resubmit your query

Posted: Fri Sep 12, 2008 9:40 am

Thx for explaining me raven, but I have phpMyAdmin version 2.11.6 but i don't see any profiling. I'll check in which version profiling is integrated.

Posted: Fri Sep 12, 2008 10:26 am

It was added in v2.11.0.0 (2007-08-21). You may have to activate it; I don't remember.

Posted: Fri Sep 12, 2008 10:52 am

I'm checking in config.default.php file in includes libraries of phpmyadmin and don't know if this code enables profiling. Are you so kind and check your settings?

These are mine:

Code:

/*******************************************************************************


 * Developers ONLY!


 * To use the following, please install the DBG extension from http://dd.cron.ru/dbg/


 *


 * @global array $cfg['DBG']


 */


$cfg['DBG'] = array();





/**


 * Make the DBG stuff available


 *


 * @global boolean $cfg['DBG']['enable']


 */


$cfg['DBG']['enable'] = false;