Thanks for the answer,
Michael Widenius wrote:
> How many entries do you have with 'WW.site = 3' ? Is '69383' about right?
mysql yeti5gdb -e "select COUNT(*) from valeur_variables where site
=3" gives
+----------+
| COUNT(*) |
+----------+
| 1212868 |
+----------+
> 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).
>
before isamchk :
+-------+------+---------------+--------+---------+-----------+-------+------------+
| 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 |
+-------+------+---------------+--------+---------+-----------+-------+------------+
after isamchk -a or isamchk -r
+-------+------+---------------+--------+---------+-----------+-------+------------+
| 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 | 69383 |
where used |
+-------+------+---------------+--------+---------+-----------+-------+------------+
after isamchk the query (without explain) goes fast with straight_join,
slow without.
Jean Claude LAFFITTE.
