MySQL Forums
Forum List  »  Newbie

Re: Newbie question about correct settings to achieve better performance
Posted by: Rick James
Date: June 08, 2009 08:00PM

SELECT  Time_Stamp, Time_Stamp_ms, SK00_SC_MODA_CH3,
        SK00_SC_MODA_CH4, SK00_LP_Rate, SK00_FL_RateAll,
        SK00_FL_RateOil, SK00_FL_RateWater,
        SK00_HP_Rate, SK00_SC_MODA_CH1, Bias
    FROM  TREND001
    WHERE  ( ( Time_Stamp > _latin1'2009-06-06 22:59:00'
         OR  ( Time_Stamp = _latin1'2009-06-06 22:59:00'
          AND  Time_Stamp_ms >= 100))
        AND  ( Time_Stamp < _latin1'2009-06-07 09:50:47'
         OR  ( Time_Stamp = _latin1'2009-06-07 09:50:47'
          AND  Time_Stamp_ms <= 634))
      AND  Bias = 300 )

      OR  ( ( Time_Stamp > _latin1'2009-06-06 21:59:00'
        OR  ( Time_Stamp = _latin1'2009-06-06 21:59:00'
         AND  Time_Stamp_ms >= 100))
       AND  ( Time_Stamp < _latin1'2009-06-07 08:50:47'
        OR  ( Time_Stamp = _latin1'2009-06-07 08:50:47'
         AND  Time_Stamp_ms <= 634))
      AND  Bias = 360 )
    ORDER BY  Time_Stamp ASC, Time_Stamp_ms ASC

Direct the following discussion at the designers of the software:

I'll try to explain it politely...

It is not a good idea to have a "continuous" value split between two fields and having a compound key on them. Performance will be a problem, not to mention the unreadable nature of it.

Suggest you change Time_Stamp and Time_Stamp_ms to one DOUBLE field with
UNIX_TIMESTAMP(Time_Stamp) + Time_Stamp_ms / 1000
both in the table and in the query.

Or you could use a BIGINT with
UNIX_TIMESTAMP(Time_Stamp) * 1000 + Time_Stamp_ms

Then, I suggest doing UNION instead of OR:
SELECT ... BETWEEN ... AND Bias=300
UNION
SELECT ... BETWEEN ... AND Bias=360

(Or use >= and <= instead of BETWEEN.)

mysql> select UNIX_TIMESTAMP('2009-06-07 09:50:47') + 634/1000;
+--------------------------------------------------+
| UNIX_TIMESTAMP('2009-06-07 09:50:47') + 634/1000 |
+--------------------------------------------------+
|                                  1244393447.6340 |
+--------------------------------------------------+

You cannot fix the performance if you do not have access to the schema, the INSERTs, and the SELECTs.

Options: ReplyQuote


Subject
Written By
Posted
Re: Newbie question about correct settings to achieve better performance
June 08, 2009 08:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.