List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:June 8 2011 8:58am
Subject:Re: Fastest Select
View as plain text  
----- Original Message -----
> From: "Anupam Karmarkar" <sb_akarmarkar@stripped>
> select * from XYZ where key = 123;
> Now if i have to load data feed of 10 million once in week i need to
> consider loading time also

Yes, On InnoDB you can't disable the primary key, as the data is index-organized. The
reason why the primary key select is so fast, is also the reason why the data load is so
slow :-p

Now, if you really only need to check the existence of a single value, you can just import
that value from the csv, and ignore all other fields - that should speed up the data load
quite a bit.

I just did a load of 10 million integers into a MyISAM, an InnoDB and a Memory table with
just the integer field. Load times were respectively 35.18, 110.90 and 61.05 seconds from
a local file; on all three tables primary key select (with sql_no_cache even) was pretty
much instantaneous (0.01 seconds). I didn't test selects under parallel load, though.

Make sure your key cache can hold all your indices. That way, pure-index selects should
never go to disk once the index is cached.

You may also consider taking that particular functionality out of MySQL altogether: have a
look at Memcached, an in-memory key/value store. It's insanely efficient at single key
lookups, and has a lot less overhead than SQL for that purpose. It's typically used for
caching key/value pairs for webservers (like session variables and whatnot) but may be
well-suited for your purpose, too.

Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Fastest SelectAnupam Karmarkar7 Jun
  • Re: Fastest SelectClaudio Nanni7 Jun
    • Re: Fastest SelectJohan De Meersman8 Jun
      • Re: Fastest SelectAnupam Karmarkar8 Jun
        • Re: Fastest SelectJohan De Meersman8 Jun