Forum Mjukvara Programmering och digitalt skapande Tråd Inlägg

MySQL LEFT JOIN från stora tabeller (Error: MAX_JOIN_SIZE)

1
Skriv svar
Permalänk
Medlem
●

MySQL LEFT JOIN från stora tabeller (Error: MAX_JOIN_SIZE)

Håller på med ett projekt där jag använder MySQL som databas och har en tabell för alla användare (users) samt en tabell för att logga aktivitet (log_activity). Det jag kort och gott vill göra är att få fram en tabell med alla användare där en kolumn är den senaste aktiviteten från varje specifik användare. Detta funkade mycket bra med en enkel LEFT JOIN tills att jag fick för mig att "stresstesta" databasen och la in över 100 000 rader i logg-tabellen. Då får jag plötsligt felkoden "#1104 - Den angivna frågan skulle läsa mer än MAX_JOIN_SIZE rader". Har försökt att ta reda på alternativa lösningar genom någon form av nästlade select eller liknande, men jag går bet på hur jag ska skriva denna kod för att den ska passa för mig. Någon som har en bra vägledning åt mig?

Min kod (som tidigare fungerat):

SELECT
*,
MAX(log_create_timestamp) AS senaste_aktivitet
FROM
users
LEFT JOIN log_activity ON log_activity.log_create_user_id = users.user_id
Redigera
Citera flera Citera
Permalänk
Medlem
●

Felmeddelandet antyder att din select-sats skulle returnera ett större antal rader än vad din mysql-server tillåter. Har du behov av att hämta alla användares senaste aktivitetsstämpel?

Antingen kan du begränsa ditt urval genom t.ex.

select top 100 * left join ...

eller endast hämta 1 rad per användare. Det senare förslaget kan dock ge samma fel som du redan får om användartabellen skulle öka markant

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

Nedanstående är helt otestat. Dessutom kör jag inte MySQL till vardags.

Du vill ha index på log_activity.log_create_timestamp och log_activity.log_create_user_id så att det går snabbt att söka där. users.user_id förutsätter jag är primary key, så där behöver du antagligen inget index.

Din MAX(log_create_timestamp) kommer inte att returnera det du vill, den kommer inte köra per log_create_user_id utan på hela svaret, alla rader kommer alltså ha samma värde.

Din nuvarande fråga tar ut alla loggrader för alla användare, vilket gör att du får en väldig massa kandidatrader i den kartesiska produkten mellan loggrader och användare innan WHERE-villkoret hanteras, vilket triggar felet. Försök få ner antalet kandidatrader.

Det svåra är att få ut den senaste loggraden per användare, inklusive loggmeddelandet, från loggtabellen, vilket gör att GROUP BY (log_create_user_id) inte skulle fungera. Testa med PARTITION BY i stället.

Typ:

SELECT 
users.your_username_field,
latestLogsPerUser.your_log_message_fieldname,
latestLogsPerUser.log_create_timestamp,
FROM
users JOIN
  (SELECT
      ROW_NUMBER() OVER(PARTITION BY log_create_user_id ORDER BY log_create_timestamp DESC) rowNumber,
      your_log_message_fieldname,
      log_create_timestamp
      log_create_user_id,
   FROM log_activity WHERE rowNumber = 1) latestLogsPerUser
ON users.user_id = latestLogsPerUser.log_create_user_id

Som sagt, jag har inte ens testat syntaxen i det ovanstående, det är en skiss. Börja med att testa den inre SELECTen och se till att den fungerar, sedan bör det vara trivialt att JOINA in de fält du vill ha från användartabellen.

I värsta fall får du ge dig på det faktiska felmeddelandet, men prio bör vara att få till en fungerande fråga först.

Senast redigerat
Redigera
Citera flera Citera
Permalänk
Medlem
●

Stort tack för era tappra försök att hjälpa mig. @KAD jag tror att du är en lösning närmare, men jag får ändå inte riktigt till det trots att jag försökt under kvällen. Jag gjorde ett exempel av min kod på SQL Fiddle så är det enklare att förklara:

http://sqlfiddle.com/#!9/08eda6/1

