18k queries against 70k rows

[D]

18k queries against 70k rows

query:
select cn from geoip where [decimal IP] between start and end

table:
"start_ip","end_ip","start","end","cc","cn"
"11.0.0.0","12.1.52.63",184549376,201405503,"US"," United States"
"111.0.0.0","111.0.0.255",1862270976,1862271231,"K R","Korea"
"112.63.162.116","112.63.162.119",1883218548,18832 18551,"ES","Spain"

After the queries are cached -thank god for query caching- it takes less
than a minute. Before the queries are cached it takes upwards of half an
hour. I have the primary key set to the start and end columns. Any ideas for
speeding things up?

[Bill Karwin]

D wrote:

18k queries against 70k rows

query:
select cn from geoip where [decimal IP] between start and end

table:
"start_ip","end_ip","start","end","cc","cn"
"11.0.0.0","12.1.52.63",184549376,201405503,"US"," United States"
"111.0.0.0","111.0.0.255",1862270976,1862271231,"K R","Korea"
"112.63.162.116","112.63.162.119",1883218548,18832 18551,"ES","Spain"

After the queries are cached -thank god for query caching- it takes less
than a minute. Before the queries are cached it takes upwards of half an
hour. I have the primary key set to the start and end columns. Any ideas for
speeding things up?

Always when trying to improve performance, examine the query using
EXPLAIN to see if it's really using the indexes you intend it to use.
You might need to create another single-column index on 'end'.

Another idea might be to load your 18k decimal IP's into another table,
and do a join to geoip. For instance:

CREATE TEMPORARY TABLE tmpiplist (
decimalip VARCHAR(12) NOT NULL PRIMARY KEY
);
LOAD DATA INFILE 'iplist.txt' INTO TABLE tmpiplist;

SELECT i.decimalip, COALESCE(g.cn, 'unknown') AS cn
FROM tmpiplist AS i LEFT OUTER JOIN geoip AS g
ON i.decimalip BETWEEN g.start and g.end;

At least that way you'd be eliminating the overhead of parsing and
executing 18,000 SQL statements; you'd execute one query and get back
18,000 rows in the result set.

Regards,
Bill K.

[D]

thanks for responding

explain query:

explain select cn from geoip where 3734003077 between start and end

result:

"id","select_type","table","type","possible_keys", "key","key_len","ref","rows","Extra"
1,"SIMPLE","geoip","ALL","","",(null),"",70865,"Us ing where"

[Bill Karwin]

D wrote:

thanks for responding

explain query:

explain select cn from geoip where 3734003077 between start and end

result:

"id","select_type","table","type","possible_keys", "key","key_len","ref","rows","Extra"
1,"SIMPLE","geoip","ALL","","",(null),"",70865,"Us ing where"

Well, I had in mind that _you_ would do the analysis of what this means.
I suggested that you run the query with EXPLAIN so that _you_ could
interpret the results.

See http://dev.mysql.com/doc/mysql/en/explain.html

That page has some good examples of how to interpret the output of
EXPLAIN. (Hint: the EXPLAIN output above indicates that the query is
using no index.)

You should try defining an additional index on your 'end' column, and
using >= and <= instead of BETWEEN. The MySQL optimizer might not be
smart enough to use a compound index with BETWEEN.

Regards,
Bill K.

[D]

Yes, I read that page. Thanks for the additional tips.

[D]

I think I've made a bit of progress. I created one compound unique index
with start and end. The between statement indeed doesn't use the index for
my query.

explain select * from geoip where start<=1130773714 and end>=1130773714

1, 'SIMPLE', 'geoip', 'range', 'Index_1', 'Index_1', 4, '', 10747, 'Using
where'

[Bill Karwin]

D wrote:

I think I've made a bit of progress. I created one compound unique index
with start and end. The between statement indeed doesn't use the index for
my query.

explain select * from geoip where start<=1130773714 and end>=1130773714

1, 'SIMPLE', 'geoip', 'range', 'Index_1', 'Index_1', 4, '', 10747, 'Using
where'

So now you've got it down from scanning 70,865 rows to 10,747 rows.
That should improve the speed of the query somewhat.

I'm guessing that it's able to use the index to evaluate "start <= ...",
but not "end >= ..." (the usual problem with compound indexes: find
everyone named "Steve" in the telephone book).

Now try creating an additional index on end.

Regards,
Bill K.

[D]

I created two indexes and it did not use them. The compound index was the
only one it would use. The compound index is smaller than two independent
indexes.

I think the between statement deals better with constants, such as: col
between 1 and 10...not: foo between col1 and col2.