MyChenQL queries

[filburt1]

Or MySQL in this case...any way to optimize this query so it doesn't take so obscenely long to execute? What it's supposed to do (and what I think it is doing) is return the number of posts that were created durning each of the 24 hours in a day.

mysql> SELECT HOUR(FROM_UNIXTIME(dateline)), COUNT(*) FROM post GROUP BY HOUR(FROM_UNIXTIME(dateline));
+-------------------------------+----------+
| HOUR(FROM_UNIXTIME(dateline)) | COUNT(*) |
+-------------------------------+----------+
| 0 | 941 |
| 1 | 1006 |
| 2 | 560 |
| 3 | 597 |
| 4 | 534 |
| 5 | 533 |
| 6 | 664 |
| 7 | 888 |
| 8 | 978 |
| 9 | 1225 |
| 10 | 1670 |
| 11 | 2017 |
| 12 | 1707 |
| 13 | 1886 |
| 14 | 1980 |
| 15 | 2658 |
| 16 | 3196 |
| 17 | 4150 |
| 18 | 4418 |
| 19 | 3134 |
| 20 | 3503 |
| 21 | 3523 |
| 22 | 2600 |
| 23 | 1595 |
+-------------------------------+----------+
24 rows in set (1.72 sec)

[JulianD]

24 rows in set (3.24 sec)

A little slow... hehehe I'm curious about how to optimize this query too :)

[okrogius]

Your best bet is to run something like "SELECT dateline FROM post" and then do the sorting of the reuslts with php.

[filburt1]

Problem is that will return almost 50,000 rows and make PHP have to do all the work :(

[okrogius]

BTW out of curiousity why do you need this?

[filburt1]

For a mod/hack I'm making that will show a graph of posts per hour.

[okrogius]

I'm assuming this is some sort of a statistical thing. Just create a cronjob that does this slow query once every 12 hours (or osmething like that) and stores the results. Whenever someone loads the stats page show the stored results with something like "last updated at 12 pm today".

[filburt1]

Hmm, good idea although I would have prefered something realtime :(

[NTLDR]

Perhaps an index on the dateline field may speed this up, just a thought, I've no real idea if that will help or not without trying it.

[okrogius]

Originally posted by filburt1
Hmm, good idea although I would have prefered something realtime :(

Well you can run the cronjob every 30 mins or every hour if you want to. :p

[filburt1]

Maybe there's some way to split this into two queries...somehow that could help?

[okrogius]

Originally posted by NTLDR
Perhaps an index on the dateline field may speed this up, just a thought, I've no real idea if that will help or not without trying it.

It will speed up this query, but it will slow down all insert/update/remove queries on the post table (done by things such as posting a new thread or a reply).