performance - When does a slow MySQL query on a given connection affect other connections? -

i think have basic understanding of this, hoping can give me more details interested in learning more database performance.

lets have large database, many millions of entries, database supports many connections. doing simple queries on database slow there's data. i'm trying understand when query on given connection starts have direct effect on performance of queries running on other connections.

if 1 connection locks elements, understand that hold queries running other connections need elements . example doing:

select update 

will lock selecting.

what happens when simple like:

select count(*) mytable 

lets have table billion rows running count going take time (running on innodb). affect queries running on other connections?

what if select large amount of data using select , join, like:

select * mytable1 join mytable2 on mytable1.id = mytable2.id; 

does having join lock other queries?

i'm finding hard know queries have direct effect on performance of queries running on other connections.

thanks

there different angles:

• row locking: shouldn't happen if tune architecture, should forget it
• real performances issues , bottleneck. in our case, collateral effects.

about second point, problem divided in 3 areas:

• disk reads
• memory usage (buffer)
• cpu usage.

about disk reads: more data (in bytes) retrieve, more harddrive going busy , slowdown other activity using it. reduce size of selected rows avoid disk overhead.

about memory usage: mysql manages internal buffer, can stuck in situations. don't know enough give proper answer, know definetly should keep eye on.

about cpu usage: cpu busy when

• has calculate (joins, preparing statements, arithmetics...)
• has peripheric stuff: moving bytes disk memory instance. optimize queries reduce cpu overhead. (sounds silly but, well, turns out problem anyway...)

so, when know when there's collateral effect? profiling hardware... how profile?

• absolute profiling: use show innodb status or show profile useful informations main mysql harddrive, cpu , memory watches.
• relative profiling: use favorite os profiler. under windows xp instance, can use great perfmon.exe , watch private bytes , virtual bytes of mysql process. relative, because afterall if query time consuming on computer, might not on nasa system...

hope helps, regards.