Re: Very long query doubt... (Monster query...)

[Rick James]

First, let's make it readable:

select  sum(sells),sum(quantity),sum(dto * quantity)
    from  table
    where  data >= "2008-01-01"
      and  data <= "2009-01-01"
      and  ( ( article_name="A1"
      and  class="N" )
      or  ( article_name="A2"
      and  class="N" )
      or  ( article_name="A3"
      and  class="N" ) ... )

Bug? Do you really want all of 2008, plus one day from 2009?

Start by rearranging the WHERE into an equivalent format:

select  sum(sells),sum(quantity),sum(dto * quantity)
    from  table
    where  data >= "2008-01-01"
      and  data <= "2009-01-01"
      and  class="N"
      and  article_name IN ( "A1", "A2", "A3", ... )

Then make sure you have
INDEX (class, article_name),
INDEX (data)
I'm not sure which one will be more useful.

To go further, please provide
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]

"Monster" is more than, say, 6 hours. Or more than, say, a billion rows. But I'll admit it is pretty big for a newbie.

Hmmm... Perhaps it's not the same 'class' for each? Then about the only thing worth doing is
INDEX(data).

If this is going to grow, with more data coming in, then I recommend "summary" table(s). That pushes the execution cost away from the report queries onto building the summary tables.

CREATE TABLE summary
   data DATE
   class ...
   article_name ...
   sum_sells INT
   sum_quantity INT
   sum_prod DOUBLE
INDEX(data, class, article)
);

and use SUM(), GROUP BY, etc to add new rows each day.