Ottimizzare (riscrivere) un po' di query

[andr3a]

posta un dump con dei dati ed un esempio di risultato atteso

[luca200]

Senza una subquery temo sia dura.
Prova un po' se non ho lasciato qualcosa per strada

$result = mysql_query("SELECT q1.id_utenti, q1.name,q1.datareg,q1.voti,    
                       count(r.id_risorse) as risorse FROM
                       (SELECT u.id_utenti, u.name, FROM_UNIXTIME(u.data_registrazione,'%d/%m/%Y') as datareg, count(v.id_voti) as voti
                       FROM utenti u 
                       LEFT JOIN voti v    ON u.id_utenti = v.utenti_id GROUP BY u.id_utenti) as q1 
                       LEFT JOIN risorse r ON q1.id_utenti = r.utenti_id 
                       WHERE LOWER(q1.name) LIKE LOWER('$item%') 
                       GROUP BY q1.id_utenti 
                       LIMIT 100"); 

Ho cambiato la group by usando l'id invece del nome perché mi sembra MOLTO più corretto. Per il resto quoto quanto detto da piero sul collate e sul lower

Originariamente inviato da piero.mac
non capisco perche' usare il LOWER.... se correttamente applicato il collate *_ci non ce ne bisogno, pero' appesantisce.

[andrea.paiola]

allora vediamo un po'...
http://www.webdevmine.it/leggi_utent...tico.php?str=G
dovrebbe restistuire quello che vedete online, cliccando sul link...

invece con l'ultimo script (quello di piero) ritorna:

<table><th>Nome utente</th>
                                         <th>Data registrazione</th>
                                         <th>Numero risorse</th>
                                         <th>Numero voti</th><tr>
                                              <td>[url="utente_dettagli.php?id=3"]gizax[/url]</td>
                                              <td>19/05/2006</td><td>4</td><td>4</td>

                                              </tr></table> 

dump di utenti, risorse e voti:

CREATE TABLE `risorse` (
`id_risorse` int(10) unsigned NOT NULL auto_increment,
`utenti_id` int(11) NOT NULL default '0',
`url` tinytext,
`nome` tinytext,
`lingua` tinytext,
`media` decimal(2,1) unsigned NOT NULL default '0.0',
`n_voti` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`id_risorse`),
KEY `utenti_id` (`utenti_id`),
KEY `media` (`media`),
KEY `n_voti` (`n_voti`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dump dei dati per la tabella `risorse`
--

INSERT INTO `risorse` (`id_risorse`, `utenti_id`, `url`, `nome`, `lingua`, `media`, `n_voti`) VALUES (1, 1, 'http://www.andreapaiola.it', 'Sito di Andrea Paiola', 'italiano', 0.0, 0);
INSERT INTO `risorse` (`id_risorse`, `utenti_id`, `url`, `nome`, `lingua`, `media`, `n_voti`) VALUES (2, 1, 'http://www.w3.org', 'World Wide Web Consortium', 'inglese', 0.0, 0);
INSERT INTO `risorse` (`id_risorse`, `utenti_id`, `url`, `nome`, `lingua`, `media`, `n_voti`) VALUES (3, 2, 'http://www.html.it', 'HTML.it', 'Italiana', 0.0, 0);
INSERT INTO `risorse` (`id_risorse`, `utenti_id`, `url`, `nome`, `lingua`, `media`, `n_voti`) VALUES (4, 3, 'http://www.gizax.it', 'Daniele Florio', 'italiano', 5.0, 4);

-- --------------------------------------------------------

--
-- Struttura della tabella `utenti`
--

CREATE TABLE `utenti` (
`id_utenti` int(10) unsigned NOT NULL auto_increment,
`name` varchar(30) character set utf8 collate utf8_swedish_ci NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`data_registrazione` int(10) unsigned NOT NULL default '0',
`ultimo_accesso` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id_utenti`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dump dei dati per la tabella `utenti`
--

INSERT INTO `utenti` (`id_utenti`, `name`, `pass`, `data_registrazione`, `ultimo_accesso`) VALUES (1, 'andrea.paiola', 'nonveladico', 1147976952, 1147976970);
INSERT INTO `utenti` (`id_utenti`, `name`, `pass`, `data_registrazione`, `ultimo_accesso`) VALUES (2, 'smal', 'nonveladico', 1147989501, 1147989665);
INSERT INTO `utenti` (`id_utenti`, `name`, `pass`, `data_registrazione`, `ultimo_accesso`) VALUES (3, 'gizax', 'nonveladico', 1148018661, 1148018689);
INSERT INTO `utenti` (`id_utenti`, `name`, `pass`, `data_registrazione`, `ultimo_accesso`) VALUES (4, 'ipa', 'nonveladico', 1148048965, 1148049001);

-- --------------------------------------------------------

--
-- Struttura della tabella `voti`
--

CREATE TABLE `voti` (
`id_voti` int(10) unsigned NOT NULL auto_increment,
`risorse_id` int(10) unsigned NOT NULL default '0',
`utenti_id` int(10) unsigned NOT NULL default '0',
`valore` enum('1','2','3','4','5') NOT NULL default '1',
PRIMARY KEY (`id_voti`),
KEY `risorse_id` (`risorse_id`),
KEY `utenti_id` (`utenti_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dump dei dati per la tabella `voti`
--

INSERT INTO `voti` (`id_voti`, `risorse_id`, `utenti_id`, `valore`) VALUES (1, 4, 3, '5');
INSERT INTO `voti` (`id_voti`, `risorse_id`, `utenti_id`, `valore`) VALUES (2, 4, 3, '5');
INSERT INTO `voti` (`id_voti`, `risorse_id`, `utenti_id`, `valore`) VALUES (3, 4, 3, '5');
INSERT INTO `voti` (`id_voti`, `risorse_id`, `utenti_id`, `valore`) VALUES (4, 4, 3, '5');

[andrea.paiola]

Originariamente inviato da luca200
Senza una subquery temo sia dura.
Prova un po' se non ho lasciato qualcosa per strada

$result = mysql_query("SELECT q1.id_utenti, q1.name,q1.datareg,q1.voti,    
                       count(r.id_risorse) as risorse FROM
                       (SELECT u.id_utenti, u.name, FROM_UNIXTIME(u.data_registrazione,'%d/%m/%Y') as datareg, count(v.id_voti) as voti
                       FROM utenti u 
                       LEFT JOIN voti v    ON u.id_utenti = v.utenti_id GROUP BY u.id_utenti) as q1 
                       LEFT JOIN risorse r ON q1.id_utenti = r.utenti_id 
                       WHERE LOWER(q1.name) LIKE LOWER('$item%') 
                       GROUP BY q1.id_utenti 
                       LIMIT 100"); 

Ho cambiato la group by usando l'id invece del nome perché mi sembra MOLTO più corretto. Per il resto quoto quanto detto da piero sul collate e sul lower

adesso mi da risultati corretti quando l'utente ha inserito voti e invece li sballa quando l'utente non ha ancora inserito voti
per esempio su andrea.paiola mi da 0 risorse e 2 voti quando invece dovrebbe essere 2 risorse e 0 voti


ah ditemi pure come intervenire sul collate e LOWER... cosa e come conviene fare ditemelo voi

[luca200]

Originariamente inviato da andrea.paiola
per esempio su andrea.paiola mi da 0 risorse e 2 voti quando invece dovrebbe essere 2 risorse e 0 voti

...siamo sicuri??? :master:
Provato ad invertirla, a me funziona

[luca200]

Originariamente inviato da andrea.paiola
per esempio su andrea.paiola mi da 0 risorse e 2 voti quando invece dovrebbe essere 2 risorse e 0 voti

Ho replicato il tuo dump e ci ho lanciato sopra la mia query.
Il risultato è 0 voti e 2 risorse.
Almeno copiare....

[andrea.paiola]

Originariamente inviato da luca200
...siamo sicuri??? :master:
Provato ad invertirla, a me funziona

in che senso invertirla? :master:

$result = mysql_query("SELECT q1.id_utenti, q1.name,q1.datareg,q1.voti,    
                       count(r.id_risorse) as risorse FROM
                       (SELECT u.id_utenti, u.name, FROM_UNIXTIME(u.data_registrazione,'%d/%m/%Y') as datareg, count(v.id_voti) as voti
                       FROM utenti u
                       LEFT JOIN voti v    ON u.id_utenti = v.utenti_id GROUP BY u.id_utenti) as q1
                       LEFT JOIN risorse r ON q1.id_utenti = r.utenti_id
                       WHERE LOWER(q1.name) LIKE LOWER('$item%')
                       GROUP BY q1.id_utenti
                       LIMIT 100");
                     
                    if(mysql_num_rows($result) > 0){
                       
                            $risposta = '<table><th>Nome utente</th>
                                         <th>Data registrazione</th>
                                         <th>Numero risorse</th>
                                         <th>Numero voti</th>';
                             
                            while ($row = mysql_fetch_row($result)) {
         
                                $id_utente = $row[0];
                                $nome      = $row[1];
                                $data      = $row[2];
                                $ris       = $row[3];
                                $voti      = $row[4];
                                
                                $risposta .= "<tr>
                                              <td><a href=\"utente_dettagli.php?id=$id_utente\">$nome</a></td>
                                              <td>$data</td><td>$ris</td><td>$voti</td>
                                              </tr>";
                    
                            }
                        $risposta.='</table>'; 

[piero.mac]

pure a me funziona quella che avevo postato prima ....

pero' visto che paiola vorrebbe ottimizzare direi di iniziare dalle tabelle.

in utenti e risorse c'e' il campo n_voti (calcolato) , serve a nulla.

in voti c'e' un doppio legame con utenti_id e risorse_id. Il voto dovrebbe essere relazionato alla sola risorsa.

[andrea.paiola]

Originariamente inviato da luca200
Ho replicato il tuo dump e ci ho lanciato sopra la mia query.
Il risultato è 0 voti e 2 risorse.
Almeno copiare....

a me da 0 risorse e 2 voti!

[luca200]

Originariamente inviato da andrea.paiola

La mia query estrae i voti prima e le risorse poi.
Se tu li tiri fuori a rovescio, è ovvio che poi vedi i dati ribaltati.