Re: Estimate Server Hardware Requirements
To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
* SHOW VARIABLES LIKE 'innodb%';
and surround them with [ code ] and [ / code ]
I don't see any index on ret_operations1 starting with ope_date; I don't know if it would help, but note that GROUP BY starts with ope_date.
It would be better if you could remove these rows from the table (and remove this from the WHERE):
ope_flux IS NULL AND ope_outdated_flag IS NULL
If you need to hang onto those records, they could be migrated to another (similar) table, and do UNIONs for the few cases when you need to see both sets of data.
Which of these is most selective?
((((((((ret_operations1.OPE_PRD_OPERATEUR_NM='orange'
AND ret_operations1.OPE_PRD_NC='e.bytel 60 eur')
AND ret_operations1.OPE_PRIXVENTE=20)
AND ret_operations1.OPE_INC_NUMSERIE='1302297833')
AND ret_operations1.OPE_HEURE>='01:00')
AND ret_operations1.OPE_HEURE<='20:45:59')
AND ret_operations1.OPE_DET_CODEDETAILLANT='11084322')
AND ret_operations1.OPE_OUTDATED_FLAG IS NULL )
AND ret_operations1.OPE_DATE='2009-1-15')
(The next question will be: Do you have an index starting with it?)
Build a summary table for the GROUP BY problem. It would have an INDEX of
(ope_date, ope_prd_operateur_id, ope_prd_operateur_nm, ope_gro_id,
ope_gro_nm, ope_prd_id, ope_prd_nc, ope_det_codedetaillant,
ope_prixvente, ope_tauxtva, ope_prd_prixachat)
The other fields would include
100/(ope_tauxtva+100),
count(*)
The monthly invoice could hit the summary table, and run _much_ faster.
The summary table would be updated every day, inserting new rows for yesterday's data. (This assumes you never go back and modify/delete rows in ret_operations1.)
If the PRIMARY KEY is really a compound key of 3 VARCHARs, then I recommend switching to an INT UNSIGNED AUTO_INCREMENT (and changing the PK to UNIQUE).