faster queries in sentinel.php

Worker Joined: Nov 10, 2004 Posts: 110

Hi Raven,
In /includes/sentinel.php
you use:

Code:

$checkrow = $db->sql_fetchrow($db->sql_query("CHECK TABLE ".$prefix."_nsnst_ip2country"));

to see is the IP2Country module is installed.
Also it checks if the table has been proberly closed.

I discovered that this is a rather slow query. Faster would be:

Code:

$checkrow = $db->sql_fetchrow($db->sql_query("CHECK TABLE ".$prefix."_nsnst_ip2country QUICK"));

In this way not the entire the table would be checked, enough to see if it exists.

But even faster would be to use:

Code:

$checkrow = $db->sql_fetchrow($db->sql_query("SHOW TABLES LIKE '".$prefix."_nsnst_ip2country%'"));

Or is it that you want to build in a complete table check on purpose?
What do you think of these ideas?

Cheers,
Richard

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

Quote:

Or is it that you want to build in a complete table check on purpose?

I don't believe so. I'll pass this by Bob Marion (the chief developer) and make sure. Thanks so much for the suggestion!

registered · Posted: Sat Nov 20, 2004 3:45 pm

I'll update the codes to use the fastest one. We're just checking to see if the table is there as if it's not then IP2C ins't installed Smile

Posted: Sat Nov 20, 2004 6:12 pm

Take the % (wildcard) of the end. Improves the chance that the index will be used.

Posted: Mon Nov 22, 2004 9:29 am

The 'slow' query is actually used in:
\admin\modules\sentinel\ABDBOptimize.php
\admin\modules\sentinel\ABDBRepair.php
\admin\modules\sentinel\ABDBStructure.php
\admin\modules\sentinel\ABSearchUsers.php
\admin\modules\sentinel\functions.php
\includes\sentinel.php

But of course the most important place to improve it would be in sentinel.php

Cheers,
Richard Smile

Posted: Mon Nov 22, 2004 10:55 am

further on (to reduce querytime of sentinel.php)

The get_ip() is called by the proxy blocker as well as in the 'Basic Value Setup'
This results in the in_range() function to be called twice and thus the query:

Code:

SELECT * FROM nuke_nsnst_reserved_ranges WHERE ip_lo<='1234567890' AND ip_hi>='1234567890'

to be run twice.

I'm not that much into sentinel.php to oversee the concequenses of the following change right now, but it should be possible to move the proxy-section to after the 'basic value'-section
and change:

Code:

$proxy0 = get_ip();

to

Code:

$proxy0 = $remote;

If this is too drastically, another change would undoubtly be possible Smile

Cheers!
Richard Wink

Posted: Mon Nov 22, 2004 3:43 pm

It just doesn't stop. But as long as I'm in the flow I guess I'll keep on posting on this. (Maybe this topic should move to enhancement requests)

Anyway, I found another way to reduce the ammount of queries that sentinel.php executes (this time quite drastically).

in sentinel.php
[FIND]

Code:

// AUTHOR Protection

[BEFORE ADD:]

Code:

$result = $db->sql_query("SELECT * FROM ".$prefix."_nsnst_blockers");


$num_rows = $db->sql_numrows($result);


for ($i = 0; $i < $num_rows; $i++) {


$array[$i] = mysql_fetch_array($result);


}


$arrayother = $array[0];


$arrayunion = $array[1];


$arrayclike = $array[2];


$arrayharvester = $array[3];


$arrayscript   = $array[4];


$arrayauthor   = $array[5];


$arrayreferer = $array[6];


$arrayfilter   = $array[7];


$arrayrequest = $array[8];


$arraystring   = $array[9];


$arrayadmin = $array[10];

aditionally, change the folowing line in the different blocker-sections from:

Code:

$blocker_row = abget_blocker("author");

to:

Code:

$blocker_row = $arrayauthor;

(for example, change:

Code:

$blocker_row = abget_blocker("admin");

to:

Code:

$blocker_row = $arrayadmin;

The identifier-names $arrayadmin,$arrayauthors, etcetera are only used for ease of use here. The code could also be changed to:

Code:

$blocker_row = $array[10];

instead of:

Code:

$blocker_row = $arrayadmin;

.

The result of these changes is that instead of the folowing queries:

Quote:

SELECT * FROM nuke_nsnst_blockers WHERE block_name='author'
SELECT * FROM nuke_nsnst_blockers WHERE block_name='admin'
SELECT * FROM nuke_nsnst_blockers WHERE block_name='union'
SELECT * FROM nuke_nsnst_blockers WHERE block_name='clike'
SELECT * FROM nuke_nsnst_blockers WHERE block_name='filter'
SELECT * FROM nuke_nsnst_blockers WHERE block_name='script'
SELECT * FROM nuke_nsnst_blockers WHERE block_name='referer'
SELECT * FROM nuke_nsnst_blockers WHERE block_name='harvester'
SELECT * FROM nuke_nsnst_blockers WHERE block_name='string'
SELECT * FROM nuke_nsnst_blockers WHERE block_name='request'

only the folowing is executed:

Quote:

SELECT * FROM nuke_nsnst_blockers

Raven & Bob, I wonder if this is interesting for Sentinel or if there were explicit (security) reasons to code otherwise.

Cheers from a cloudy & wet Holland,
Richard Smile

Posted: Mon Nov 22, 2004 5:23 pm

Great ideas Smile

Posted: Mon Nov 22, 2004 7:40 pm

Would you please send me (email) your 'enhanced' sentinel.php file? I want to test your changes. Thanks!

Posted: Tue Nov 23, 2004 5:20 pm

Hi Raven,
Did you check my sentinel.php in you pm-box?
I wonder if you checked it out.

cheers,
Richard Smile

Posted: Tue Nov 23, 2004 5:55 pm

I am testing it as I have time Smile

Posted: Tue Nov 23, 2004 6:07 pm

you win