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
