Re: Does partitioning make sense for this application?
Posted by:
Rick James
Date: October 28, 2012 06:09PM
> the query based on its parameters such that the equals is always first.
No -- the _index_ needs to be ordered that way. The order in the _WHERE_ clause does not matter.
> Maybe build an array for the query so that the elements of the query can be assembled in the best order.
Yes. Building an array is a good way to construct the WHERE clause.
Look at these two _strings_:
'10.21.21.0'
'10.21.3.0'
Which one will sort _first_? The first on, which has a "2" paired up with the "3" in the second one. That implies that
WHERE srcip BETWEEN '10.21.21.0' AND '10.21.44.0'
will _include_ '10.21.3.0', which is not what is meant by IP ranges.
> At this point I'm realizing I need to go back to the drawing board.
I'm afraid so.
> he claims his software will search 365 days in a few seconds
That could be -- IF
* The amount of data is small enough, and/or
* There is a good index for the query in question, and/or
* All the data fits in RAM.
> The most predominent queries would be for a range of src IPs and one dest IP,
INDEX(daddr, saddr)
> but they literally could be anything.
Can't do _everything_ fast.
> Show me all connections to a specific port over a specific timerange,
INDEX(port, stime)
> show me all connections to the following destination IP over x to y timerange
INDEX(daddr, stime)
Question: Are the typical queries for "recent" timeranges? If so, we can discuss partitioning on stime; it will help _some_ queries. (Well, cannot use stime as it stands, but we can work on that.)
How many rows will you be inserting per second?
Do you have RAID striping?
Subject
Views
Written By
Posted
3340
October 25, 2012 08:46PM
1987
October 26, 2012 07:41PM
2201
October 26, 2012 09:55PM
1553
October 27, 2012 09:42PM
1577
October 27, 2012 10:41PM
Re: Does partitioning make sense for this application?
1702
October 28, 2012 06:09PM
1561
October 28, 2012 08:58PM
1737
October 30, 2012 12:15PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.