#Macos querious 2 enum how to
#Macos querious 2 enum plus

One row of Ips represents one 'item' by specifying the starting IP address and the 'owner'. In addition to the two-table schema, there are a set of Stored Routines to encapsulate the necessary code. The second table has 'PRIMARY KEY(owner)'.

#Macos querious 2 enum how to

This discussion centers around `Ips` and how to efficiently map IP(s) to/from owner(s). In the reference implementations `owner` is an id used to JOIN the two tables. The second table contains other infomation about the 'owner' of each 'item'. The first table (`Ips` in the reference implementations) is carefully designed to be optimal for all the basic operations needed. The inconvenience of JOINing to the second table is small compared to the performance gains. The interesting work is in the Ips, not the second table, so I focus on it. The IPv6 parameters are 32-digit hex because it was the simpler that BINARY(16) or IPv5 for a reference implementation.The IPv4 parameters could have been dotted quads I chose to keep the reference implemetation simpler instead.The routine parameters for a 'block' could have be start of this block and start of next block.That one address could have been the 'end' address.These were alternative designs they seemed to be no better, and possibly worse: This is crucial to the design and its performance: One SQL statement is a DELETE if hits as many rows as are needed for the block.2-7 SQL statements, hitting the clustered PRIMARY KEY for the rows containing and immediately adjacent to the block.The record containing this will be 'adjacent' to the other record. For start of block: One single-row fetch using the PRIMARY KEY.Since InnoDB is used, and the PRIMARY KEY (clustered) is used, most operations hit only 1 block.įinding the 'block' where a given IP address lives: In a large table, 'counting the disk hits' is the important part of performance. The solution here can usually get the desired information by fetching a single row, or a small number of rows. The instinctive solution often leads to scanning half the table to do just about anything, such as finding the item containing an 'address'. The solution builds a table to take advantage of that, then uses Stored Routines to get around the clumsiness imposed by it. I will present a solution that enforces the fact that items cannot have overlapping ranges. The indexes fail to lead to optimal functioning because the database does not understand that the ranges are non-overlapping.

You play with the indexes, but find nothing that works well. Once you get a large set of items, performance degrades.

Your queries involve a WHERE clause that compares for being between the start and end values.

#Macos querious 2 enum plus

So, instinctively, you create a table with start and end of the range, plus info about the item. You have pairs of start and end values one 'item' belongs to each such 'range'. These could be IP addresses, datetimes (show times for a single station), zipcodes, etc. Your data includes a large set of non-overlapping 'ranges'.