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ł
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)
_______________________________________________ pld-users-pl mailing list pld-users-pl@lists.pld-linux.org http://lists.pld-linux.org/mailman/listinfo/pld-users-pl