[Mysql 4.1.22][PHP 5.2.5] upgrade a php5 mysql4 query lenta

[pyotrex]

Ciao a tutti ho questa tabella che ha 27380 record

CREATE TABLE `dyn_carrello` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_utente` int(5) unsigned NOT NULL default '0',
  `numero` varchar(50) NOT NULL default '',
  `sconto_pianificazione` float(5,2) unsigned NOT NULL default '0.00',
  `sconto_ruolo` float(5,2) unsigned NOT NULL default '0.00',
  `sconto_qta` float(5,2) unsigned NOT NULL default '0.00',
  `sconto_discrezionale` float(5,2) unsigned NOT NULL default '0.00',
  `prezzo` float(8,2) unsigned NOT NULL default '0.00',
  `qta` int(10) unsigned NOT NULL default '0',
  `id_ordine` varchar(11) NOT NULL default '',
  `nota_riga` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=256009 DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=256009 ; 

precedentemente usavo php4 e mysql3 e il campo id_ordine era un INT e tutto filava liscio, poi per esigenze imposte da modifiche richieste, il campo id_ordine è diventato un VARCHAR, php5 e mysql4, eseguendo questa query

SELECT dyn_catalogo . * , dyn_carrello.qta, dyn_carrello.nota_riga
FROM dyn_carrello
INNER JOIN dyn_catalogo ON dyn_carrello.numero = dyn_catalogo.numero
WHERE dyn_carrello.id_utente = '7'
AND dyn_carrello.id_ordine = '0'
ORDER BY dyn_catalogo.codice 

che per altro restituisce un record la query diventa talmente lenta da restituire Fatal error: Maximum execution time of 30 seconds exceeded in... ho provato anche per curiosità a riportare il campo id_ordine a int, ma non cambia nulla, l'unica cosa che rende la query gestibile è la rimozione di ORDER BY che però a me serve, tengo a precisare che tutto gira in locale sul mio portatile, come detto sopra, la stessa query su php4 e mysql3 è una scheggia, a questo punto mi e vi chiedo, c'è qualcosa che non va nella mia installazione php-mysql oppure le caratteristiche dell'ambiente aggiornato php-mysql richiedono delle modifiche nella query?

[Whitecrowsrain]

il problema potrebbe essere quel "dyn_catalogo .*", quanti campi ha dyn_catalogo?
ti servono veramente tutti?

altrimenti potresti scindere in 2 la query trovando prima il dyn_carrello.numero e poi vai sul dyn_catalogo...

vedi come va! e facci sapere

[ntd]

Posta anche la struttura di dyn_catalogo, altrimenti si brancola nel buio.

[Whitecrowsrain]

Originariamente inviato da ntd
Posta anche la struttura di dyn_catalogo, altrimenti si brancola nel buio.

si forse è meglio!

[pyotrex]

CREATE TABLE `dyn_catalogo` (
  `id` int(7) unsigned NOT NULL auto_increment,
  `id_produttore` int(5) unsigned NOT NULL default '0',
  `id_padre` int(6) unsigned NOT NULL default '0',
  `id_file` int(7) unsigned default NULL,
  `numero` varchar(50) default NULL,
  `codice` varchar(50) default NULL,
  `ean` varchar(50) default NULL,
  `qta` int(6) unsigned default NULL,
  `evidenza` int(1) unsigned NOT NULL default '0',
  `offerta` int(1) unsigned NOT NULL default '0',
  `novita` int(1) unsigned NOT NULL default '0',
  `classe` char(2) NOT NULL default '',
  `sort_c` int(7) unsigned default NULL,
  `sort_p` int(7) unsigned default NULL,
  `selettore` int(1) unsigned NOT NULL default '0',
  `confezioni` int(4) unsigned default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=11593 ; 

475 record

[ntd]

Originariamente inviato da pyotrex

SELECT dyn_catalogo . * , dyn_carrello.qta, dyn_carrello.nota_riga
FROM dyn_carrello
INNER JOIN dyn_catalogo ON dyn_carrello.numero = dyn_catalogo.numero
WHERE dyn_carrello.id_utente = '7' AND dyn_carrello.id_ordine = '0'
ORDER BY dyn_catalogo.codice

Probabilmente hai perso tutti gli indici durante il trasferimento del database perché nelle strutture non ne vedo neanche uno.

Comunque, a spanne, per ottimizzare la query che hai descritto devi indicizzare i seguenti campi:

in dyn_carrello:
  numero
  id_utente
  id_ordine
in dyn_catalogo:
  numero
  codice

Inoltre, dyn_carrello.id_utente è numerico, quindi non devi metterlo fra apici.

Se vuoi ottimizzare questo database, considera seriamente di cambiare i vari id in valori numerici e, se non possibile, di usare almeno CHAR invece di VARCHAR.

[pyotrex]

Originariamente inviato da ntd
Probabilmente hai perso tutti gli indici durante il trasferimento del database perché nelle strutture non ne vedo neanche uno.

in entrambe le tabelle ci sono "solo" i campi id dichiarati come primary key, in automatico dovrebbero essere anche index, correggimi se sbaglio.

Comunque, a spanne, per ottimizzare la query che hai descritto devi indicizzare i seguenti campi:
codice:

in dyn_carrello:
numero
id_utente
id_ordine
in dyn_catalogo:
numero
codice



Inoltre, dyn_carrello.id_utente è numerico, quindi non devi metterlo fra apici.

Se vuoi ottimizzare questo database, considera seriamente di cambiare i vari id in valori numerici e, se non possibile, di usare almeno CHAR invece di VARCHAR.

ok

ma volevo anche capire come mai con mysql 4.1 e php 4 su server web la query è velocissima, in locale con mysql3 e php4 anche, mentre sempre in locale con mysql4 e php5 diventa lentissima, io in php.ini (php5) ho attivato l'estensione mysql e mysqli, anche se in questo codice uso mysql

[ntd]

Originariamente inviato da pyotrex
in entrambe le tabelle ci sono "solo" i campi id dichiarati come primary key, in automatico dovrebbero essere anche index, correggimi se sbaglio.

Quando esporto la struttura, io vedo tutti campi indicizzati. Per esempio, in questa mia tabella il campo _creation è indicizzato:

CREATE TABLE IF NOT EXISTS `ntd_request` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `_creation` date NOT NULL,
  `contact` varchar(50) collate utf8_bin NOT NULL COMMENT 'category=required',
  `subject` varchar(50) collate utf8_bin NOT NULL COMMENT 'category=required',
  `content` varchar(200) collate utf8_bin NOT NULL COMMENT 'category=required|widget=textarea',
  PRIMARY KEY  (`id`),
  KEY `_creation` (`_creation`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=0 ;

Originariamente inviato da pyotrex
ma volevo anche capire come mai con mysql 4.1 e php 4 su server web la query è velocissima, in locale con mysql3 e php4 anche, mentre sempre in locale con mysql4 e php5 diventa lentissima, io in php.ini (php5) ho attivato l'estensione mysql e mysqli, anche se in questo codice uso mysql

Non è il PHP, ci metterei la salciccia sulla graticola. Secondo me, come sopra accennato, hai un problema di indici nelle varie versioni di MySQL. Puoi verificarlo facilmente con phpMyAdmin (se puoi usarlo) o similari.

[pyotrex]

Ho capito perfettamente il discorso degli indici, questa è l'esportazione delle 2 tabelle sul server web fatta tramite phpadmin, qui c'è mysql4 e php4 e la query è veloce

CREATE TABLE `dyn_carrello` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_utente` int(5) unsigned NOT NULL default '0',
`numero` varchar(50) NOT NULL default '',
`sconto_pianificazione` float(5,2) unsigned NOT NULL default '0.00',
`sconto_ruolo` float(5,2) unsigned NOT NULL default '0.00',
`sconto_qta` float(5,2) unsigned NOT NULL default '0.00',
`sconto_discrezionale` float(5,2) unsigned NOT NULL default '0.00',
`prezzo` float(8,2) unsigned NOT NULL default '0.00',
`qta` int(10) unsigned NOT NULL default '0',
`id_ordine` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=320993 ;

