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
Thread
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