SQL dotaz

[Jiří Nesvačil]

Zdravim,

predelejte to na left outer join a dale pokud to bude pomale, tak zaindexujte sloupce pro spojeni a ty podle ktereho tridite

CREATE VIEW meta3 AS SELECT a.id,
                             a.title,
                             b.author_sort authors,
                             (SELECT name                 FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
                             a.series_index,
                             (SELECT group_concat(name)   FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
                             (SELECT group_concat(value)   FROM custom_column_1 WHERE custom_column_1.id IN (SELECT value from books_custom_column_1_link WHERE book=books.id)) genre,

                             (SELECT group_concat(value)   FROM custom_column_2 WHERE custom_column_2.id IN (SELECT value from books_custom_column_2_link WHERE book=books.id)) type,
                             path,
                             (SELECT name                 FROM data WHERE data.book=books.id) file,
                             (SELECT group_concat(format) FROM data WHERE data.book=books.id) formats,
timestamp
                             FROM books a
left outer join from books_authors_link c on c.book=a.id
left outer join authors b on  b.authors.id = c.autor
...

Jirka

Dne 14. 11. 2016 v 15:21 Kosťa MK napsal(a):
> Zdravím,
>
> mám následující problém:
>
> Nad databází mám vytvořen relativně složitý view:
>
> CREATE VIEW meta3 AS SELECT id,
>                             title,
>                             (SELECT author_sort          FROM authors WHERE authors.id IN (SELECT author from books_authors_link WHERE book=books.id)) authors,
>                             (SELECT name                 FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
>                             series_index,
>                             (SELECT group_concat(name)   FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
>                             (SELECT group_concat(value)   FROM custom_column_1 WHERE custom_column_1.id IN (SELECT value from books_custom_column_1_link WHERE book=books.id)) genre,
>
>                             (SELECT group_concat(value)   FROM custom_column_2 WHERE custom_column_2.id IN (SELECT value from books_custom_column_2_link WHERE book=books.id)) type,
>                             path,
>                             (SELECT name                 FROM data WHERE data.book=books.id) file,
>                             (SELECT group_concat(format) FROM data WHERE data.book=books.id) formats,
> timestamp
>                             FROM books
>
> funguje perfektně, jenže je zoufale pomalý pokud se pokusí o ORDER nad libovolným sloupcem
>
> Mohl by mě pridím někdo nakopnout jak to zrychlit?
>
> S pozdravem
> Kosta
>
> _______________________________________________
> HW-list mailing list  -  sponsored by www.HW.cz
> Hw-list na list.hw.cz
> http://list.hw.cz/mailman/listinfo/hw-list