Small DB optimization

Posted: Thu May 14, 2009 7:49 pm

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

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

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.

Posted: Thu May 14, 2009 10:54 pm

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,

registered · Posted: Sun May 17, 2009 8:34 am

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).

Posted: Sun May 17, 2009 9:59 am

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.

Posted: Mon May 18, 2009 6:06 pm

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.

Posted: Sun Jul 26, 2009 9:46 pm

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` )

Posted: Sun Jul 26, 2009 10:52 pm

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.

Posted: Sun Jul 26, 2009 11:30 pm

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.

Posted: Mon Jul 27, 2009 7:47 am

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.