Indeks til sortering og henting av resultater

[RipZ-]

Hei,

 

Jeg har et problem med MySQL og indeksering. Det har seg slik at jeg har en tabell som skal sorteres ut i fra en kolonne som inneholder en dato. Datokolonnen er indeksert. Radene jeg ønsker å hente er fra felles kategori og har en kolonne med en tallverdi som sier hvilken kategori dette er. Denne kolonnen er også indeksert.

 

Problemet er at når jeg henter resultater vha "where cat = 1", så vil ikke MySQL bruke indeksen for dato til å sortere. I stedet bruker den filesort og temporary tables. Og det går mildt sagt treeegt.

 

Spørringen er ganske kompleks. Kategorikolonnen ligger egentlig i en annen tabell. Jeg vet MySQL kan få problemer med dette, så jeg har også prøvd å indeksere kolonner i "hovedtabellen" (der kolonnen dato ligger, som det skal sorteres ut i fra). Men selv når jeg henter rader basert på denne verdien, vil ikke MySQL bruke indeksen for dato til å sortere.

 

Så til nå ser jeg kun to løsninger, som begge har fordeler og ulemper.

 

1) Sortere dato vha indeks og lete etter riktig kategori uten indeks. Det som da vil skje er at MySQL vil starte på den nyeste datoen og søke etter rader til den finner så mange rader som inneholder riktig kategori gitt av limiten i spørringen. Dette går veldig, veldig raskt når det er lav limit og hvis det er kategorier som utgjør en stor prosentandel av databasen. Men når det er ikke fult så mange rader fra denne kategorien, særlig om MySQL må gå langt tilbake i tid i databasen, vil dette ta laaang tid.

 

2) Hente resultatene basert på indeksen for kategorien. Dette vil være raskt for de små kategoriene som ikke inneholder mange rader siden det går relativt raskt å sortere dem med filesort. Men de største kategoriene som inneholder mange tusen rader, vil ta laaang tid å sortere.

 

Nå bruker jeg metode 1 fordi jeg prioriterer de største kategoriene. Det jeg ønsker er en løsning som fungerer bra for både små og store kategorier. Metode 2) vil uansett blir dårligere og dårligere etter hvert som de minste kategoriene også får en del rader i seg.

 

Har lest en hel del, men finner ingen løsning. Noen som har hatt liknede problemer og funnet en smart løsning? Er det rett og slett umulig å hente rader basert på en indeks, og sortere resultatene basert på en annen indeks? Støtter PostgreeSQL dette?

 

Lesestoff;

http://dev.mysql.com/doc/refman/5.0/en/ord...timization.html

 

You use ORDER BY on non-consecutive parts of a key:

 

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

Dette ser ut som er årsaken til at MySQL ikke ønsker å bruke indeksen for datoen. Noen som har et forslag hvordan jeg kan løse dette på en relativt smart måte?Noen med en ide til workaround? Eller blir det beste å migrere til en annen database?

[kilogram]

Har du mulighet for å gi oss beskrivelser av tabellene (DESCRIBE tabellnavn), sånn at vi har litt meir å gå etter? Evt. heile spørringa? (Eller betre, om du hadde gidda, eit forenkla eksempel på det du prøver å gjere) Eg klarar i alle fall ikkje å sjå kvifor dette skjer uten å ha ei slik oversikt...

[RipZ-]

Skjønner at det kan være litt vanskelig å skjønne hva jeg vil fram til. Å legge frem alle tabellstrukturene mine her tror jeg ikke hjelper noe særlig da det er såpass mye urelevant data. Så jeg illustrerer det med et veldig simpelt eksempel i stedet.

 

Tre kolonner; `cat`, `date` og `tekst`

Kolonnene cat og date er indeksert.

 

Henter alle radene fra cat = 1 og sorterer ved å bruke datoen;

SELECT * from tabell where cat = 1 order by date desc

 

Det som skjer da, er at det blir brukt filesort til sorteringen.

 

 

Spørringen min kan f.eks se slik ut:

select d.link, d.date innhold from

parse p

left join news n on n.parse_id = p.id

left join data d on d.id = n.data_id

left join site s on s.id = p.site_id

where s.lang = 'en'

and p.cat_id = 1 order by d.date desc limit 30

 

Da vil det bli brukt filesort fordi MySQL ikke greier å bruke indeksen til date til å sortere.

 

Bilde fra explanation:

 

Dette er altså metode 2 som beskrevet i min første post, og illustrerer problemet med order by.

 

Skal jeg bruke indeksen til dato for sortering, gjør jeg om på rekkefølgen tabellene blir hentet inn i. Da skjer det som jeg forteller om i metode 1.

 

Spørring:

select d.link, d.date

FROM data d

LEFT JOIN news n ON n.data_id = d.id

LEFT JOIN parse p ON n.parse_id = p.id

LEFT JOIN side s ON s.id = p.side_id

where s.lang = 'en'

and p.kategori_id = 1 order by d.date desc limit 30

 

Explanation:

 

Som man kan se, brukes indeksen til order by, men antall mulige rader blir ALL, noe som ikke er optimalt.

 

Det jeg ønsker å gjøre:

-> hente rader basert på kategori

-> sortere radene etter dato

Endret 22. februar 2006 av RipZ-