Fastest Select

--0-18943544-1307451311=:45494
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi All,

We have very big table with few column contains nearly 10 million records, =
We need to tune this table for simple select statement where we check recor=
d exists in table or not and=A0 requirement is response time should be less=
than 10 million second for nearly 1000 concurrent requests, what is best w=
ay to tune this table, table is kind of very static and we load data weekly=
once only,=20
Which engine is good MyISAM or InnoDB with index ?
Will any partitioning on this table help?

--Anupam

--0-18943544-1307451311=:45494--

Re: Fastest Select

--90e6ba6e843854e35f04a51f49b6
Content-Type: text/plain; charset=ISO-8859-1

Hi Anupam,

how do you load data? is it naturally ordered in any way?

Claudio

2011/6/7 Anupam Karmarkar

> Hi All,
>
> We have very big table with few column contains nearly 10 million records,
> We need to tune this table for simple select statement where we check record
> exists in table or not and requirement is response time should be less than
> 10 million second for nearly 1000 concurrent requests, what is best way to
> tune this table, table is kind of very static and we load data weekly once
> only,
> Which engine is good MyISAM or InnoDB with index ?
> Will any partitioning on this table help?
>
> --Anupam
>



--
Claudio

--90e6ba6e843854e35f04a51f49b6--

Re: Fastest Select

----- Original Message -----
> From: "Claudio Nanni"
>
> how do you load data? is it naturally ordered in any way?

Also, what's the record structure, and what are the criteria you use to check if a record exists?

If you only need to know wether a given record exists, your best bet is probably going to be InnoDB with the lookup fields as primary key - assuming they're unique - as that will give you index lookup without additional data block fetch if you request no other fields than those in the index.

Caveats always apply, of course, so extra information may be useful.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Fastest Select

--0-1039766705-1307520295=:15500
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I am loading data using LOAD DATA as source is csv file.

My selection is very simple with like

select * from XYZ where key =3D 123;

for 1 million sample record
I created innodb table with key, to load data from csv it took nearly 1 and=
1/2 hour on modest PC
I created MyISAM table with key it took nearly 7 min to load data into tabl=
e,

I created MyISAM table with hash partition it took nearly 1 and 1/2 hour

with InooDB and Partition=A0 got descent result for select

Now if i have to load data feed of 10 million once in week i need to consid=
er loading time also

--Anupam


--- On Wed, 8/6/11, Johan De Meersman wrote:

From: Johan De Meersman
Subject: Re: Fastest Select
To: "Claudio Nanni"
Cc: mysql@lists.mysql.com, "Anupam Karmarkar"
Date: Wednesday, 8 June, 2011, 10:53 AM



----- Original Message -----
> From: "Claudio Nanni"
>=20
> how do you load data? is it naturally ordered in any way?

Also, what's the record structure, and what are the criteria you use to che=
ck if a record exists?

If you only need to know wether a given record exists, your best bet is pro=
bably going to be InnoDB with the lookup fields as primary key - assuming t=
hey're unique - as that will give you index lookup without additional data =
block fetch if you request no other fields than those in the index.

Caveats always apply, of course, so extra information may be useful.

--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?unsub=3Dsb_akarmarkar@y=
ahoo.com


--0-1039766705-1307520295=:15500--

Re: Fastest Select

----- Original Message -----
> From: "Anupam Karmarkar"
>
> 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org