>>>>> "Jean" == Jean Claude LAFFITTE <jc.laffitte@stripped>
> writes:
>> Description:
Jean> this last times, I've noticed that some queries take a long time (about
Jean> 10 sec).
Jean> These queries where generated from a CGI script, using perl DBI and I
Jean> was used to get the results in less than 1 sec.
Jean> I've run isamchk on all the tables, sorting index, sorting rows.
Jean> I've check for errors with -r and -o options.
Jean> After that, the problem remains the same.
Jean> I've done a mysqldump of the database, and than create a new one from
Jean> the dump.
Jean> The queries works fine (less than 1 sec) on the new database.
Jean> I've done isamchk -e *.ISM on each database, and there is no diff
Jean> Here is the description of the tables used by the query :
Jean> #
Jean> # Table structure for table 'valeur_variables'
Jean> #
Jean> CREATE TABLE valeur_variables (
Jean> site mediumint(9) DEFAULT '0' NOT NULL,
Jean> i_num int(11) DEFAULT '0' NOT NULL,
Jean> v_num tinyint(4),
Jean> dt datetime,
Jean> valeur char(30),
Jean> KEY site (site,i_num),
Jean> KEY i_num (i_num)
Jean> );
Jean> #
Jean> # Table structure for table 'instances'
Jean> #
Jean> CREATE TABLE instances (
Jean> site mediumint(9) DEFAULT '0' NOT NULL,
Jean> f_type smallint(6),
Jean> l_num smallint(6) DEFAULT '0' NOT NULL,
Jean> inst_fonc tinyint(4),
Jean> num int(11) DEFAULT '0' NOT NULL,
Jean> KEY site (site,num,l_num),
Jean> UNIQUE site_2 (site,num),
Jean> KEY l_num (l_num)
Jean> );
You should remove the KEY 'site'; This will not help in any circumstances!
A new key 'valeur_varibles.dt' may also help a bit!
Jean> Here is the query :
Jean> select dayofmonth(VV.dt), hour(VV.dt), VV.valeur
Jean> from valeur_variables VV, instances I
Jean> where I.num = VV.i_num AND I.f_type= 201 AND VV.v_num = 0
Jean> AND VV.dt>=\"1999-3-01 01:00:00\" AND VV.dt<\"1999-4-01
Jean> 01:00:00\"
Jean> AND VV.site = I.site AND I.site = 3 ORDER BY VV.dt ASC
Jean> I've done an explain select and a show index from valeur_variables on
Jean> each
Jean> database.
Jean> the old database ( the slow one) gives :
Jean>
> +-------+--------+---------------+--------+---------+--------------+-------+------------+
Jean> | table | type | possible_keys | key | key_len | ref |
Jean> rows | Extra |
Jean>
> +-------+--------+---------------+--------+---------+--------------+-------+------------+
Jean> | VV | ref | site,i_num | site | 3 | ??? |
Jean> 69383 | where used |
Jean> | I | eq_ref | site,site_2 | site_2 | 7 | ???,VV.i_num |
Jean> 1 | where used |
Jean>
> +-------+--------+---------------+--------+---------+--------------+-------+------------+
>
How many entries do you have with 'WW.site = 3' ? Is '69383' about right?
<cut>
Jean> the new database ( the fast one) gives :
Jean>
> +-------+------+---------------+--------+---------+-----------+-------+------------+
Jean> | table | type | possible_keys | key | key_len | ref | rows |
Jean> Extra |
Jean>
> +-------+------+---------------+--------+---------+-----------+-------+------------+
Jean> | I | ref | site,site_2 | site_2 | 3 | ??? | 1 |
Jean> where used |
Jean> | VV | ref | site,i_num | site | 7 | ???,I.num | 46256 |
Jean> where used |
Jean>
> +-------+------+---------------+--------+---------+-----------+-------+------------+
The big difference is in this case that the table order is different.
You can force the table order in MySQL with 'STRAIGHT_JOIN'
<cut>
Jean> I've done an isamchk -r -q valeur_variables on the new database and
Jean> after
Jean> I get the same result than the old database (and query goes slow)
Jean> Does isamchk -r do any kind of sorting ?
The problem with your queries are that if you only examine keys, both
join versions should resolve the query in about the same time.
(MySQL uses only key statistics to find out in which order it should
join tables)
isamchk -a (and -r) will update the 'mean' key statistics, which
usually helps to resolve queries faster.
There is however one thing I find strange.
Can you mail me the output of:
explain select straight_join dayofmonth(VV.dt), hour(VV.dt), VV.valeur
from instances I, valeur_variables VV,
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
after you have run 'isamchk -r' or 'isamchk -a'
(I would like to check the values in the 'rows' column).
At any rate, you can get the above query faster by adding more keys or using
STRAIGHT_JOIN.
Regards,
Monty
