WN - problem med Mysql Slow query

WN (https://www.wn.se/forum/index.php)

- Serversidans teknologier (https://www.wn.se/forum/forumdisplay.php?f=4)

- - problem med Mysql Slow query (https://www.wn.se/forum/showthread.php?t=1041594)

zilveer

2010-04-18 21:09

problem med Mysql Slow query

Hejsan allihopa,
jag har ett "litet" problem men som påverkar hela databasen pga det lilla problemet så påverkas hela servern och blir seg pga alla mysql slow queries.

SQL raden som anropas ser ur på följande vis:

Kod:

SELECT * FROM users ORDER BY id DESC;

Detta anrop skapar problem för mig, kör jag explain på den så får jag fram följande data:

Kod:

id         select_type         table         type         possible_keys         key         key_len         ref         rows         Extra

1         SIMPLE         users         index         NULL         PRIMARY         4         NULL         1938

Denna logg sparas i mysql_slow_quries filen. Förstår dock inte, det är en InnoDB databas.

Raden ser ut som följande i mysql slow queries filen:

Kod:

# Time: 100418 15:27:51

# Query_time: 2  Lock_time: 0  Rows_sent: 1801  Rows_examined: 1801

SELECT * FROM users ORDER BY id DESC;

Vad kan detta bero på? Tacksam för all hjälp och vägledning.
mvh

fabian

2010-04-18 21:15

Har du index på id?

Är du säker på att du måste köra igenom alla 1801 rader? Skall du verkligen använda alla, eller funkar det kanske lika bra med en limit på 50?

zilveer

2010-04-18 21:22

fabian:
tack för snabb svar.. glömde skriva att id är PRIMARY. Och anledningen till att jag vill ha ut alla är att jag ska köra paging på det. Hur kommer det sig att kolumnen possible_keys samt ref är NULL när jag kör explain på anropet?

Weaver

2010-04-18 21:35

Nu vet jag inte hur hela din tabell ser ut men... Anledningen till att possible_keys är NULL kan vara för att MySQL anser det vara snabbare att läsa direkt från tabellen istället för att använda sig av index.

Om du skall implementera paging bör du använda LIMIT (offset, antal) i din query som fabian rådde dig till. Du skall inte dela upp resultatet i PHP eller ASP.

Tänk också på att bara begära data som du behöver. * är sällan optimalt.

Magnus_A

2010-04-18 21:38

Även om du kör paging behöver du väl inte hämta alla.
Det räcker väl med totalantalet och den aktuella sidan?

Tror inte mysql använder index när du hämtar alla, finns inget att selektera på.

Hur uppför sig frågan utan order by och med ORDER BY NULL?

zilveer

2010-04-18 21:41

Weaver: tack för svar:)

Saken är den att jag använder ett paging script där jag först måste räkna ut antalet rader.. den i sin tur tar mysql raden och sätter en limit på den.

tabellen har lite information om användare, som t.ex. namn, mail, datum samt lite till info.

men om mysql anser att det går snabbare att läsa så hela tabellen istället för att använda index förstår jag inte för som det ser ut nu så körs denna mysql anrop varje gång någon laddar sidan. har man 10 000 besökare så blir det ju 10 000 slow queries..

Ingen som kan hjälpa mig med att lösa detta?

zilveer

2010-04-18 21:44

Citat:

Ursprungligen postat av Magnus_A (Inlägg 20351765)

EXPLAIN SELECT * FROM users

Kod:

id         select_type         table         type         possible_keys         key         key_len         ref         rows         Extra

1         SIMPLE         users         ALL         NULL         NULL         NULL         NULL         1634

ORDER BY NULL

Kod:

id         select_type         table         type         possible_keys         key         key_len         ref         rows         Extra

1         SIMPLE         users         ALL         NULL         NULL         NULL         NULL         2134

fabian

2010-04-18 22:01

För att räkna alla rader kan du ju köra

Kod:

SELECT count(*) FROM users;

Sedan kör du på sida 1

Kod:

SELECT * FROM users ORDER BY id DESC LIMIT 0,50;

På sida 2

Kod:

SELECT * FROM users ORDER BY id DESC LIMIT 50,50;

På sida 3

Kod:

SELECT * FROM users ORDER BY id DESC LIMIT 100,50;

Eller är det något jag missar?

tartareandesire

2010-04-18 22:02

Citat:

Ursprungligen postat av zilveer (Inlägg 20351766)

Är det segt även med COUNT? Du bör som sagt var inte läsa hela tabellen bara för att räkna rader.

Clarence

2010-04-18 22:04

Citat:

Ursprungligen postat av zilveer (Inlägg 20351766)

men om mysql anser att det går snabbare att läsa så hela tabellen istället för att använda index förstår jag inte för som det ser ut nu så körs denna mysql anrop varje gång någon laddar sidan. har man 10 000 besökare så blir det ju 10 000 slow queries..

Ingen som kan hjälpa mig med att lösa detta?

Du har redan fått svaret. Du bör inte hämta alla rader. Du bör använda LIMIT. Sidindelning görs med enkelt genom att hämta totala antalet rader från databasen. Dock bör du inte köra en generisk COUNT för varje besökare mot en innodb-tabell utan spara undan antalet rader i speciell tabell/rad/fil - vid insert eller jämna mellanrum beroende på mängd inserts. Detta då innodb bara har en ungefärlig rowcount cachad.

zilveer

2010-04-18 22:11

Saken är den att jag använder paging scriptet som finns på följande länk:
http://net.tutsplus.com/tutorials/ph...data-with-php/

nu verkar det som att den inte går att använda..

Clarence
kan ej spara antalet rader i en fil/tabell då jag även har sökkriterier på sidan.. kanske blir enklast att bara köra med anrop:

count()...sök kriterier..

sen så kör jag samma anrop med:

sökkriterier.. order by ?

Lindahl

2010-04-18 22:29

Kolla lite på SQL_CALC_FOUND_ROWS. Då kan du få fram totala antalet rader även om du använder LIMIT.

zilveer

2010-04-18 22:47

Tack för svar allihopa..

Jag har kommit fram till följande:

I och med att sidan kommer att visas beroende på sökkriterier och jag använder paging och behöver limit på detta så är den smidigaste lösningen

att t.ex. få följande mysql anrop rad

observera att detta bara är ett exempel på anrop, den är inkorrekt men vill bara visa hur jag skulle vilja ha det.

Kod:

SELECT * FROM users t LEFT JOIN guestbook g ON g.uid=t.i WHERE bla bla bla GROUP BY bla bla ORDER BY id DESC LIMIT 0,25



till denna rad:



SELECT COUNT(*) FROM users t LEFT JOIN guestbook g ON g.uid=t.i WHERE bla bla bla

med andra ord vill jag ersätta allt som finns mellan SELECT och FROM med COUNT(*) samt att jag vill få bort group by, order by samt limit..

Om jag inte har fel så kan man fixa detta med reguljära uttryck, är bara n00b på detta.. har kommit så här långt:

Kod:

        $search_sql = "SELECT * FROM users t LEFT JOIN guestbook g ON g.uid=t.i WHERE bla bla bla GROUP BY bla bla ORDER BY id DESC LIMIT 0,25";

        $reg_expr = '/SELECT * FROM$/';

        

        $rep_with = ' COUNT(*) ';

        $count_sql = preg_replace($reg_expr, $rep_with, $search_sql);

någon som har en lösning?

taz76

2010-04-18 23:33

varför krångla till det med ett regex? om du vet tabellens namn så ser jag ingen anledning att krångla med regex.

Jonas

2010-04-19 11:03

SQL " ORDER BY " åsidosätter användning av INDEX.

Därför kommer frågan att klassas som SLOW QUERY när du använder det.

Fabians " SELECT count(<fältnamn>) ..." är ett bättre alternativ eftersom den kommer att använda INDEX.

Clarence

2010-04-19 22:06

Citat:

Ursprungligen postat av Jonas (Inlägg 20351830)

Nej, det beror på WHERE-satsen, joins, sorteringsordningen (ASC/DESC) osv. Endast ett index används, men kan användas för både sortering och villkor. Se http://dev.mysql.com/doc/refman/5.0/...imization.html för detaljer för första biten.

En liten detalj är också att COUNT(fältnamn) räknar icke-null värden på fältet. COUNT(*) däremot räknar antalet rader.

Dock bör man om man använder innodb använda räknare så långt som möjligt - med ett gäng miljoner rader kan en COUNT(*) lätt ta flera minuter (och i bästa fall hamna i query cache en liten liten stund innan en ny insert görs). Eftersom frågan verkar kunna ställas utan WHERE-sats bör man ha en räknare för antalet rader. Sen åsidosätter man den med en COUNT(*) för ovanliga queries (ofta allt utom just den utan villkor).

zilveer

2010-04-20 23:30

Hej och tack för alla svar.. Nu har jag åtgärdat problemet med en COUNT(*), sen så kör jag med en vanlig SELECT-sats för att hämta ut data från mysql.

Nu har jag dock ett annat litet mindre problem med mysql slow queries.
Följande rad hamnar i mysql slow queries filen, här kan man ju inte köra en explain så jag förstår inte riktigt varför det blir så.

Kod:

UPDATE apps SET downloads = downloads+1 WHERE id = "ettid" ;

Jag har satt ID som en primär nyckel, förstår inte att det kan hamna under mysql slow queries? Någon som har tips på vad detta kan bero på?

zilveer

2010-04-20 23:30

Kod:

UPDATE apps SET downloads = downloads+1 WHERE id = "ettid" ;

Jag har satt ID som en primär nyckel, förstår inte att det kan hamna under mysql slow queries? Någon som har tips på vad detta kan bero på?

goose

2010-04-20 23:48

är id en sträng, eller bara ett konstigt exempel (syftar på "ettid") ?

wdb	2010-04-21 08:55

Citat:

Ursprungligen postat av Jonas (Inlägg 20351830)

Grattis, 3 av 3 fel. Folk som bara gissar borde verkligen inte uttala sig.

zilveer, det blir enklare att se vad som är fel om du postar en DESCRIBE på tabellen så att vi ser att du faktiskt har id som primary key och inte förvirrat till det för dig själv.

zilveer

2010-04-21 11:42

wdb:

här ser du:

Kod:

Full Texts          Field         Type         Null         Key         Default         Extra

                 id         int(11) unsigned         NO         PRI         NULL         auto_increment

                 name         varchar(255)         NO         MUL         NULL

wdb	2010-04-22 09:05

Citat:

Ursprungligen postat av zilveer (Inlägg 20352137)

wdb:

här ser du:

Kod:

Full Texts          Field         Type         Null         Key         Default         Extra

                 id         int(11) unsigned         NO         PRI         NULL         auto_increment

                 name         varchar(255)         NO         MUL         NULL

Är det där verkligen apps-tabellen?

zilveer

2010-04-23 18:05

Hej nej nu har det blivit fel:

hä'r är tabellen:

id int(11)
name varchar(255)
downloads int(11)
downloads_total int(11)

zilveer

2010-05-07 15:54

ingen som har lust att svara ?

Alla tider är GMT +2. Klockan är nu 10:29.