Ravens PHP Scripts: Forums
 

 

View next topic
View previous topic
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> NukeSentinel™ v2.6.x
Author Message
emilacosta
New Member
New Member


Joined: Apr 07, 2009
Posts: 8
Location: Miami, FL

PostPosted: Thu May 14, 2009 7:49 pm Reply with quote

Hi,

I found that the query:

Code:
SELECT `c2c` FROM `nuke_nsnst_ip2country` WHERE `ip_lo`<='3702851360' AND `ip_hi`>='3702851360' LIMIT 0,1


Was showing up as a query that was not using index and only where and it was sometimes mark as an slow query.

To fix this issue I added the following index:

Code:
ALTER TABLE `nuke_nsnst_ip2country` ADD INDEX (`ip_lo`,`ip_hi`,`c2c`)


The new index show the query now using where and index and its speed also improved tremendously from 1.2+ to 0.3+ seconds on my server.

Enjoy. Wink

_________________
Emil Acosta

"In the computer business you’re either a one or a zero and I am determined never to be zero." 
View user's profile Send private message
Raven
Site Admin/Owner


Joined: Aug 27, 2002
Posts: 17086

PostPosted: Thu May 14, 2009 10:35 pm Reply with quote

Not sure where you're seeing this, but if you installed the tables correctly the index is already applied. Here's how it works as a new installation.

The first tables that gets installed are the CORE tables. One of the tables that gets created but not populated is the ip2country table. Here's the code from core.sql:
Code:
DROP TABLE IF EXISTS $prefix.`_nsnst_ip2country`;

CREATE TABLE IF NOT EXISTS $prefix.`_nsnst_ip2country` ( `ip_lo` int(10) unsigned NOT NULL default '0', `ip_hi` int(10) unsigned NOT NULL default '0', `date` int(20) NOT NULL default '0', `c2c` char(2) NOT NULL default '', PRIMARY KEY  (`ip_lo`,`ip_hi`), KEY `c2c` (`c2c`), KEY `date` (`date`)) TYPE=MyISAM;


So at that point the schema is in place. Then when the ip2c tables are being populated, the first instructions in ip2country1.sql that are executed are:
Code:
TRUNCATE TABLE $prefix.`_nsnst_ip2country`;

LOCK TABLES $prefix.`_nsnst_ip2country` WRITE;
ALTER TABLE $prefix.`_nsnst_ip2country` DISABLE KEYS;


We disable the keys to allow for faster loading of the table.

Then, once all the inserts are completed, the very last instructions in ip2country16.sql are:
Code:
ALTER TABLE $prefix.`_nsnst_ip2country` ENABLE KEYS;

UNLOCK TABLES;


At that point the table gets indexed and optimized. If your ip2country table schema does not reflect the keys then I would have to suggest that you may have missed a step.
 
View user's profile Send private message
emilacosta
PostPosted: Thu May 14, 2009 10:54 pm Reply with quote

Hi Raven,

I saw the three index of the initial installation:

index 1 (ip_lo, ip_hi)
index 2 (c2c)
index 3 (date)

but if you notice on the table the "cardinality" of each index is not equal to each other meaning that both index 1 and 2 will be called when looking for c2c on the ip_lo and ip_hi range.

By adding the new index you unify the "cardinality" and there is no need to scan two indexes because on one you have the full result while still on a range.

If you use the EXPLAIN function of MySQL on the above query without the index I mention you will get that the query is only using "USING WHERE" on the extra column, and if you repeat this step after adding the index you will see that the query then how "USING WHERE, USING INDEX"

Thank you,
 
montego
Site Admin


Joined: Aug 29, 2004
Posts: 9456
Location: Arizona

PostPosted: Sun May 17, 2009 8:34 am Reply with quote

emilacosta, something else is going on here as the index that you propose does not make sense for the WHERE clause of the SQL statement you are referencing. An index is used on WHERE clause elements, NOT on the data field being returned. The first PK index should be taking care of the SQL you reference.

Index 2 is only used when a search is using the c2c field in the WHERE clause (and most likely ONLY when just that one field is being used in the WHERE clause).

_________________
Only registered users can see links on this board! Get registered or login!
Only registered users can see links on this board! Get registered or login! 
View user's profile Send private message Visit poster's website
Raven
PostPosted: Sun May 17, 2009 9:59 am Reply with quote

I just "verified" emilacosta's findings.

Code:


id    select_type    table                   type       possible_keys    key       key_len    ref    rows       Extra
1       SIMPLE          nuke_nsnst_ip2country    range    PRIMARY          PRIMARY    4          NULL    86355    Using where
Showing rows 0 - 0 (1 total, Query took 0.6333 sec)

