CODEWITHSUNDEEP
mysqlinnodbmyisam
Xavier_Ex
Xavier_Ex

Reputation: 8810

MySQL: Converting from MyISAM to InnoDB makes queries faster...?

I had a table with 3,000,000 rows which has IP ranges and their corresponding country, region(province) and city. The table was in MyISAM engine.

I wrote a script to fetch the geographic location for users by their IP and some of the queries took up to 17sec. I then converted the table to InnoDB and retried the querying process, some of the problematic queries known to be time consuming before has been improved in performance significantly, taking as short as 0.02 seconds. Although there still were queries took 2 or 3 seconds to complete, the over all time consumed by the look up on about 1000 IP address took around half the time as before when ran on MyISAM engine.

I did a search online comparing MyISAM and InnoDB, but all of the articles I read judged MyISAM being faster than InnoDB on SELECT queries. However, according to what I have seen this is not the case for my table. Does anyone has any theories why?

P.S, My table has no FK constraints, no PK, look up is done by comparing the IP with the 'ip_start' and 'ip_end' columns of each row. The row would match if the IP is within the range. Following is an example query that's in my script:

SELECT country, region, city FROM ip_location WHERE ip_start<=1177798832 AND ip_end>=1177798832 LIMIT 1;

Upvotes: 0

Views: 1283

Answers (2)

Brendan Long
Brendan Long

Reputation: 54312

I found a good article about InnoDB vs MyISAM that says this about performance:

This technique of clustered primary keys is one reason for the fairly large margin in the benchmarks between MyISAM and InnoDB when the query in question uses the primary key for a range. What is somewhat surprising, though, is that InnoDB still wins in the tests that use a secondary index. This is comforting as it shows that the performance of the engine is not just based on key clustering, a performance boost that is very query dependent.

InnoDB uses some other tricks with indexes as well. It can build "adaptive hash indexes" for frequent queries and does so when an entire table comes close to fitting in memory.[5] These hash indexes are quite a bit faster than the standard BTree index (when the table is in memory). Again, this is another significant performance improvement at the expense of memory usage.

Your table sounds fairly large, so I doubt it's being held entirely in memory, but it sounds like InnoDB handles indexes better than MyISAM anyway, plus since you're being so specific, it may be caching the result of the query.

Upvotes: 2

D Mac
D Mac

Reputation: 3809

Check the configuration of the two engines. It is possible that the config of your InnoDB engine is more efficient for your searches than the way you have MyISAM set up.

A key config values is innodb_buffer_pool_size, which is where InnoDB caches data (and indexes, but you don't have any indexes).

Upvotes: 2

Related Questions