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