Regarding Update/select Query

[parwal.sandeep]

Hello grp!

i'm using INNODB tables which are using frequently .
if i fire a SELECT query which fetch major part of table it usually
take 10-20 seconds to complete.
in mean time if any UPDATE qry comes for a perticular row which is part
of SELECT qry i want to know that whether UPDATE will wait for
completing SELECT qry or not,
or it simply executing without bothering SELECT qry .

plz reply .

[Shawn Hamzee]

First of all a select query on an innodb table should not take 10-20 seconds
to complete. There's something wrong somewhere. You can turn the slow query
log on to find out what is holding you up. You can ready about the slow
query and how to turn it on at www.mysql.com

Secondly, innodb uses what is called multi versioning which enables every
query to see their own version of the data it is trying to access (where for
read and/or update). Locking is rare and if it is needed then innodb uses
row level locking which means as long as you're not updating what you're
reading, performance should not effected (no wait).

Hope this helps.
-s
On 7/11/06 07:09, in article
11**********************@b28g2000cwb.googlegroups. com,
"pa************@gmail.com" <pa************@gmail.comwrote:

Hello grp!

i'm using INNODB tables which are using frequently .
if i fire a SELECT query which fetch major part of table it usually
take 10-20 seconds to complete.
in mean time if any UPDATE qry comes for a perticular row which is part
of SELECT qry i want to know that whether UPDATE will wait for
completing SELECT qry or not,
or it simply executing without bothering SELECT qry .

plz reply .

[parwal.sandeep]

nope ! in some case seniario SELECT query takes major part of tables
...
and it takes more than 20 seconds . in b/w if we fire UPDATE qry
it also takes time to complete .
so i want to confirm that still innodb table has row level locking,
will SELECT query reads snapshot of DB without acquiring a read lock on
it and let UPDATE statment
do what row it want to update . or it will wait till SELECT qry
completes.
i dont know much about MySQL internal how it handle this queries . plz
help me !


Shawn Hamzee wrote:

First of all a select query on an innodb table should not take 10-20 seconds
to complete. There's something wrong somewhere. You can turn the slow query
log on to find out what is holding you up. You can ready about the slow
query and how to turn it on at www.mysql.com

Secondly, innodb uses what is called multi versioning which enables every
query to see their own version of the data it is trying to access (where for
read and/or update). Locking is rare and if it is needed then innodb uses
row level locking which means as long as you're not updating what you're
reading, performance should not effected (no wait).

Hope this helps.
-s
On 7/11/06 07:09, in article
11**********************@b28g2000cwb.googlegroups. com,
"pa************@gmail.com" <pa************@gmail.comwrote:

Hello grp!

i'm using INNODB tables which are using frequently .
if i fire a SELECT query which fetch major part of table it usually
take 10-20 seconds to complete.
in mean time if any UPDATE qry comes for a perticular row which is part
of SELECT qry i want to know that whether UPDATE will wait for
completing SELECT qry or not,
or it simply executing without bothering SELECT qry .

plz reply .

[Shawn Hamzee]

Yes, innodb utilizes row level locking. And also don't forget about multi
versioning like I said in the previous post.

Your updates like I said before should not take that long to return. You can
use background updating (I think you have to include some modifiers in your
sql statements) which will do the work behind the scenes; however, it
returns control to the calling program.
On 7/12/06 03:01, in article
11**********************@m73g2000cwd.googlegroups. com,
"pa************@gmail.com" <pa************@gmail.comwrote:

nope ! in some case seniario SELECT query takes major part of tables
..

and it takes more than 20 seconds . in b/w if we fire UPDATE qry
it also takes time to complete .
so i want to confirm that still innodb table has row level locking,
will SELECT query reads snapshot of DB without acquiring a read lock on
it and let UPDATE statment
do what row it want to update . or it will wait till SELECT qry
completes.
i dont know much about MySQL internal how it handle this queries . plz
help me !


Shawn Hamzee wrote:

>First of all a select query on an innodb table should not take 10-20 seconds
to complete. There's something wrong somewhere. You can turn the slow query
log on to find out what is holding you up. You can ready about the slow
query and how to turn it on at www.mysql.com

Secondly, innodb uses what is called multi versioning which enables every
query to see their own version of the data it is trying to access (where for
read and/or update). Locking is rare and if it is needed then innodb uses
row level locking which means as long as you're not updating what you're
reading, performance should not effected (no wait).

Hope this helps.
-s
On 7/11/06 07:09, in article
11**********************@b28g2000cwb.googlegroups. com,
"pa************@gmail.com" <pa************@gmail.comwrote:

>>Hello grp!

i'm using INNODB tables which are using frequently .
if i fire a SELECT query which fetch major part of table it usually
take 10-20 seconds to complete.
in mean time if any UPDATE qry comes for a perticular row which is part
of SELECT qry i want to know that whether UPDATE will wait for
completing SELECT qry or not,
or it simply executing without bothering SELECT qry .

plz reply .

[parwal.sandeep]

yes my query is multitable update query in normal senario it takes less
than 1 second .
since it update only one row from each table which binded with foreign
keys ..

some times it takes time up to 40 sec which is not feasible now i want
to know whether
the select queries are takes lock or not .. these queires create temp.
table by select query .. some times it contains lacs of row in some
where conditions ..

one more thing these create temp. table from select takes lock on it
or not !

and what is background update can u till me ..

thanx for replying my qry.

[Shawn Hamzee]

It's a way for mysql to allow return of control to the calling program while
it's processing the data. I am not that good to have memorized all the
details; however, you can read about it on MySQL.com site. I remember that
you just add a modifier to the update.
On 7/12/06 12:09, in article
11**********************@p79g2000cwp.googlegroups. com,
"pa************@gmail.com" <pa************@gmail.comwrote:

yes my query is multitable update query in normal senario it takes less
than 1 second .
since it update only one row from each table which binded with foreign
keys ..

some times it takes time up to 40 sec which is not feasible now i want
to know whether
the select queries are takes lock or not .. these queires create temp.
table by select query .. some times it contains lacs of row in some
where conditions ..

one more thing these create temp. table from select takes lock on it
or not !

and what is background update can u till me ..

thanx for replying my qry.