n+1 problem, un classico

[hhchnos]

Ciao a tutti sono alle prese col cosidetto n+1 problem. Per semplificare devo estrarre dal mio db gli ultimi 10 post pubblicati e i relativi ultimi 5 commenti, ho 2 tabelle: una posts l'altra comments. Il modo facile è: fare una prima query dove prendo gli ultimi 10 post e poi in loop eseguo per ogni post una query al singolo post e ne estraggo 3 commenti, insomma tante query per pochi dati.

Una soluzione fra le tante che ho trovato in rete, link , propone di usare 2 sole query e combinarne gli array. Nel mio caso funziona solo in parte, infatti non è possibile limitare i commenti per singolo post ma solo globalmente.

Ci sto sbattendo la testa da alcuni giorni...

Voi come fareste?

[badaze]

Guarda là => http://forum.html.it/forum/showthrea...readid=2954958

[hhchnos]

Se ho capito bene, questa query ha un solo LIMIT ( per le notizie ). Io vorrei limitare sia la prima che la seconda tabella.

[Alhazred]

... insomma tante query per pochi dati. ...

11 query non mi sembrano questa esagerazione.
Poi così ti ritrovi i dati belli e pronti, nell'altro caso devi far lavorare di più PHP per manipolare i dati, quindi tempo per l'elaborazione e memoria per le strutture dati d'appoggio.

Quale delle due strade conviene?

[hhchnos]

11 query non mi sembrano questa esagerazione.
Poi così ti ritrovi i dati belli e pronti, nell'altro caso devi far lavorare di più PHP per manipolare i dati, quindi tempo per l'elaborazione e memoria per le strutture dati d'appoggio.

Quale delle due strade conviene?

bella domanda... son 2 giorni che cerco sulla rete e le soluzioni sono molto diverse fra loro.

1) nessuno che io sappia risolve il problema con 1 query
2) metà delle persone usa 2 query e lavora poi sulle strutture
3) l'altra metà preferisce fare tutte le query in loop

Ora, sarebbe sempre meglio fare dei microbenchmark a queste soluzioni, ma la cosa che un po mi ha sorpreso è che per un pattern così standard (posts e commenti) non esista un "modo" universalmente utilizzato.

[badaze]

Ce l'ho fatta !!! Ma non prende le notizie senza commenti.

select * from (select @c:=0, @d:=-1, @a:=0 ) as _ROWS
inner join (select @c:=@c+1 AS num_row, id_master, title, @e:=-1 from `master`) A on 1=1 
inner join ( select * from (
	SELECT D1.id_master, min(num_com) AS minc, max(num_com) AS maxc from 
	(select DISTINCT `id_master`, @f:=0 from `detail`) as D1
	 inner join 
	 (select @f:=@f+1 AS num_com, id_master from `detail`) as D2 on D1.id_master = D2.id_master
	 group by D1.id_master
	) AS D3
) as D0 on A.id_master = D0.id_master
inner join (select @a:=@a+1 AS num_com2, id_master, `comment` from `detail`) C on A.id_master = C.id_master
where num_row <= 4 and num_com2 < (minc + 3)
order by num_com2

tabella master :

-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1
-- Généré le :  ven. 02 juin 2017 à 12:54
-- Version du serveur :  5.7.17
-- Version de PHP :  7.1.3
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Base de données :  `tests`
--
-- --------------------------------------------------------
--
-- Structure de la table `master`
--
CREATE TABLE `master` (
  `id_master` int(11) NOT NULL,
  `title` varchar(50) COLLATE latin1_bin NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
--
-- Déchargement des données de la table `master`
--
INSERT INTO `master` (`id_master`, `title`) VALUES
(1, 'argomento 1'),
(2, 'argomento 2'),
(3, 'argomento 3'),
(4, 'argomento 4'),
(5, 'argomento 5'),
(6, 'argomento 6'),
(7, 'argomento 7');
--
-- Index pour les tables déchargées
--
--
-- Index pour la table `master`
--
ALTER TABLE `master`
  ADD PRIMARY KEY (`id_master`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

tabella detail

-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1
-- Généré le :  ven. 02 juin 2017 à 12:53
-- Version du serveur :  5.7.17
-- Version de PHP :  7.1.3
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Base de données :  `tests`
--
-- --------------------------------------------------------
--
-- Structure de la table `detail`
--
CREATE TABLE `detail` (
  `id_detail` int(11) NOT NULL,
  `id_master` int(11) NOT NULL,
  `comment` varchar(50) COLLATE latin1_bin NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
--
-- Déchargement des données de la table `detail`
--
INSERT INTO `detail` (`id_detail`, `id_master`, `comment`) VALUES
(1, 1, 'commento 1 argomento 1'),
(2, 1, 'commento 2 argomento 1'),
(5, 2, 'commento 1 argomento 2'),
(6, 2, 'commento 2 argomento 2'),
(7, 2, 'commento 3 argomento 2'),
(8, 2, 'commento 4 argomento 2'),
(9, 3, 'commento 1 argomento 3'),
(10, 3, 'commento 2 argomento 3'),
(11, 3, 'commento 3 argomento 3'),
(12, 3, 'commento 4 argomento 3'),
(13, 4, 'commento 1 argomento 4'),
(14, 4, 'commento 2 argomento 4'),
(15, 4, 'commento 3 argomento 4'),
(16, 4, 'commento 4 argomento 4'),
(17, 4, 'commento 5 argomento 4'),
(18, 5, 'commento 1 argomento 5'),
(19, 5, 'commento 2 argomento 5'),
(20, 5, 'commento 3 argomento 5'),
(21, 5, 'commento 4 argomento 5'),
(22, 5, 'commento 5 argomento 5'),
(23, 6, 'commento 1 argomento 6'),
(24, 6, 'commento 2 argomento 6');
--
-- Index pour les tables déchargées
--
--
-- Index pour la table `detail`
--
ALTER TABLE `detail`
  ADD PRIMARY KEY (`id_detail`),
  ADD KEY `detail01` (`id_master`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Con un limite di 4 notizie con fino a 3 commenti
20170602_001.JPG

Senza limiti
20170602_002b.JPG

[hhchnos]

dio santo badaze, hai creato un mostro!!!!!

mi ci vuole mezza giornata solo per studiarmelo adesso

[hhchnos]

intanto che mi preparo psicologicamente allo studio del mostro, ho fatto alcuni benchmark fra: soluzione a 2 query e soluzione a 11 query. Il codice è abbastanza grossolano senza nessuna particolare ottimizzazione. I risultati in termini di velocità sono praticamente equivalenti, leggermente migliore la soluzione con 2 query.

per, Badaze: sarebbe molto interessante se alla fine facessi delle comparazioni con le altre 2 modalità. Potresti scriverci un libro (o un articolo)

[badaze]

Mi sembrava di avere già fatto una cosa simile per risolvere un problema su questo forum. Ma va a ritrovare un post fatto forse anni fa. Questa volta ho salvato la query.
Ricordati che non prende i 'master' senza 'detail'. Ti lascio questo compito se ne hai bisogno.

[hhchnos]

Update: ho riordinato un po il codice qui e li e adesso la soluzione con 2 query pare molto più veloce, impiega circa la metà del tempo.

Se a qualcuno interessa posso postare il codice