>Description:
this last times, I've noticed that some queries take a long time (about
10 sec).
These queries where generated from a CGI script, using perl DBI and I
was used to get the results in less than 1 sec.
I've run isamchk on all the tables, sorting index, sorting rows.
I've check for errors with -r and -o options.
After that, the problem remains the same.
I've done a mysqldump of the database, and than create a new one from
the dump.
The queries works fine (less than 1 sec) on the new database.
I've done isamchk -e *.ISM on each database, and there is no diff
Here is the description of the tables used by the query :
#
# Table structure for table 'valeur_variables'
#
CREATE TABLE valeur_variables (
site mediumint(9) DEFAULT '0' NOT NULL,
i_num int(11) DEFAULT '0' NOT NULL,
v_num tinyint(4),
dt datetime,
valeur char(30),
KEY site (site,i_num),
KEY i_num (i_num)
);
#
# Table structure for table 'instances'
#
CREATE TABLE instances (
site mediumint(9) DEFAULT '0' NOT NULL,
f_type smallint(6),
l_num smallint(6) DEFAULT '0' NOT NULL,
inst_fonc tinyint(4),
num int(11) DEFAULT '0' NOT NULL,
KEY site (site,num,l_num),
UNIQUE site_2 (site,num),
KEY l_num (l_num)
);
Here is the query :
select dayofmonth(VV.dt), hour(VV.dt), VV.valeur
from valeur_variables VV, instances I
where I.num = VV.i_num AND I.f_type= 201 AND VV.v_num = 0
AND VV.dt>=\"1999-3-01 01:00:00\" AND VV.dt<\"1999-4-01
01:00:00\"
AND VV.site = I.site AND I.site = 3 ORDER BY VV.dt ASC
I've done an explain select and a show index from valeur_variables on
each
database.
the old database ( the slow one) gives :
+-------+--------+---------------+--------+---------+--------------+-------+------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+-------+--------+---------------+--------+---------+--------------+-------+------------+
| VV | ref | site,i_num | site | 3 | ??? |
69383 | where used |
| I | eq_ref | site,site_2 | site_2 | 7 | ???,VV.i_num |
1 | where used |
+-------+--------+---------------+--------+---------+--------------+-------+------------+
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| valeur_variables | 1 | site | 1 | site
| A | 34 | NULL |
| valeur_variables | 1 | site | 2 | i_num
| A | 34 | NULL |
| valeur_variables | 1 | i_num | 1 | i_num
| A | 25 | NULL |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
the new database ( the fast one) gives :
+-------+------+---------------+--------+---------+-----------+-------+------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+---------------+--------+---------+-----------+-------+------------+
| I | ref | site,site_2 | site_2 | 3 | ??? | 1 |
where used |
| VV | ref | site,i_num | site | 7 | ???,I.num | 46256 |
where used |
+-------+------+---------------+--------+---------+-----------+-------+------------+
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| valeur_variables | 1 | site | 1 | site
| A | NULL | NULL |
| valeur_variables | 1 | site | 2 | i_num
| A | NULL | NULL |
| valeur_variables | 1 | i_num | 1 | i_num
| A | NULL | NULL |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
I've done an isamchk -r -q valeur_variables on the new database and
after
I get the same result than the old database (and query goes slow)
Does isamchk -r do any kind of sorting ?
>How-To-Repeat:
>Fix:
>Submitter-Id: <submitter ID>
>Originator: Red Hat Linux User
>Organization:
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis: Database corruption (or maybe Index order problem)
>Severity:
>Priority:
>Category: mysql
>Class:
>Release: mysql-3.22.21 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 7.11 Distrib 3.22.21, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty
Server version 3.22.21
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 2 hours 52 min 57 sec
Threads: 1 Questions: 185 Slow queries: 2 Opens: 90 Flush tables:
12 Open tables: 2
>Environment:
System: Linux yeti2.SOBEL 2.0.35 #5 Wed Sep 16 18:36:26 MEST 1998 i686
unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.7.2.3/specs
gcc version 2.7.2.3
Compilation info: CC='egcs' CFLAGS='-O6 -fomit-frame-pointer
-mpentium' CXX='egcs' CXXFLAGS='-O6
-fomit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
Configure command: ./configure --enable-shared --enable-assembler
--disable-shared --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc
--datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info
--includedir=/usr/include --mandir=/usr/man
Perl: This is perl, version 5.004_04 built for i386-linux
