Re: [Secureideas-base-devel] Optimizing maintenance query
Brought to you by:
secureideas,
sinukas
From: Michael S. <ms...@ma...> - 2008-08-10 19:04:37
|
On Sun, Aug 10, 2008 at 01:34:40PM -0500, Micah Gersten wrote: >The query I wrote requires looking at a smaller dataset, not a larger >one. the original query requires the join on every row. My query only >joins the unique values. No, your query may require running distinct across *all rows of acid_event* (assuming the absence of a myisam-specific index optimization), which is likely way more work than is strictly required to determine the intersection of ips in acid_ip_cache with non-null fqdns with src ips in acid_event. (Note that it is *not* necessary to look at every row to perform the join, only rows matching the selection criteria.) What you're doing is forcing a particular solution to the problem, rather than allowing the db engine's query planner to determine the best solution based on the actual data. I understand that you're doing this to take advantage of a nifty performance optimization in mysql, but you need to understand that this can reduce performance generically. (And, as mentioned in previous threads, may reduce performance even with mysql, given different table types.) Mike Stone |