CREATE TABLE `dyn_catalogo` (
`id` int(7) unsigned NOT NULL auto_increment,
`id_produttore` int(5) unsigned NOT NULL default '0',
`id_padre` int(6) unsigned NOT NULL default '0',
`id_file` int(7) unsigned default NULL,
`numero` varchar(50) default NULL,
`codice` varchar(50) default NULL,
`ean` varchar(50) default NULL,
`qta` int(6) unsigned default NULL,
`evidenza` int(1) unsigned NOT NULL default '0',
`offerta` int(1) unsigned NOT NULL default '0',
`novita` int(1) unsigned NOT NULL default '0',
`classe` varchar(2) NOT NULL default '',
`sort_c` int(7) unsigned default NULL,
`sort_p` int(7) unsigned default NULL,
`selettore` int(1) unsigned NOT NULL default '0',
`confezioni` int(4) unsigned default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1379 ;

[ntd]

Non so che dirti, anche quest'ultimo non ha indici e non è ottimizzato. Ha di diverso che parecchi id sono numerici e la tabella dyn_catalogo è molto più piccola (e quindi è naturale sia più veloce).

Originariamente inviato da ntd
Comunque, a spanne, per ottimizzare la query che hai descritto devi indicizzare i seguenti campi:

in dyn_carrello:
  numero
  id_utente
  id_ordine
in dyn_catalogo:
  numero
  codice

Ma hai problemi ad aggiungere questi indici? Una volta fatto, prova questa query direttamente da phpMyAdmin e dimmi quanto tempo ci mette:

SELECT dyn_catalogo.*, dyn_carrello.qta, dyn_carrello.nota_riga
FROM dyn_carrello INNER JOIN dyn_catalogo ON dyn_carrello.numero = dyn_catalogo.numero
WHERE dyn_carrello.id_utente = 7 AND dyn_carrello.id_ordine = '0'
ORDER BY dyn_catalogo.codice