MyISAM no table lock

Hi there.

I was reading last week (and of course, i can't find it now) something about
'nicifying' a query, so taht it doesn't lock the table...

How is this done? I've read so much stuff lately, that i can't find it for
the life of me, and google is not being my friend :(

Steve.




--
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: MyISAM no table lock

--0016e64806785b7b59047f181169
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Feb 8, 2010 at 3:37 PM, Steve Staples wrote:

> Hi there.
>
> I was reading last week (and of course, i can't find it now) something
> about
> 'nicifying' a query, so taht it doesn't lock the table...
>
> How is this done? I've read so much stuff lately, that i can't find it
> for
> the life of me, and google is not being my friend :(
>

Maybe Google feels that you haven't been a partiicularly good friend to
them, of late ? :-)

"Nicifying" a query is not something you can write a manual for, alas.

EXPLAIN your queries.

If you need little data, see if you can pad it to the index you use -
that'll prevent additional disk reads.

InnoDB will help your inserts not block.

Avoid full table scans. Avoid outer joins, they cause full table scans.

et cetera ad nauseam.




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

--0016e64806785b7b59047f181169--

RE: MyISAM no table lock

Wow... maybe i should have re-evaluated my php script that does all this...

It is VERY in-efficient, ever look back at your code/script and think...
WHAT WAS I THINKING? Well, this is one of those occasions...

Basically, what i was doing, was running though my freeradius radius
accounting table, looking for duplicate IP addresses, from if i didn't get a
stop packet, and then what I would do, is simulate a stop record to denote
that user isn't online anymore....

The way i was/am doing it, is so ridiculous, i mean... it works, but when
there are multiple inserts and queries going on at the same time, it was
locking the tables, preventing writes, and it was just being dumb.

I am re-writing it now, so it will be much faster :)

Thanks Johan ;)

On a side note, i am currently looking to switch to InnoDB tables now...

-----Original Message-----
From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan De
Meersman
Sent: February 8, 2010 10:01 AM
To: mysql@lists.mysql.com
Subject: Re: MyISAM no table lock

On Mon, Feb 8, 2010 at 3:37 PM, Steve Staples wrote:

> Hi there.
>
> I was reading last week (and of course, i can't find it now) something
> about
> 'nicifying' a query, so taht it doesn't lock the table...
>
> How is this done? I've read so much stuff lately, that i can't find it
> for
> the life of me, and google is not being my friend :(
>

Maybe Google feels that you haven't been a partiicularly good friend to
them, of late ? :-)

"Nicifying" a query is not something you can write a manual for, alas.

EXPLAIN your queries.

If you need little data, see if you can pad it to the index you use -
that'll prevent additional disk reads.

InnoDB will help your inserts not block.

Avoid full table scans. Avoid outer joins, they cause full table scans.

et cetera ad nauseam.




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

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.733 / Virus Database: 271.1.1/2650 - Release Date: 02/08/10
02:35:00


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