MySQL Forums
Forum List  »  Partitioning

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Does partitioning make sense for this application?
1613
October 28, 2012 06:09PM


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.