MySQL: Vícesloupcový podvýběr

Výběry z tabulek jsou často využívány v nejjednodušší možné podobě SELECT * FROM `tabulka` WHERE podmina, zkušenější programátoři hnězdičku moc nepoužívají, vyjmenují pouze sloupce které potřebují, čímž trochu sníží datová zátěž pro přenosy mezi serverem a aplikací. Hodně používaný je JOIN (celá řada „programátorů“ skončí u LEFT JOIN) a v oprávněných případech někdo používá podvýběry.

Podvýběry, sub SELECTy, jsou fajn nástroj, ale ne vždy je triviální dostat data jak potřebuji, dostal jsem se do situace, kdy jsem potřeboval podvýběrem vybrat více sloupců a to MySQL nedovoluje (možná jiný SQL server ano). Za příklad poslouží aktuálně řešený případ – novinky pro jeden web, protože k novince může být přiřazený obrázek, ale nemusí, ne vždy je to pravidlem.

Jedna tabulka slouží pro uložení obsahu novinky:

CREATE TABLE `news` (
  `news_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `langs_id` int(10) unsigned NOT NULL DEFAULT '0',
  `socbook` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `visible` int(1) unsigned NOT NULL DEFAULT '0',
  `title` varchar(200) COLLATE utf8_czech_ci NOT NULL,
  `name` varchar(200) COLLATE utf8_czech_ci NOT NULL,
  `content` text COLLATE utf8_czech_ci NOT NULL,
  `add_date` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `last_edit` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`news_id`),
  UNIQUE KEY `idx_name` (`name`,`langs_id`),
  KEY `idx_main_pg` (`langs_id`),
  KEY `add_date` (`add_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci COMMENT='persistent pages of web' AUTO_INCREMENT=14 ;

INSERT INTO `news` (`news_id`, `langs_id`, `socbook`, `visible`, `title`, `name`, `content`,
  `add_date`, `last_edit`) VALUES
(13, 1, 0, 1, 'Babyprofi.cz - Nový partner',
  'n13-babyprofi-cz-nov-partner', 'Oznamujeme zákazníkům, že naše zboží mohou zakoupit 
  u nového parntera firmy, kterým se stává firma BabyProfi.cz.
',
  '2011-08-20 17:29:12', '2011-08-20 17:31:02'),
(12, 1, 0, 1, 'Babyprofi.cz - Nový partner', 'n12-babyprofi-cz-nov-partner',
  'Oznamujeme zákazníkům, že naše zboží mohou zakoupit u nového parntera firmy, 
  kterým se stává firma BabyProfi.cz.
',
  '2011-08-20 17:28:58', '2011-08-20 17:28:58'),
(11, 1, 0, 1, 'Babyprofi.cz - Nový partner', 'n11-babyprofi-cz-nov-partner',
  'Oznamujeme zákazníkům, že naše zboží mohou zakoupit u nového parntera firmy, 
  kterým se stává firma BabyProfi.cz.
',
  '2011-08-20 17:27:29', '2011-08-20 19:09:48');

Druhá tabulka mi slouží pro přiřazení obrázku. Možná to vypadá komplikovaně, ale tento podivný systém mi šetří hodně starostí, protože obrázky, a bez problémů i další přílohy, nicméně teď mne zajímají opravdu jen obrázky, jsou uloženy na disku jen s id a koncovkou a v databázi je určeno kam patří, jaký mají typ. Tabulka pro soubory vypadá následovně:

CREATE TABLE `files` (
  `files_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL DEFAULT '0',
  `ptype` varchar(25) COLLATE utf8_czech_ci NOT NULL,
  `ftype` char(4) COLLATE utf8_czech_ci NOT NULL,
  `fname` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`files_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=219;

INSERT INTO `files` (`files_id`, `item_id`, `ptype`, `ftype`, `fname`) VALUES
(211, 41, 'products', 'jpg', 'bt359955.jpg'),
(217, 12, 'news', 'jpg', 'logo.jpg'),
(218, 13, 'news', 'jpg', 'abc_design_logo.jpg');

V tabulkách jsem ponechal i nějaká data, aby šlo testovat.

Při výběru potřebuji 5 posledních záznamů, jež jsou označeny jako zobrazitelné, tj. ve sloupci `visible` nemají nulu, seřadit je sestupně podle data a pokud existuje k nim obrázek, tak jej hned vybrat také. Programátor, který nepřemýšlí by vybral potřebné novinky, pak pole s novinkami cyklem prošel a doplnil si obrázky do pole, neboli, pokud bude chtít zobrazovat posledních 5 novinek, znamená to 6 dotazů na databázi, což není mnoho, ale pokud jsou stránky dostatečně hodně navštěvované, je dobré odstranit každý nadbytečný dotaz. Je to cesta, do níž opravdu nechci, byť funguje.

Další metoda, udělat podvýběr (SELECT `t1`.`news_id`, `t1`.`title`, `t1`.`content`, `t1`.`add_date`, (SELECT `t2`.`files_id`, `t2`.`ftype`, `t2`.`fname` FROM `files` WHERE `ptype`='news' AND `t1`.`news_id`=`t2`.`item_id`) FROM `news` AS `t1` WHERE `t1`.`langs_id`='1' AND `t1`.`visible`<>'0' ORDER BY `t1`.`add_date` LIMIT 5) nepůjde, minimálně na nejrozšířenější MySQL, protože není povolený výběr více než jednoho sloupce.

Spojením tabulek pomocí JOINu se dostávám správným směrem, ale také to nebude cesta zcela přímá, prosté propojení SELECT `t1`.`news_id`, `t1`.`title`, `t1`.`content`, `t1`.`add_date`, `t2`.`files_id`, `t2`.`ftype`, `t2`.`fname` FROM `news` AS `t1` LEFT JOIN `files` AS `t2` ON `t1`.`news_id`=`t2`.`item_id` WHERE `t1`.`langs_id`='1' AND `t1`.`visible`<>'0' AND `t2`.`ptype`='news' ORDER BY `t1`.`add_date` LIMIT 5 skrývá jedno úskalí. Vybral jsem sice novinky s obrázky, ale obrázek není povinná položka, takže nebude-li zadán, zahodil jsem novinku kvůli podmínce `t2`.`ptype`='news', jíž vynechat nelze, protože stejná hodnota `item_id` se může objevit jak pro novinky, tak pro položky zboží, nebo partnery.

Ale JOIN je správná cesta, jak jsem našel v diskuzích, jen místo názvu tabulky připojuji výběr z tabulky files, který jsem patřičně omezil:

SELECT 
  `t1`.`news_id`, `t1`.`title`, `t1`.`content`, `t1`.`add_date`,
  `t2`.`files_id`, `t2`.`ftype`, `t2`.`fname` 
FROM `news` AS `t1` LEFT JOIN (
  SELECT DISTINCT `files_id`, `item_id`, `ftype`, `fname` FROM `files` WHERE `ptype`='news'
) AS `t2` ON `t1`.`news_id`=`t2`.`item_id` WHERE
  `t1`.`langs_id`='1' AND `t1`.`visible`<>'0' ORDER BY `t1`.`add_date` LIMIT 5

Efektivita nejspíše nebude moc slavná, ale rozhodně lepší, než při cyklickém tázání se na tabulku souborů. Určitě je potřeba si poladit indexy. V zápisku jsem postup vysvětlil na konkrétním příkladu, ale myslící programátor jej určitě dovede překlopit na své potřeby.

6 komentářů u „MySQL: Vícesloupcový podvýběr“

  1. ad
    Při výběru potřebuji 5 posledních záznamů, jež jsou označeny jako zobrazitelné, tj. ve sloupci `visible` nemají nulu, seřadit je sestupně podle data a pokud existuje k nim obrázek, tak jej hned vybrat také. Programátor, který nepřemýšlí by vybral potřebné novinky, pak pole s novinkami cyklem prošel a doplnil si obrázky do pole, neboli, pokud bude chtít zobrazovat posledních 5 novinek, znamená to 6 dotazů na databázi, což není mnoho, ale pokud jsou stránky dostatečně hodně navštěvované, je dobré odstranit každý nadbytečný dotaz. Je to cesta, do níž opravdu nechci, byť funguje.
     
    Není nutné mín dotazů šest stačí dva. Jeden načte novinky. Průchodem cyklem se zjistí odpovídající ID, ale dotaz na ně se nevolá hned, ale až na konci cyklu jako WHERE id IN (…). Vpodstatě jde o LEFT JOIN na straně PHP,

    1. @Jan Hrouza: Jasně, to mě nenapadlo, protože jsem se soustředil jen na SQL část a přitom IN docela běžně používám. Díky za upozornění, že jsem kvůli hledání jednoho místa neviděl vlevo-vpravo. Mimochodem nechci se bavit jen o PHP, používám i další jazyky, třeba VB.Net (ten s FirebirdSQL a SQLite), nebo Python, a databáze (asi ty nejznámější svobodné – MySQL, FirebirdSQL, SQLite a poměrně vzásně i PostgreSQL).
      Nehledal jsem nejtriviálnější cestu, primární snaha byla dostat vše do jediného dotazu a povedlo se mi bez potřeby další manipulace s poli. Vím že jsem neobjevil žádnou ameriku, ale třeba někomu podobný hint pomůže. Předpokladem pochopitelně je, že k záznamu vybírám jediný obrázek, na více obrázků/souborů používám jiný postup a hlavně tuto potřebu mám až na detailu produktu/novinky.

  2. Prosim precti si neco o outer join (doufam, ze ho mysql zna :-). Pak to dokazes dat i bez pod dotazu – ktery je v tomto pripade velmi spatnym reseni (o skladani vysledku mimo dtb ani nemluve 🙂

  3. 1) Zkoušel autor EXPLAIN svého dotazu? 
    2) Má autor povědomí o indexech?
    3) Proč autor nepoužil jednoduché: SELECT … FROM `news` AS `t1` LEFT JOIN `files` AS `t2` ON `t1`.`news_id`=`t2`.`item_id` AND `t2`.`ptype`='news' WHERE  `t1`.`langs_id`='1' AND `t1`.`visible`<>'0' ORDER BY `t1`.`add_date` LIMIT 5

    1. @Petr: Díky, zapomenuté OUTER JOIN. 🙁 Edit:  MySQL OUTER JOIN evidentně neumí, našel jsem řešení, ale většina byla založena na cimrmanovských úkrocích stranou a UNIONování výsledků.

      @LuKo:
      1. Zkoušel a vyšel příšerně, ale na to mám o krok dále cachování, bavím se teď jen o SQL části.
      2. Co jsou indexy? Dobře blbý pokus o vtip. (BTW: v článečku chybí index na `ptype` v tabulce `files`)
      3. Protože pro obrázky může být, a v některých případech bude, i další podmínka. Mimochodem, jste si jist, že Vám vyleze i novinka, jež obrázek nemá, tzn. `t2`.`ptype` bude NULL? Jde připojit OR `t2`.`ptype`=NULL, ale teď netestuji, jestli projde OK.
      Edit: Obávám se, že špatně, protože vazba na `t2`.`ptype` musí být, stejná hodnota `t1`.`item_id` může přijít z produktů, kategorií,…

  4. Díky za zajímavý článek .. vklidu si to prostuduji 🙂

Komentáře jsou uzavřeny.