Problemy z baza MySQL po przenosinach

[Paweł Kośka]

Witam,

Przenosiłem bazę danych MySQL z serwera 4.1 na 5.0
Kopie wykonywałem przez polecenie mysqldump --opt ...

No i jest pewien problem, coś mi się chyba rozjechało, a nie mogę dość co.

Mam takie zapytanie:
SELECT * FROM linki_comments as comments, linki_photos as photos, linki_users 
as users WHERE (photo_active = '1') AND (comments.photo_id = photos.photo_id)  
AND (comments.user_id = users.user_id) ORDER BY comment_id  DESC LIMIT 30;

No i teraz to zapytanie mam:
30 rows in set (3 min 27.92 sec)

Mam kopie plików ze starego serwera MySQL i jak je wrzucę 
do /var/lib/mysql/mysqldb/db/ to ta baza działa bardzo ładnie.
30 rows in set (0.00 sec)

W załączniku wynik z explain wyżej wspomnianego zapytania, na zlej i dobrej 
bazie. Ktoś może podpowiedzieć jak naprawić tą bazę?

-- 
Pozdrawiam,
Paweł
-------------- następna część ---------
 mysql> use zla
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> explain SELECT * FROM linki_comments as comments, linki_photos as photos, linki_users as users WHERE (photo_active = '1') AND (comments.photo_id = photos.photo_id)  AND (comments.user_id = users.user_id) ORDER BY comment_id  DESC LIMIT 30;
+----+-------------+----------+--------+------------------+----------+---------+----------------------+-------+----------------------------------------------+
| id | select_type | table    | type   | possible_keys    | key      | key_len | ref                  | rows  | Extra                                        |
+----+-------------+----------+--------+------------------+----------+---------+----------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | photos   | ALL    | PRIMARY          | NULL     | NULL    | NULL                 | 23753 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | comments | ref    | photo_id,user_id | photo_id | 3       | zla.photos.photo_id  |    28 |                                              |
|  1 | SIMPLE      | users    | eq_ref | PRIMARY          | PRIMARY  | 3       | zla.comments.user_id |     1 | Using where                                  |
+----+-------------+----------+--------+------------------+----------+---------+----------------------+-------+----------------------------------------------+
3 rows in set (0.00 sec)

mysql> use dobra
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> explain SELECT * FROM linki_comments as comments, linki_photos as photos, linki_users as users WHERE (photo_active = '1') AND (comments.photo_id = photos.photo_id)  AND (comments.user_id = users.user_id) ORDER BY comment_id  DESC LIMIT 30;
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+--------+-------------+
| id | select_type | table    | type   | possible_keys | key     | key_len | ref                     | rows   | Extra       |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+--------+-------------+
|  1 | SIMPLE      | comments | index  | photo_id      | PRIMARY | 3       | NULL                    | 736746 |             |
|  1 | SIMPLE      | users    | eq_ref | PRIMARY       | PRIMARY | 3       | dobra.comments.user_id  |      1 | Using where |
|  1 | SIMPLE      | photos   | eq_ref | PRIMARY       | PRIMARY | 3       | dobra.comments.photo_id |      1 | Using where |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+--------+-------------+
3 rows in set (0.00 sec)