Great! Thanks for the benchmarks!

D’oh! Yes, you’re completely right. I wasn’t reading sql statement correctly, and there was a typo in it (using >= instead of <=) in my test cases. (Note to self, do not comment until after coffee has been had.)

I did do some benchmarking on some stuff here.

The winner is definitely the “SELECT * (SELECT * .. WHERE my_ip <= ip_begin..) AS T …” at least on my box configuration. – approx 5K queries/sec (single client)

The worse is of course, the “ip BETWEEN start and end” at a whole whopping 3 queries/sec, and that’s with the optimizer claiming to use start ip index.

My version using classb was able to crank out 2K queries/sec. Not bad, but only half as good as the SELECT(SELECT *) AS T.. one.

I disagree. The ‘229512’ rows reported in first version’s subquery does not really hold. That’s what’s peculiar about EXPLAIN: it does not (maybe can not?) take the LIMIT into consideration. I assure you (by testing it and seeing the performance, that is) that the number reported is not actually the number of rows to be scanned in this plan. The real number is 1.

Please notice that this is regardless of the subquery: see the EXPLAIN plan discussion within my post.

The second step involved is really nothing, since it only needs to scan a table of a single row. So no real impact on memory or CPU here.

Whereas in the second version you’ve provided, and as I explained in my post, 229512 may actually be a reasonable estimation for number of rows to scan.

Regards

Unfortunately, that expression is worse. The sub-select will grab ALL rows above the starting IP, create a derived table, and then scan through that derived table which *does not* have an index. The lower the IP number is, the more rows that will have been dumped into the derived table.

Under MySQL, derived tables do not inherit any indexes from the original table, or tables, it was created from. It would be awesome if it did, but it doesn’t.

The MySQL optimizer ain’t too happy about it:

explain SELECT * FROM (select * from geo_ip_blocks WHERE 412306898 >= ip_begin ORDER BY ip_begin limit 1) AS T where 412306898 <= ip_end\G

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: geo_ip_blocks
type: range
possible_keys: ip_begin,index_on_ip_begin,classb_begin
key: ip_begin
key_len: 4
ref: NULL
rows: 229512
Extra: Using where

Now look at the following SQL which is what we all naturally want to try first when attacking this problem:

explain SELECT * FROM geo_ip_blocks WHERE 412306898 BETWEEN ip_begin AND ip_end\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: geo_ip_blocks
type: range
possible_keys: ip_begin,ip_end,index_on_ip_begin,classb_begin
key: ip_begin
key_len: 4
ref: NULL
rows: 229512
Extra: Using where

In both cases the estimated number of rows to scan is the same: 229512. The first SQL statement is going to be worse since there’s a second step involved with having to create a derived table.

The second SQL only has one action involved, which is exactly the same as the one in the first SQL.

This seems right to me, and was what I meant… 🙂

SELECT * FROM (
SELECT * FROM regions_ip_range
WHERE my_ip >= start_ip
ORDER BY start_ip LIMIT 1
) AS t
WHERE my_ip <= end_ip;

I haven’t tested this performance-wise but it seems right theoretically… Either way, your spot on both of your “wrong ways”. 🙂