Avatar billede jakobdo Ekspert
09. oktober 2012 - 14:09 Der er 21 kommentarer og
2 løsninger

Optimer sql udtræk / fetch af data

Hej,
jeg har følgende kode: (simplificeret)

$items = array();
$id = 12;
if($stmt = $mysqli->prepare("SELECT name,content,sort FROM items WHERE parent_id = ? ORDER BY sort")){
  $stmt->bind_param("i",$id);
  $stmt->execute();
  $stmt->bind_result($name,$content,$sort);
  while ($stmt->fetch()){
    $items[] = array("name" => $name, "content" => $content, "sort" => $sort);
  }
}

Hvis jeg laver en EXPLAIN på den, siger den:

id = 1
select_type = SIMPLE
table    = items
type = ref
possible_keys = parent_id
key = parent_id
key_len = 4
ref = const
rows = 154
Extra = Using where; Using filesort

Jeg har forsøgt at lave noget "timing" på udtrækket og det tager omkring 2,7sekunder.
Kan jeg ikke gøre det hurtigere?

Håber nogle forstår hvad jeg mener. :o)

PS: Jeg har indeks (BTREE) på ID, parent_id og sort
PSS: Og jeg erkender gerne, jeg er ikke mysql ekspert, så famler lidt i blinde. :o)
Avatar billede arne_v Ekspert
09. oktober 2012 - 14:32 #1
Grundliggende mener jeg at din SQL er korrekt og at MySQL optimizer ikke er snu nok!

Men hvis din kode ikke skal porteres til en anden database (og med mysqli kald, saa er den ikke portabel anyway), saa proev og drop index paa parent_id og sort - og lav et nyt index paa (parent_id,sort).
Avatar billede jakobdo Ekspert
09. oktober 2012 - 14:39 #2
Har nu lavet et index på (sort,parent_id) og udtrækket tager stadig 2-3 sekunder.

Kan det virkelig passe det skal tage 2-3 sekunder at udtrække ~150 rækker?
Avatar billede arne_v Ekspert
09. oktober 2012 - 14:45 #3
hvordan ser explain ud efter det nye index?
Avatar billede arne_v Ekspert
09. oktober 2012 - 14:45 #4
nej!
Avatar billede jakobdo Ekspert
09. oktober 2012 - 14:53 #5
Så siger explain dette:

id = 1
select_type = SIMPLE
table = items
type = ALL
possible_keys = NULL
key = NULL
key_len = NULL
ref = NULL
rows = 1783
Extra = Using where; Using filesort
Avatar billede arne_v Ekspert
09. oktober 2012 - 15:03 #6
hm

index (parent_id,sort) != index (sort,parent_id)
Avatar billede jakobdo Ekspert
09. oktober 2012 - 15:25 #7
I see...
Så siger den:

id = 1
select_type= SIMPLE
table = items
type = ref
possible_keys = sort
key = sort
key_len = 4
ref = const
rows = 155
Extra = Using where
Avatar billede jakobdo Ekspert
09. oktober 2012 - 15:29 #8
Ved ikke om dette kan være en detalje:

name = varchar(255)
content = mediumtext

Kan det være noget af årsagen?
Avatar billede jakobdo Ekspert
09. oktober 2012 - 15:36 #9
Tror sgu jeg fandt synderen. :o)

Nogle af rækkerne, har en tom "content" dette tjekkede jeg med:

if(strlen($content)>0)

uden den linje, tager querien kun 0.01 sekund.
Så takker for din indsats, men lukker med dette som svaret.
Håber det er ok arne_v. :o)
Avatar billede jakobdo Ekspert
09. oktober 2012 - 15:37 #10
Vil dog gerne dele, hvis du vil have lidt point for indsatsen ?
Avatar billede arne_v Ekspert
09. oktober 2012 - 15:42 #11
if(strlen($content)>0)

boer ikke kunne tage saa lang ekstra tid
Avatar billede arne_v Ekspert
09. oktober 2012 - 15:44 #12
jeg synes at det ville vaere sjovt at sammenligne

index paa parent_id og index paa sort men ikke index paa (parent_id,sort)

index paa (parent_id,sort) og ikk eindex paa parent_id og ikke index paa sort

for din nye PHP kode
Avatar billede arne_v Ekspert
09. oktober 2012 - 15:44 #13
hvis der ikke er nogen forskel, saa tager du bare selv point
Avatar billede jakobdo Ekspert
09. oktober 2012 - 15:53 #14
Hej Arne,
når min første løsning var ikke korrekt.
Det var min: $stmt->bind_result($content,$sort,$parent_id);
Som fejlede og dermed gav den "gode" tid.

Men jeg kan se forskellen er:

SELECT content,sort,parent_id FROM items = 2,7sek.

SELECT sort,parent_id FROM items = 0sek.

Så hvordan udtrækker man mediumtext i "stor" stil uden at gå ned på performance?
Avatar billede arne_v Ekspert
09. oktober 2012 - 16:01 #15
Jeg tror stadig at det er vaerd at undgaa "Using filesort
"
Avatar billede arne_v Ekspert
09. oktober 2012 - 16:03 #16
Hvor stort er content faktisk?

Det kan vaere stoerre end 64 KB?
Avatar billede jakobdo Ekspert
10. oktober 2012 - 09:14 #17
Hvordan ser jeg størrelsen af en mediumtext ?
Altså jeg startede med at have content som text, der blev det i nogle tilfælde "clipped" derfor måtte jeg hæve den et niveau.
Avatar billede arne_v Ekspert
10. oktober 2012 - 15:24 #18
TEXT kan klare 64 KB.

MEDIUMTEXT kan klare 16 MB.
Avatar billede jakobdo Ekspert
10. oktober 2012 - 15:46 #19
Og TEXT var desværre ikke nok, så derfor måtte jeg op i MEDIUMTEXT.
Jeg troede du mente hvor store tekster jeg rent faktisk havde. :o)
Avatar billede arne_v Ekspert
11. oktober 2012 - 03:04 #20
Skal du faktisk vise saa store data eller kan du selecte SUBSTRING(content,1,200) eller tilsvarende?
Avatar billede jakobdo Ekspert
15. oktober 2012 - 09:29 #21
Jeg skal vise det hele.
Jeg lavede dog en lille "optimering".

Systemet var bygget sådan op:

AGENDA
  AGENDA_PUNKT
    AGENDA_UNDERPUNKT

I udtrækket vil der altid være en AGENDA.
1-X AGENDA_PUNKTER
1-X AGENDA_UNDERPUNKTER

Da jeg gerne ville kunne styre opsætningen af hver underpunkt 100% i "runtime", gemte jeg dem i en række hver.

Nu har jeg så samlet alle underpunkter og gemmer dem formatteret i AGENDA_PUNKT, så dermed et AGENDA_UNDERPUNKTER tabellen droppet og dermed mister jeg lidt formatteringsmuligheder.

Men jeg gik fra ~3 sekunder -> 0.5 sekund.
Så det er ok her og nu.

Tak for dine inputs Arne.
Avatar billede jakobdo Ekspert
24. oktober 2012 - 00:23 #22
Arne: Smid svar, så kan vi dele. Du kom med gode inputs, men jeg tror ikke der var en løsning, så lukker med mit "hack" ovenover.
Avatar billede arne_v Ekspert
25. oktober 2012 - 22:54 #23
ok
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