Newsgroup: it.comp.software.database
Titolo: (lungo) ottimizzare query con campi DATE mysql

Ciao a tutti,
 
VERSIONE BREVE:
 
sto cercando di ottimizzare i tempi di esecuzione di alcune query su 
una tabella che contiene un campo di tipo DATE eventuali 
suggerimenti/consigli/link su best practices o schema ottimizzati per 
questa tipologia di dati sono ben accette
 
 
VERSIONE LUNGA:
 
ho "ereditato" la seguente tabella
 
CREATE TABLE `products` (
  `file_date` date NOT NULL,
  `supplier_id` varchar(128) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `cost` double(10,2) DEFAULT NULL,
  `pyear` smallint(5) unsigned DEFAULT '0',
  `pmonth` smallint(5) unsigned DEFAULT '0',
  PRIMARY KEY (`file_date`,`supplier_id`),
  KEY `pyear` (`pyear`),
  KEY `pmonth` (`pmonth`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
la tabella contiene ~5 milioni di righe e ogni giorno viene aggiornata
con circa 30 mila righe
 
i campi pmonth e pyear valgono rispettivamente MONTH(file_date) e 
YEAR(file_date) e sono stati creati per evitare di usare le suddette 
function nelle query e nell'ottica di velocizzare l'esecuzione delle 
stesse
 
ho dei problemi di velocità di esecuzione con le seguenti query:
 
-- #1 tutti i mesi disponibili per l'anno selezionato
SELECT DISTINCT pmonth FROM products WHERE pyear=2012;
 
-- #2 tutti i giorni disponibili per il mese e l'anno selezionato
SELECT DISTINCT file_date FROM products WHERE pmonth=1 AND pyear=2012;
 
 
per quanto riguarda il caso #1 cambiando la query in questo modo
dimezzo i tempi di esecuzione ma è ancora troppo lenta
 
-- passa da 20-30 secondi a 10-20 secondi
SELECT DISTINCT pmonth AS month_no FROM products WHERE  file_date >= 
'2012-01-01' AND file_date <= '2012-12-31' ORDER BY month_no ASC;
 
 
per il caso #2 ho risolto (sic!) con la seguente query
 
-- passa da 5-10 secondi a 0.05 :( anche riavviando il servizio
SELECT DISTINCT file_date FROM products WHERE file_date >= '2012-01-01' 
AND file_date <= '2012-01-31'
 
 
facendo dei test ho notato che aggiungendo le seguenti tabelle
 
 
CREATE TABLE `product_id` (
  `supplier_id` VARCHAR(128) NOT NULL,
  `description` VARCHAR(255) DEFAULT '',
  PRIMARY KEY (`supplier_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
 
 
CREATE TABLE `product_cost` (
  `file_date` DATE NOT NULL,
  `supplier_id` VARCHAR(128) NOT NULL,
  `cost` DOUBLE(10,2) DEFAULT NULL,
  PRIMARY KEY (`file_date`,`supplier_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
 
CREATE TABLE `product_date` (
  `file_date` DATE NOT NULL,
  `supplier_id` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`file_date`,`supplier_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
 
 
e popolando le tabella a partire dalla tabella products
 
INSERT INTO `product_id`(SELECT supplier_id, description FROM products 
GROUP BY supplier_id);
 
INSERT INTO `product_cost`(SELECT file_date, supplier_id, cost FROM 
products);
 
INSERT INTO `product_date`(SELECT file_date, supplier_id FROM 
products);
 
 
la seguente query impiega la metà
 
SELECT DISTINCT MONTH(file_date) AS month_no FROM product_date WHERE 
file_date >= '2012-01-01' AND file_date <= '2012-12-31' ORDER BY 
month_no
 
 
rispetto a questa:
 
SELECT DISTINCT MONTH(file_date) AS month_no FROM products WHERE 
file_date >= '2012-01-01' AND file_date <= '2012-12-31' ORDER BY 
month_no
 
la EXPLAIN è praticamente identica non capisco perchè impieghi la metà 
del tempo
 
-- 
hai mai pensato ad un futuro nell'Arma?
si sono liberati dei posti al 5° Barzellettieri
 
 

 
 
ciao,
 
il primo step che farei è creare indici sulle colonne che vengono
interessate da where condition.
Avendo tempo risponderei punto per punto anche alla versione lunga
ma ora mi è impossibile.
 
hint: 
http://www.amazon.com/Relational-Database-Index-Design-Optimizers/
 
dp/0471719994
 
 
ciao

 ma ora mi è impossibile.
 
 
e ora? :D
 
-- 
non c'è un domani
l'ho eroso così tanto che è gia giovedi