top10stats in 1 query?

[HakkieDEV]

Hi all,

I've some top10 statistics on my index.php, in total 5 different ones.

To make it a bit easier, lets call them stats1, stats2, stats3, stats4, stats5.

This means that a query has to be run 5 times, to select the data, then sort them.

I was wondering if something like this could be done in a cronjob (lets say once a day) and that the results of those 5 querys would be inserted in a new table.

The newtable would be something like:
stats1_userid, stats1_username, stats1_posts, stats2_userid, stats2_username, stats2_posts, etc, etc.

While doing this, I could save 4 querys on my index.php, wich is heavily modded and I am desperate to look for something that could be done with less querys.

Is this possible? if yes, how?

[Xenon]

if you are using vb3 you can use the vb cron to fetch the needed data, then put everything into an php array, serialize that and put it into the datastore.
the vb datastore can be fetched without adding extra queries at all ($specialtemplates ;))

[HakkieDEV]

Okay, I figured out a way, but I don't understand that the page isn't showing faster.


OLD-------------------------------------------------------
Page generated in 4.02501 seconds with 43 queries [Server Loads: 0.08 0.07 : 0.15]
OLD-------------------------------------------------------



NEW-------------------------------------------------------
Page generated in 4.06289 seconds with 39 queries [Server Loads: 0.14 0.10 : 0.15]
NEW-------------------------------------------------------

Is it because the new table has 75 rows? :D

Any idea how I could get around this?

[Xenon]

i think there may be something else which takes too much time

[HakkieDEV]

So, 1 query that has to search in 75 rows is normally faster then 5 different querys to do the 75 rows?

[Xenon]

one query doing 75 rows shouldn't take 4 secounds at all ;)

[Marco van Herwaarden]

Well the number of returning rows don't always say something about the number of rows MySQL has to process, select, join and sort, before it can return that number of rows.

A select with a '...FROM singletable WHERE column = 1' that would select 75 out of the 150 rows, would be very fast.

Selecting from multiple big tables, joining on nonunique fields, sorting, etc, still returning 75 rows could be very time consuming.