Take the 2-minute tour ×

I am querying data from a database, and the query clause varies with the day. In other words, I want to get data in one-day unit by counting seconds in unix timestamps.

So, what I did for one-day-query is,

SELECT IDnum, Timestamp FROM table_name
WHERE CONVERT(Timestamp, UNSIGNED INTEGER) >= ($start_tim-86400*($i+1))
  AND CONVERT(Timestamp, UNSIGNED INTEGER) < ($start_time-86400*$i)

because Timestamp attribute in table is varchar(32), I used CONVERT() in where clause to convert it to int, and compare with int type of 86400.

This query clause works so far, but it takes at least 5 mins in that I set ini_set('max_execution_time', 300). And after 5 mins, it showed "Fatal error: Maximum execution time of 420 seconds exceeded in C:\www\LineChartOne\generateJSONdata.php on line 90", and still not complete.

My question is why it need so much time and, because I used function call in the "where clause"? Or something wrong with the "where clause", otherwise it should not run so slowly.

share|improve this question
Consider having MySQL explain what's going on: DESCRIBE SELECT IDnum, Timestamp FROM table_name WHERE CONVERT(Timestamp, UNSIGNED INTEGER) >= ($start_tim-86400*($i+1)) AND CONVERT(Timestamp, UNSIGNED INTEGER) < ($start_time-86400*$i) ^^^^^^^^ That should shed some light on what's taking so much time. –  Madbreaks Nov 14 '12 at 17:33
add comment

2 Answers

I'm guessing that the CONVERT() is super inefficient. Use UNIX_TIMESTAMP() or FROM_UNIXTIME() or date functions like DATE_SUB() or DATE_DIFF() to compare the dates.

If it still takes forever then either there's something wrong and you don't have tens of millions of rows then it is likely that:

  1. Your PHP code is inefficient.
  2. There's more to this query than what you're showing.
  3. Your web and or mySQL servers are under heavy load from other users.


Sweet jesus, the code in your comment makes my brain itch. Also I just now realized that your Timestamp column is a VARCHAR and not a TIMESTAMP. Bad user. Do not store numbers or dates as strings. Go sit in the corner.

  • Option 1: Make it an INT. [works with your existing code]
  • Option 2: Make it a DATETIME. [generally more flexible]

Things your are not getting:

  1. FROM_UNIXTIME() converts an integer timestamp to a DATETIME type. If you feed it a string it will do an implicit conversion. [conversions are slow]
  2. UNIX_TIMESTAMP() takes a date string, DATE, or DATETIME and returns an integer.

The code you gave UNIX_TIMESTAMP(FROM_UNIXTIME(Timestamp)) converts the string to an int to a date back to an int. The documentation is here, read up on it.

The vast majority of the reason why your query takes so long is because mySQL has to convert all of the values in Timestamp from VARCHAR to INTEGER. Change the column type and you can do simple integer comparisons which will be worlds faster.

A much smaller performance gain can be had by pre-computing the expression $start_time-86400*($i+1) in PHP. mySQL should optimize to evaluate only once, but the optimizer can do funky things when your queries become more complex and it might start computing this for each row.

share|improve this answer
Thanks for your help. I changed to: "SELECT IDnum, Timestamp, A, B, FROM krisdata WHERE UNIX_TIMESTAMP(FROM_UNIXTIME(Timestamp)) >= (($start-$incompleteDay)-86400*$i) AND UNIX_TIMESTAMP(FROM_UNIXTIME(Timestamp)) <= $start AND A > 0.0 AND B > 0.0"; It still takes forever. Based on your 3 assumptions: –  lxx22 Nov 14 '12 at 19:23
Based on your 3 assumptions: 1. my php code dies at Fatal error: Maximum execution time of 300 seconds exceeded in C:\www\LineChartOne\generateJSONdata.php on line 107. Line 107 is $results = mysql_query($sqlQuery) so it is still query clause takes much time? 2. All my query condition is posted here, only reply with A B these two short names and about A B they must be greater than 0 I do not know what to do next, thanks a lot! –  lxx22 Nov 14 '12 at 19:31
updated my answer. –  Sammitch Nov 14 '12 at 21:12
add comment

The mysql query parser is struggling with converting those columns, you need to use the correct data types for the values you are storing, ie: int for int, varchar for strings, datetime etc....

share|improve this answer
add comment

Your Answer