Det är inget loggmeddelande som jag behöver få med utan det enda jag kort och gott vill ha utöver det som står i users-tabellen är senaste aktivitet för varje user. Jag hade missat att skriva med GROUP BY i mitt senaste exempel. På denna sida funkar ju allt som jag vill, men lek med tanken att det är några 1000 rader i users och 500 000 rader eller mer i log_activity, då blir det error så behöver en annan smidig lösning för att inte sänka databasen när jag gör förfrågan.

Redigera
Citera flera Citera
Permalänk
Medlem
●

Kikat i din sqlfiddle, du kommer inte runt problemet genom att göra LEFT JOIN efter GROUP BY?

SELECT users.*, log_activity.senaste_aktivitet FROM 
users (SELECT * FROM users) as users
LEFT JOIN
(SELECT log_create_user_id, MAX(log_create) as senaste_aktivitet FROM log_activity GROUP BY log_create_user_id) as log_activity
ON users.user_id = log_activity.log_create_user_id;

Edit: Om det är bekymmer ang prestandan bör du som @KAD sa, indexera log_activity tabellen. Jag tror det vettiga vore att skapa ett composite index på log_create_user_id och log_create.

Du kan testa indexera och sätta SQL_BIG_SELECTS=1 eller höja SQL_MAX_JOIN_SIZE för att köra din query?

Senast redigerat Strök över redundant select-queryn och lade till [code] taggar, tack @snajk!
Redigera
Citera flera Citera
Permalänk
Avstängd
●

Jag tänker mig typ:

SELECT
  users.*
  l.log_create
 FROM
 users
 LEFT JOIN (
   SELECT 
     MAX(log_create) as log_date,
     log_create_user_id AS user_id
   FROM
     log_activity
   GROUP BY log_create_user_id
  ) AS l ON l.user_id = users.user_id
Redigera
Citera flera Citera
Permalänk
Medlem
●
Skrivet av snajk:

Jag tänker mig typ:

SELECT
  users.*
  l.log_create
 FROM
 users
 LEFT JOIN (
   SELECT 
     MAX(log_create) as log_date,
     log_create_user_id AS user_id
   FROM
     log_activity
   GROUP BY log_create_user_id
  ) AS l ON l.user_id = users.user_id
Gå till inlägget

Ja det är samma som min query, men bättre formattering

Märkte att du gjorde iofs ingen select * from users, det var redundant i min query

Redigera
Citera flera Citera
Permalänk
Avstängd
●
Skrivet av Pepe Silvia:

Ja det är samma som min query, men bättre formattering

Märkte att du gjorde iofs ingen select * from users, det var redundant i min query

Gå till inlägget

Jo jag läste inte övriga inlägg så noga. SQL är väl det område eller språk där jag kommer närmast att vara en expert, så det är bara roligt att försöka komma på någon smart lösning. Dock har jag ingen erfarenhet av MySQL utan har bara jobbat med MS SQL, och lite SQLite i skolan förstås. Nu är det mer ORM:er och MongoDB som gäller så det är sällan jag får använda mina kunskaper.

Redigera
Citera flera Citera
Permalänk
Medlem
●

Tackar och bugar det vänligaste. Tror att ni faktiskt har hittat lösningen på mina bekymmer. När jag testade koden i min databas där jag lagt in över 200 000 rader på prov i tabellerna så funkade koden att köra utan felmeddelanden - förutom att jag behövde lägga till ett , -tecken efter SELECT users*, men det var inte så betungande!

Åter igen, stort tack för att ni tog er tid att hjälpa en stackars man i nöd!

Redigera
Citera flera Citera
Permalänk
Medlem
●
Skrivet av Sir.Persson:

Tackar och bugar det vänligaste. Tror att ni faktiskt har hittat lösningen på mina bekymmer. När jag testade koden i min databas där jag lagt in över 200 000 rader på prov i tabellerna så funkade koden att köra utan felmeddelanden - förutom att jag behövde lägga till ett , -tecken efter SELECT users*, men det var inte så betungande!

Åter igen, stort tack för att ni tog er tid att hjälpa en stackars man i nöd!

Gå till inlägget

Härligt! Förhoppningsvis kommer man ihåg detta om man stöter på samma problem i framtiden

Redigera
Citera flera Citera
1
Skriv svar