Hello.
Very often InnoDB provides a better performance in
environment where a lot of concurrent SELECTs and
UPDATEs. Check if the problem still exists if you change
the engine of you tables from MyISAM to InnoDB.
M.E. Koch wrote:
> Hello:
>
> After haveing a look at the `show processlist`
> I realized that querys will be locked if there
> is an update or insert statement otherwise
> the `STATUS` is as regular "copying to temp",
> "sorting" etc. So that not all querys will be
> locked automatically. (But when checking there
> where lots of insert intos so that every query
> seemd to be locked)
>
> some select querys
>
> `SELECT kk2.k_id FROM katalog_katalog kk
> LEFT JOIN katalog_katalog kk1 ON k_id=kk1.kat_id
> LEFT JOIN katalog_katalog kk2 ON kk1.k_kat_id=kk2.kat_id
> WHERE kk.kat_id=34678 AND kk1.k_kategorie_id=56
> AND kk2.k_kategorie_id=24;
>
>
> An insert into/update looks like this:
>
> INSERT INTO katalog
> SET kategorie_id=36, titel="foo foo bar",
> artikel="bar bar bar foo"
>
> The Update gets an additonal `WHERE id=1234`
>
> about the create statements:
> Two of many tables hopefully give an insight
> //----- snip
>
> CREATE TABLE `katalog` (
> `id` int(11) NOT NULL auto_increment,
> `kategorie_id` int(11) NOT NULL default '0',
> `datum` datetime NOT NULL default '0000-00-00 00:00:00',
> `titel` varchar(200) collate latin1_german2_ci default NULL,
> `untertitel` text collate latin1_german2_ci,
> `einleitung` text collate latin1_german2_ci,
> `artikel` text collate latin1_german2_ci,
> `quelle` text collate latin1_german2_ci,
> `bild_id` int(11) default NULL,
> `txt1` text collate latin1_german2_ci,
> `txt2` text collate latin1_german2_ci,
> `txt3` text collate latin1_german2_ci,
> `txt4` text collate latin1_german2_ci,
> PRIMARY KEY (`id`),
> KEY `kategorie_id_idx` (`kategorie_id`),
> KEY `datum_idx` (`datum`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci PACK_KEYS=1
> ;
>
> and
>
>
> CREATE TABLE `katalog_katalog` (
> `kat_id` int(11) NOT NULL default '0',
> `kategorie_id` int(11) NOT NULL default '0',
> `k_id` int(11) NOT NULL default '0',
> `k_kategorie_id` int(11) NOT NULL default '0',
> KEY `kat_id_idx` (`kat_id`),
> KEY `kategorie_id_idx` (`kategorie_id`),
> KEY `k_id_idx` (`k_id`),
> KEY `k_kategorie_id_idx` (`k_kategorie_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
> PACK_KEYS=1;
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
