vb.org Archive - MyChenQL queries

Page 1 of 2

Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - MyChenQL queries (https://vborg.vbsupport.ru/showthread.php?t=46564)

filburt1

12-11-2002 02:20 AM

MyChenQL queries

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.

Code:

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

12-11-2002 02:37 AM

24 rows in set (3.24 sec)

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

okrogius

12-11-2002 04:30 PM

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

filburt1

12-11-2002 04:31 PM

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

okrogius

12-11-2002 05:20 PM

BTW out of curiousity why do you need this?

filburt1

12-11-2002 05:21 PM

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

okrogius

12-11-2002 05:25 PM

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

12-11-2002 05:26 PM

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

NTLDR

12-11-2002 05:27 PM

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

12-11-2002 05:29 PM

Quote:

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

All times are GMT. The time now is 08:45 AM.

Page 1 of 2

Show 40 post(s) from this thread on one page

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01638 seconds Memory Usage 1,735KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: