From: Jean Claude LAFFITTE
Date: April 29 1999 1:31pm
Subject: Re: Database corruption (or maybe Index order problem)
List-Archive: http://lists.mysql.com/mysql/2613
Message-Id: <37285F4D.7A2A35FF@wanadoo.fr>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.