MySQL Forums
Forum List  »  Partitioning

Re: Should I partition?
Posted by: Rick James
Date: April 04, 2013 11:35PM

> if someone wants to do a "like" search for "10.0.0.%"

Turn that into
WHERE ipaddr BETWEEN INET_ATON('10.0.0.0') AND INET_ATON('10.0.0.255')
It would not take much PHP code to do that for the users. And, if there is an index on ipaddr, it would be reasonably efficient.

IPv6 is upon us! INT won't do for IP addresses any longer. Need BINARY(16).

> I read a few forums which basically stated that if you do this, compression might take more space (ung).

ARCHIVE -- probably about the same. Some INTs and maybe DATEs will compress a little.
InnoDB -- the way it works will probably lead to taking more, not less, space for data that is not very compressible.

> they will surprise you with some wonky query.

Yeah, they are the queens of wonky!

I think you will find that mostly they will want COUNTs over civilized time ranges. If so, then Summary Tables is your solution.

> your webpage you don't seem to think fondly of subpartitioning

True. But I am willing for you to prove me wrong.

> 20G worth

How many rows will that be? Perhaps 1000/second? 100/second is generally no problem. 1000/second is a problem if you have a "random" index. We can discuss that further when you present a tentative SHOW CREATE TABLE.

Options: ReplyQuote


Subject
Views
Written By
Posted
2337
March 19, 2013 02:57PM
1302
March 21, 2013 08:49PM
1277
April 03, 2013 10:08AM
Re: Should I partition?
1186
April 04, 2013 11:35PM


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.