Let's suppose the database table is set up in such a way that this "date" field is being indexed for rapid retrieval of specific values, but this index doesn't help for queries that end up having to do some test on every value (e.g. "greater than this and less than that") -- maybe such a query ends up just doing a full-table scan because the index can't be used that way. (Maybe the "explain" facility will make that clear.)

If that's the case, it's possible that the rephrasing suggested by McDarren might save the day, if it allows the query conditions to be tested against the values in the index, rather than the values in the table itself.

But in case that doesn't pan out, it becomes a question of how to specify the query for those 100's of specific values in a more sensible way (i.e. not just making a list of 100's of values conjoined with "OR"). One possible solution would be to create a temporary table (just one column is needed), load it with the target values, and set the query to something like: 

SELECT sum(numlog) FROM table1 WHERE date IN (SELECT date from temptab
+le)

[download]
Don't forget to drop (or at least clear) the temp table when you're done. And bear in mind that you would need to be very careful if this is being done as part of a web app. (You don't want different clients using the same temp table, and you don't want too many clients creating lots of big temp tables at the same time...)

You are using a current version of MySQL, aren't you? The ability to do a sub-query select statement like that has been around since the transition to version 5.

In reply to Re: Perl and MySQL woes by graff
in thread Perl and MySQL woes by cosmicperl

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.