Re: [Secureideas-base-devel] Optimizing maintenance query
Brought to you by:
secureideas,
sinukas
From: Micah G. <mi...@on...> - 2008-08-08 00:14:34
|
BTW, this fix makes login almost instantaneous as well. Before, it took about 30 seconds to update the cache before the main page would load even with statistics off. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Micah Gersten wrote: > Here's the patch. > > Thank you, > Micah Gersten > onShore Networks > Internal Developer > http://www.onshore.com > > > > Micah Gersten wrote: > >> I have the following query: >> SELECT COUNT(DISTINCT ip_src) FROM acid_event INNER JOIN acid_ip_cache >> ON ipc_ip = ip_src WHERE ipc_fqdn is not NULL >> >> Which takes 79 seconds on my db with 6 million rows. >> >> I changed it to: >> SELECT COUNT( * ) >> FROM acid_ip_cache >> WHERE ipc_ip >> IN ( >> >> SELECT DISTINCT acid_event.ip_src >> FROM acid_event >> ) >> AND ipc_fqdn IS NOT NULL >> >> >> and it takes under 2 seconds. >> >> Can I make the changes to the code and submit a patch? I'm using MySQL, >> but the changes should work on most DB systems that support subqueries. >> >> The reason for the decrease is that it doesn't need to join all 6 >> million rows, instead it returns the 5000 or so distinct IPs and then >> looks in the acid_ip_cache table which only has about 50k rows. >> >> >> >> ------------------------------------------------------------------------ >> >> ------------------------------------------------------------------------- >> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge >> Build the coolest Linux based applications with Moblin SDK & win great prizes >> Grand prize is a trip for two to an Open Source event anywhere in the world >> http://moblin-contest.org/redirect.php?banner_id=100&url=/ >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> Secureideas-base-devel mailing list >> Sec...@li... >> https://lists.sourceforge.net/lists/listinfo/secureideas-base-devel |