Is MyISAM a bad choice for this kind of table?
----- Original Message ----- From: "Dathan Pattishall" <[EMAIL PROTECTED]>
To: "Matthew Simpson" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Friday, May 20, 2005 1:48 PM
Subject: RE: slow DELETE query
DELETE LOW_PRIORITY
But since your using myISAM the table will have to lock.
DVP ---- Dathan Vance Pattishall http://www.friendster.com
-----Original Message-----
From: Matthew Simpson [mailto:[EMAIL PROTECTED] Sent: Friday, May 20, 2005 11:22 AM
To: mysql@lists.mysql.com
Subject: slow DELETE query
I use Mysql to store call detail records from telephone calls. I have around 20 voice switches that send the call detail records in real time using INSERT statements.
I am having a problem where I need to delete "junk" call records that get generated [old call records, call records with no accountcode, etc.], but when I try to run the DELETE query, SQL grinds to a halt which causes my voice switches to halt because they can't run the INSERT queries. Is this because of table locking? An example delete query:
DELETE from cdr WHERE accountcode=''
Is there a way to make the DELETE query run at a lower priority and allow the INSERTs?
Here is the table description:
mysql> describe cdr;
+-------------+--------------+------+-----+-------------------
--+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------
--+-------+
| uniqueid | varchar(32) | | | | |
| userfield | varchar(255) | | | | |
| accountcode | varchar(20) | | MUL | | |
| src | varchar(80) | | MUL | | |
| dst | varchar(80) | | | | |
| dcontext | varchar(80) | | | | |
| clid | varchar(80) | | | | |
| channel | varchar(80) | | | | |
| dstchannel | varchar(80) | | | | |
| lastapp | varchar(80) | | | | |
| lastdata | varchar(80) | | | | |
| calldate | datetime | | | 0000-00-00 00:00:00 | |
| duration | int(11) | | | 0 | |
| billsec | int(11) | | | 0 | |
| disposition | varchar(45) | | | | |
| amaflags | int(11) | | | 0 | |
+-------------+--------------+------+-----+-------------------
--+-------+
type is MyISAM
indexes:
mysql> show index from cdr;
+-------+------------+----------+--------------+-------------+
-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | |
Cardinality | Sub_part | Packed | Comment |
+-------+------------+----------+--------------+-------------+
-----------+-------------+----------+--------+---------+
| cdr | 1 | cdr_idx | 1 | src | A | NULL | NULL | NULL | |
| cdr | 1 | cdr_idx | 2 | dst | A | NULL | NULL | NULL | |
| cdr | 1 | cdr_idx | 3 | calldate | A | NULL | NULL | NULL | |
| cdr | 1 | cdr_idx | 4 | accountcode | A | NULL | NULL | NULL | |
| cdr | 1 | i1 | 1 | accountcode | A | NULL | NULL | NULL | |
| cdr | 1 | i1 | 2 | calldate | A | NULL | NULL | NULL | |
+-------+------------+----------+--------------+-------------+
-----------+-------------+----------+--------+---------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