PROFILING:
Status                 Time
starting             0.000060
Opening tables          0.000076
System lock          0.000006
Table lock             0.000014
init                0.000016
optimizing             0.000009
statistics             0.000023
preparing             0.000015
executing             0.000066
Sending data          0.000029
end                0.000006
end                0.000006
query end             0.000005
freeing items          0.000010
closing tables           0.000006
removing tmp table       0.000019
closing tables               0.000008
logging slow query        0.000005
cleaning up          0.000006


ALTER TABLE `nuke_nsnst_ip2country` ADD INDEX (`ip_lo`,`ip_hi`,`c2c`)

id    select_type    table                   type       possible_keys    key       key_len    ref    rows       Extra
1       SIMPLE          nuke_nsnst_ip2country    range    PRIMARY,ip_lo    ip_lo    4          NULL    86237    Using where; Using index
Showing rows 0 - 0 (1 total, Query took 0.0890 sec)

PROFILING:
Status                 Time
starting             0.000025
Opening tables               0.000028
System lock          0.000006
Table lock             0.000010
init                0.000008
optimizing             0.000007
statistics             0.000009
preparing             0.000008
executing             0.000052
Sending data          0.000018
end                0.000007
end                0.000005
query end             0.000007
freeing items          0.000009
closing tables           0.000006
removing tmp table       0.000016
closing tables           0.000008
logging slow query        0.000006
cleaning up          0.000006


Montego, I am as surprised as you are. I would not have expected the compound index to matter and I'm still researching this. The one thing I note is that the where clause is unusual:

WHERE
`ip_lo`<='3702851360'
AND
`ip_hi`>='3702851360';


Note that the only value that can satisfy the AND is '3702851360' which means the where clause is actually looking for a single value but the LE and GE is having to be processed which is greater overhead. Also I'm wondering if the MySQL query cache is distorting things (not being cleared)? I say that because in the Profiling the starting and opening tables doesn't take near as long so that indicates that what should be standard for any query isn't and that would mean that the savings is actually in the overhead because the cache is being used.
 
montego
PostPosted: Mon May 18, 2009 6:06 pm Reply with quote

Still surprised. I am no DBA, but I certainly wouldn't be expecting that additional index to help...

However, `ip_lo` and `ip_hi` are different fields, so the query is actually looking for a valid range.
 
Raven
PostPosted: Sun Jul 26, 2009 9:46 pm Reply with quote

After much testing I am thinking the IP2C table only needs 2 indexes. I don't even think the `date` index is ever used but I will leave it for now. Comments?

PRIMARY KEY ( `ip_lo` , `ip_hi` , `c2c` )
KEY ( `date` )
 
emilacosta
PostPosted: Sun Jul 26, 2009 10:52 pm Reply with quote

Just make sure no other query is using, sorting or calling data from that field. If you use the print out query method I mention in the PM you can check easily all the queries that use that table on a regular installation.
 
emilacosta
PostPosted: Sun Jul 26, 2009 11:30 pm Reply with quote

Hi,

This is the other query I can't get to use an index and it is only using where:

Code:
SELECT * FROM `nuke_nsnst_ip2country` WHERE (`ip_lo`<='1276259140' AND `ip_hi`>='1276259140') AND `c2c`='01'


I was thinking that the issue is that I am calling all the values (*), are all the values needed in that case?

Some findings:

Without any extra index the current query is "using where", but it scan 76 rows, the key length is 2, the index is c2c and the ref is a constant.

If you add the index:

Code:
 ALTER TABLE `nuke_nsnst_ip2country` ADD INDEX ( `c2c` , `ip_lo` , `ip_hi` )  


The same query only scan 12 rows, but the key length is 6, the ref is null and the type of query change from const to range. Also it is a little bit faster too.
 
Raven
PostPosted: Mon Jul 27, 2009 7:47 am Reply with quote

MySQL determines which s the fastest way to get its results. Using an index is not always the fastest. And, nano seconds are not always accurate Smile.

What your query is really stating is
SELECT *
FROM `nuke_nsnst_ip2country`
WHERE `ip_lo` =1276259140
AND ip_lo = ip_hi
AND c2c = '01'

MySQL returns
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const table...

Since MySQL can determine with a simple index scan that WHERE (`ip_lo`<='1276259140' AND `ip_hi`>='1276259140') doesn't exist it has no need to do anything more.
 
Display posts from previous:       
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> NukeSentinel™ v2.6.x

View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum


Powered by phpBB © 2001-2007 phpBB Group
All times are GMT - 6 Hours
 
Forums ©