MySQL: Hur para ihop element med kolumn?

[Konservburk]

Vad jag har att utgå ifrån:

Kod: Markera allt

mysql> SELECT * FROM temp;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    3 |    5 | 
|    7 |   11 |   13 | 
|   17 |   19 |   23 | 
+------+------+------+

Vad jag vill få fram:

Kod: Markera allt

+------+------+
| col  | val  |
+------+------+
| a    |    2 | 
| a    |    7 | 
| a    |   17 | 
| b    |    3 | 
| b    |   11 | 
| b    |   19 | 
| c    |    5 | 
| c    |   13 | 
| c    |   23 | 
+------+------+

Det behöver inte vara just kolumnnamnen i "col", det går även bra med unika nummer:

Kod: Markera allt

+------+------+
| col  | val  |
+------+------+
|    1 |    2 | 
|    1 |    7 | 
|    1 |   17 | 
|    2 |    3 | 
|    2 |   11 | 
|    2 |   19 | 
|    3 |    5 | 
|    3 |   13 | 
|    3 |   23 | 
+------+------+

Finns det något vettigt sätt att få till detta? Jag kan inte mycket om SQL och har bara försökt på måfå hittills. Mitt mest tappra försök var:

Kod: Markera allt

mysql> (SELECT 'a' col, a val FROM temp)
    -> UNION
    -> (SELECT 'b' col, b val FROM temp)
    -> UNION
    -> (SELECT 'c' col, c val FROM temp);
ERROR 1137 (HY000): Can't reopen table: 'temp'

Men man kan tydligen inte referera till samma temporära tabell flera gånger i en SQL-fråga.

[mcNisse]

En lösning är att skippa temp tabellen och skapa en vanlig tabell temporärt...

Eller så gör du tre sökningar i temp-tabbellen och mergar resultatet.

Jag tror inte det finns någon riktigt bra lösning på ditt problem.

[Konservburk]

En lösning är att skippa temp tabellen och skapa en vanlig tabell temporärt...

Kommer inte på fråga eftersom det finns en överhängande risk för kollisioner med andra sessioner.

Eller så gör du tre sökningar i temp-tabbellen och mergar resultatet.

Hur? Menar du att jag ska skapa tre till temp-tabeller och sedan köra UNION på dessa?

Jag tror inte det finns någon riktigt bra lösning på ditt problem.

Man brukar ju säga att "alla sätt är bra utom de dåliga".

[mcNisse]

Hur? Menar du att jag ska skapa tre till temp-tabeller och sedan köra UNION på dessa?

Det går också bra. Dock var det inte det jag menade. Jag menar att du istället för att köra en sqlsats kör tre för att få fram datat.

[Konservburk]

Tro det eller ej, men nu har jag lyckats få till något som skulle kunna vara en lösning. Det verkar åtminstone ge rätt resultat. Om det sedan är en bra lösning eller inte får SQL-gudarna avgöra:

Kod: Markera allt

mysql> SELECT x.col,
    ->   CASE x.col
    ->     WHEN 1  THEN temp.a
    ->     WHEN 2  THEN temp.b
    ->     WHEN 3  THEN temp.c
    ->   END val
    -> FROM temp,
    -> (
    ->   SELECT 1 col
    ->   UNION
    ->   SELECT 2 col
    ->   UNION
    ->   SELECT 3 col
    -> ) x;
+-----+------+
| col | val  |
+-----+------+
|   1 |    2 | 
|   1 |    7 | 
|   1 |   17 | 
|   2 |    3 | 
|   2 |   11 | 
|   2 |   19 | 
|   3 |    5 | 
|   3 |   13 | 
|   3 |   23 | 
+-----+------+

Nu har jag en följdfråga. Det hela bygger på att jag använder en tabell med en kolumn och lika många rader som temp-tablellen har kolumner, med radnumret som värde. Går det på något sätt att få till en sådan tabell med godtyckligt antal rader? Min variant blir lite jobbig om temp-tabellen har många kolumner eftersom jag behöver lista varje rad för sig med en separat SELECT:

Kod: Markera allt

mysql> SELECT 1 col
    -> UNION
    -> SELECT 2 col
    -> UNION
    -> SELECT 3 col;
+-----+
| col |
+-----+
|   1 | 
|   2 | 
|   3 | 
+-----+

[mcNisse]

Ett litet tips. Du kan kanska enkelt avgöra om frågan är bra. Skriv bara explain framför sqlsatsen så får du reda på exekveringsplanen. För stora tabeller är det förödande med fulltablescan. Onödiga sorteringar är heller inte bra.

Ytterligare en sak som är bra att veta är att subselecter i mysql är totalt värdelösa då de i dagsläget alltid ger en fulltablescan.

[Konservburk]

Tack för tipset med EXPLAIN! Nu gäller det bara att kunna tolka informationen den ger ifrån sig. Var ser jag t.ex. om det är en "full table scan" eller inte?

Kod: Markera allt

mysql> EXPLAIN
    -> SELECT x.col,
    ->   CASE x.col
    ->     WHEN 1  THEN temp.a
    ->     WHEN 2  THEN temp.b
    ->     WHEN 3  THEN temp.c
    ->   END val
    -> FROM temp,
    -> (
    ->   SELECT 1 col
    ->   UNION
    ->   SELECT 2 col
    ->   UNION
    ->   SELECT 3 col
    -> ) x;
+----+--------------+--------------+------+---------------+------+---------+------+------+----------------+
| id | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+--------------+--------------+------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY      | temp         | ALL  | NULL          | NULL | NULL    | NULL |    3 |                | 
|  1 | PRIMARY      | <derived2>   | ALL  | NULL          | NULL | NULL    | NULL |    3 |                | 
|  2 | DERIVED      | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used | 
|  3 | UNION        | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used | 
|  4 | UNION        | NULL         | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used | 
| NULL | UNION RESULT | <union2,3,4> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                | 
+----+--------------+--------------+------+---------------+------+---------+------+------+----------------+

Eftersom du säger att subqueries är totalt värdelösa så undrar jag förstås om det alltid går att skriva om dem mer effektivt genom att använda JOIN istället?

[mcNisse]

Kolumnen possible_keys så visar den vilka index som frågan går kan gå på. Och key visar vilket index den väljer. Null i key innebär fulltablescan.

Är det lite data (kolumnen rows) så spelar det ingen roll om det blir fulltablescan eller ej.

Eftersom du säger att subqueries är totalt värdelösa så undrar jag förstås om det alltid går att skriva om dem mer effektivt genom att använda JOIN istället?

Det går oftast.