Avatar billede milo Nybegynder
10. januar 2011 - 09:48 Der er 2 kommentarer

Hjælp til optimering af mailbox sql

Hej alle.

Jeg driver et site der bl.a. har en postkassefunktion, hvortil jeg har brug for lidt hjælp til optimering, da querien tager alt for mange ressourcer og en explain viser at den langt fra er optimal. Der er  i øjeblikket knap en halv million poster.

Først en oversigt over de relevante tabeller der indgår i querien.

CREATE TABLE `sl_mail_participation` (
  `parent_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `deleted` int(1) default '0',
  `is_read` int(1) default '0',
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `sl_mail` (
  `mail_id` int(7) NOT NULL auto_increment,
  `parent_id` int(7) default NULL,
  `receiver_user_id` int(6) NOT NULL default '0',
  `sender_user_id` int(6) NOT NULL default '0',
  `mail_text` text NOT NULL,
  `mail_time` int(12) NOT NULL default '0',
  PRIMARY KEY  (`mail_id`),
  KEY `mail_inbox` (`mail_id`,`parent_id`)
) ENGINE=MyISAM AUTO_INCREMENT=482392 DEFAULT CHARSET=latin1;


Her er et screenshot af min EXPLAIN, eksekveret i sequel pro: http://img839.imageshack.us/img839/6780/screenshot20110110at941.png
Som det ses er det ikke optimalt at den løber hele sl_user igennem samt at den kigger i så mange rækker i sl_mail_participation, som indeholder knap en halv million poster.

Selve querien er følgende (id'et 33 der bliver brugt i eksemplet, er blot et eksempel på en tilfældig bruger):
SELECT
            p.*,
            pt.deleted, pt.is_read,
            m.mail_text, m.sender_user_id,
            u.user_username, photo_path, user_gender, u.user_id, user_birthdate, user_geo_area, user_postcode
        FROM
            sl_mail_participation AS pt
        INNER JOIN
            sl_mail_parent AS p ON
            pt.parent_id = p.parent_id
        INNER JOIN
            sl_mail AS m ON
            p.parent_last_mail_id = m.mail_id
        INNER JOIN
            sl_user AS u ON
            (p.parent_sender_id = u.user_id OR p.parent_receiver_id = u.user_id)
        LEFT JOIN
            sl_photo AS ph ON
            u.photo_id = ph.photo_id
        WHERE
            pt.user_id = '33' AND
            (p.parent_count > 0 OR p.parent_sender_id != '33') AND
            pt.deleted = 0 AND
            u.user_id  != '33'
        ORDER BY
            m.mail_time DESC
        LIMIT
            0,10

Querien kan også ses her: http://pastebin.com/sKFH1xQ4

Nogle der har et bud på hvordan den query kunne ændres og blive mere effektiv? (Læs: undgå at få mysql til at sluge cpu) :-)
Avatar billede AnyFellow Mester
10. januar 2011 - 10:00 #1
Hvis du ikke får andre bud kan jeg måske byde ind med noget, selvom jeg absolut ikke er mysql haj.

Når man skal bruge data fra flere tabeller, er det ikke altid en fordel at holde dette i en enkelt forespørgsel.

Så vidt jeg har forstået opbygges der en midlertidig tabel i hukommelsen når en forespørgsel udføres på tværs af tabeller, og denne tabel kan hurtig blive meget stor og tung.

Ved at opdele forespørgslen i flere bider, er det ofte muligt at hente en hel del, både hvad angår hastighed og også ressourceforbrug.
Avatar billede milo Nybegynder
10. januar 2011 - 13:21 #2
Det er også en mulighed, jeg er dog overbevist at denne kan løses i en enkelt query, uden at blive tung :-)
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester