Mysql, lägga ihop två lika tabller, med lika kolumner.

[TonyLi]

Hej (Mysql)

Jag har glömt, inte kommit på, hur jag ska attache en tabell till en annan.
Jag har alltså två tabeller, som ser lika dana ut med lika kolumnnamn. Enda som skiljer är olika tabellnamn såklart och olika data innehåll, men av samma typ.

Jag vill alltså att om ena tabellen är 4 kolumner och 40 rader, och den andra 4 kolumner och 30 rader. Så vill jag ha en ny tabell med 4 kolumner och 70 rader.

1)
Antar vi börjar med:
Create table tabell3 as ...

2)
Egentligen räcker det ju med en sql som skapar detta utan en tredje tabell, eller om det går att addera. Nåväl Ni förstår. Tack.

/ Tony

[Tobias Nyholm]

Hej.

1)
Det du försöker göra är att skapa en view. (Matrialiserad) Det går och är bättre för prestanda om data inte uppdateras så ofta. Men det är inte det du vill. Det är får lite data för att prata om prestanda förbättringar.

2)
Du vill göra en Join. För att vara mer specifik vill du göra en Natural Join.
En Natural Join kollar om två kolumnnamn är lika och joinar ihop dem. En vanlig join så får du bestämma själv vilka kolumner som ska joinas.

SELECT * FROM table1 NATURAL JOIN table2;

Om du inte vill/kan/har lust att använda natural join så kan man skriva så här:

SELECT * FROM table1 t1, table2 t2 WHERE t2.artnr=t1.artnr

Om du vill skiva om en vanlig join så kan det se ut så här:

SELECT * FROM table1 t1, table2 t2 WHERE t2.artnr=t1.id

I vanliga fall, då du inte vill ha ALL data skriver man så här:

SELECT t1.name, t1.lager, t2.artnr, t2.pris FROM table1 t1, table2 t2 WHERE t2.artnr=t1.id

[TonyLi]

Hej

Ja, kanske view. Jag tog bara exemplet, men den äkta data är 107 000 rader med 94 000 rader, så jo det är mycket data.

SELECT * FROM table1 NATURAL JOIN table2;
gav noll rader, då jag ersatte tabel1 och tabel2 med namnen.

SELECT * FROM table1 t1, table2 t2 WHERE t2.artnr=t1.artnr
Borde inte ge vad jag vill, då den bara matschar id. Jag har ju fler id på ena tabellen?

[Michael Bingmert]

Är det inte union du letar efter?

CREATE TABLE `data1` (
`falt1` INT NOT NULL ,
`falt2` VARCHAR( 10 ) NOT NULL ,
`falt3` DATETIME NOT NULL ,
`falt4` FLOAT NOT NULL 
) ENGINE = MYISAM ;

CREATE TABLE `data2` (
`falt1` INT NOT NULL ,
`falt2` VARCHAR( 10 ) NOT NULL ,
`falt3` DATETIME NOT NULL ,
`falt4` FLOAT NOT NULL 
) ENGINE = MYISAM ;

INSERT INTO `data1` (`falt1` ,`falt2` ,`falt3` ,`falt4` ) VALUES ('100', 'Etthundra', '2011-01-27 10:00:00', '100.1');
INSERT INTO `data1` (`falt1` ,`falt2` ,`falt3` ,`falt4` ) VALUES ('200', 'Tvåhundra', '2011-01-27 11:00:00', '200.2');
INSERT INTO `data1` (`falt1` ,`falt2` ,`falt3` ,`falt4` ) VALUES ('300', 'Trehundra', '2011-01-27 12:00:00', '300.3');
INSERT INTO `data2` (`falt1` ,`falt2` ,`falt3` ,`falt4` ) VALUES ('110', 'Etthundratio', '2011-01-27 10:10:00', '400.1');
INSERT INTO `data2` (`falt1` ,`falt2` ,`falt3` ,`falt4` ) VALUES ('220', 'Tvåhundratjugo', '2011-01-27 11:10:00', '500.2');
INSERT INTO `data2` (`falt1` ,`falt2` ,`falt3` ,`falt4` ) VALUES ('330', 'Trehundratrettio', '2011-01-27 12:10:00', '600.3');

SELECT * FROM `data1` UNION SELECT * FROM `data2;

[TonyLi]

Hej

Ja, det stämmer som donation skrev. Tack båda.


/ Tony

[TonyLi]

Hej fortsättning.

Hur får jag in order by exempelvis kolumn 2 på sluttabellen.
(SELECT * FROM `data1` UNION SELECT * FROM `data2)
order by 2;

Ovan syntax gällande order by är inte rätt, men ni förstår min avsikt.

Tack
/Tony

[Michael Bingmert]

Det bör bara vara att ta bort parenteserna och använda kolumnnamnet i stället för '2' då så klart.

SELECT * FROM `data1` UNION SELECT * FROM `data2` order by falt4;

..funkar utmärkt. Ordningen ska operera på hela resultatsettet. Det är betydligt värre att sortera tabellerna individuellt, så jag hoppas det inte var det du var ute efter :-)

[TonyLi]

Ser bra ut, tack.
Nu kommer bonusfrågan.
Om vi nu pratar 2 tabeller på 300 000 rader + 100 000 rader,
resultat ger 1 - 10 svar.
Jag märker att det tar sin tid för webbsidan..
Sedan kommer det troligen bli tabeller som är 3 000 000 rader + 100 000 rader i andra tabellen.
Svaren på select wheresatsen som läggs på ovan UNION är 10 - 100 svarsrader.
Utsorterat på en kolumn som består av textrad på 20 tecken ungefär.

Jag förstår att index kanske ska börja användas.... antar på den utsorterade kolumnen i select wheresatsen.
Grejen är att totaltabellen kommer växa under ett par månader.
Och jag vill slippa manuellt dela upp tabellen då det antagligen bli trögt.
Är det vettigt med View? Men det har väl inget att göra med detta? Är inte view bara en virtuell tabell,
men ändå gå igenom allt varje gång. Tabellen kan sorteras på 290 delar, då jag vet att svaret alltid är
en specifik radtillhörighet.

Tack

/ Tony

[Michael Bingmert]

Klockan är lite för mycket för att jag ska hänga med nu Tony, men generellt så kan du använda "explain" för att klura ut vad som pågår under huven på MySQL.

Skriv "explain select * from bla bla" så talar den om vad den använder för index. Om den inte använder index, eller skräms med uttryck som "filesort" så bör du titta på alternativ. Att köra "order by" på en UNION är nog allid svindyrt prestandamässigt, eftersom resultatet är ett derivat från flera tabeller som inte kan vara färdigindexerat utan måste indexeras on the fly. Om du alltid letar efter en liten resultatmängd (<1000 rader från en tabell på >100.000 poster till exempel) så är det billigare att söka ut dem osorterat (unionen i sig går fort) och sedan sortera dem i en matris i PHP eller vad du nu använder. Om du har många användare och hög serverbelastning kan du fördela belastningen ut på klienterna genom att sortera med javascript.

Om du vill ha delmängder ur de olika tabellerna så kan du köra ex.vis:

SELECT * 
FROM `data1` 
WHERE falt4 <250
UNION SELECT * 
FROM `data2` 
WHERE falt4 <250
ORDER BY falt4

I det där fallet så skall du indexera falt4 i BÅDA tabellerna.

Men som sagt, nu blev jag lite generell, men kanske hjälper det dig vidare.

[TonyLi]

Tack, intressant svar.

[Tobias Nyholm]

Njea, Michael. MySQL optimerar sina frågor riktigt hårt. Sorteringen görs därför troligtvis sist och kommer inte att slöa ner frågan så mycket.

TonyLi: Eftersom tabellen är så stor och du tycker att det går lite för långsamt då är en view lösningen. Det finns två storters view. Matrialiserad och ej matrialiserad. MySQL stödjer den ena har jag för mig... vet ej vilken.

En ej matrialiserad view så sparar du en (färdigoptimerad) query och kör den så fort du använder viewn. (Mycket liten prestanda vinst men alltid färska resultat)

En matrialiserad view sparas svaret som en tabell. Så i ditt fall skulle den tabellen innehålla 10-100 rader. Nackdelen är att tabellen innehåller data från när viewn skapades. DVS, om du skapar en view och sen uppdaterar data så kommer viewn vara outdated. Man kan kasta och skapa en ny view med jämna intervall. Typ en gång per dag..

[TonyLi]

Tack, bra tanke. Jag tänkte mig något åt det hållet du nämner på slutet, om kasta och skapa viewtabell.
Kan jag på något sätt göra åt att sökmotorspindlarna, inte stör sig av view uppdateringarna.
Eller det kanske bara är någon sekund, så det ska fungera.... eller om man vill vara omständig, skapa view bredvid och göra rename.
Nåväl, låter som bra tankar Tobias.

[Michael Bingmert]

Njea, Michael. MySQL optimerar sina frågor riktigt hårt. Sorteringen görs därför troligtvis sist och kommer inte att slöa ner frågan så mycket.

Det är precis det som är problemet. MySQL måste sortera resultatet av unionen vilket är en temporär tabell (eller hur nu MySQL hanterar det internt). Sorteringen verkar inte heller kunna dra nytta av de delindex som finns för respektive tabell. Ökade på till dryga miljonen poster och testade lite:

SELECT * FROM `data1` UNION ALL SELECT * FROM `data2`: Visar rader 0 - 29 (1 250 075 totalt, Frågan tog 0.2606 sek)
SELECT * FROM `data1` UNION ALL SELECT * FROM `data2` order by falt4 (indexerad): Visar rader 0 - 29 (1 250 075 totalt, Frågan tog 1.7608 sek)
SELECT * FROM `data1` UNION ALL SELECT * FROM `data2` order by falt1 (oindexerad): Visar rader 0 - 29 (1 250 075 totalt, Frågan tog 1.7962 sek)
..notera att de tar lika lång tid oavsett index. Det är mycket värre om man sorterar på varchar-fält:
SELECT * FROM `data1` UNION ALL SELECT * FROM `data2` ORDER BY falt2: Visar rader 0 - 29 (1 250 075 totalt, Frågan tog 2.4745 sek)


Däremot är det enorm skillnad på indexerade och icke indexerade fält i where-satsen:

SELECT * FROM `data1` WHERE falt1>500 and falt1<550 UNION ALL SELECT * FROM `data2` WHERE falt1>500 and falt1<550: Visar rader 0 - 29 (252 totalt, Frågan tog 0.1797 sek)
SELECT * FROM `data1` WHERE falt4>500 and falt4<505 UNION ALL SELECT * FROM `data2` WHERE falt4>500 and falt4<505: Visar rader 0 - 29 (1 021 totalt, Frågan tog 0.0023 sek)

Nu har jag många poster men fortfarande små datamängder. Det blir betydligt värre om poststorlekarna växer och man tappar cacheträffar på löpande band.

Eftersom jag förstår det som att Tony hela tiden söker ut små datamängder ur stora tabeller så är det lätt löst med indexerade kolumner och wheresatser. Där gör MySQL ett bra jobb. Vill man snabba på det ytterligare och har vanligt förekommande frågor så skulle jag testa med Stored Procedures också, de kan knuffa på det hela ordentligt ibland.

Om datat importeras sällan så skulle jag smacka ihop tabellerna i stället för att använda en vy.

En annan sak vi inte ens nämnt här men som ju kan vara intressant är att det är stor skillnad på UNION och UNION ALL. Den första är en implicit "DISTINCT" vilket kan skapa prestandaproblem i vissa lägen om den inte behövs, eller kanske rentav inte får förekomma.

[TonyLi]

Hej
Trevligt att du testat mycket data, det är ett område som behöver bättre förståelse.
Jag hörde av en vän att view inte lagrar, utan går igenom tabellen varje gång. Kanske cashen ni menar.

Utöver det så hade jag helt glömt bort normalisering. Lätt hänt när ett är lite data, men nu när vi pratar fler hundra tusen,
så kan det vara väsentligt. Att med normalisering indexera på integer istället för sträng.
/ Tony

[Tobias Nyholm]

Michael, Du sorterar över en miljon poster i ditt exempel. Det var inte det TonyLi vill göra och inte det jag menade. Det är självklart att det tar massa tid att sortera så mycket.
Testa plocka ut ca 100 rader med en vanlig selektion (inte limit) typ:

SELECT * FROM `data1` UNION ALL SELECT * FROM `data2` WHERE falit4<100
SELECT * FROM `data1` UNION ALL SELECT * FROM `data2` WHERE falit4<100 order by falt4

TonyLi:

Jag hörde av en vän att view inte lagrar, utan går igenom tabellen varje gång. Kanske cashen ni menar.

Din vän tänker på en icke-materialiserad view. Du ska använa en matrialiserad view för att öka prestanda.

[TonyLi]

Hej
Ja, jag / vi känner inte till materialiserad view. Får kolla det.
Om vi pratar stored proceedure, är det att köringen körs på databas processorn, till skillnad från view (vanlig select) (php) som körs ??? client eller webbserver?
Och var körs då materialiserad view?
Så om jag förstår rätt, så vore det bra att låta materialiserad view på stora db tabellen med databasprocessorn, medan övriga webbsidan php kan laddas i annan processor? Är det bra tänkt?
/ Tony

[Tobias Nyholm]

Jag vet inte om du förstått rätt.

Se en matrialiserad view som en temporär tabell. Du skriver en lång och jobbig fråga som ger dig ca 100 resultat av 1000 000. De 100 raderna sparar man som en tabell i cachen. (view)
Sen kan du skriva lätta frågor mot den viewn.

För att använda sig av Micheals exempeldatabas ovan:
CREATE VIEW v AS SELECT falt1, falt2 FROM `data1` UNION SELECT * FROM `data2 WHERE bla bla bla; //Plockar ut en mindre del av alla rader och sparar i en view.
SELECT falt2 FROM v WHERE falt1>34; //Ställer en fråga mot viewn.

OBS, den första raden körs bara för att skapa/uppdatera viewn. I vanliga fall körs bara rad 2

Googlar nu och ser att materialiserad view var den typen av view som mysql inte stödjer, men det finns en fix: http://dev.mysql.com/doc/refman/5.0/...iew.html#c9063

[TonyLi]

mm undrar om Loopia som jag har, har fixen.