[support] couple of issues with an sql query and theming a db query

[Michael Prasuhn]

Double check your indexes, but keep in mind that you can't build a  
cross-table index in MySQL. (Or Postgres for that matter)

If it's still too resource intensive, look at denormalizing the data.

http://drupal.org/project/dna

-Mike

On Sep 10, 2008, at 4:09 AM, Neil: esl-lounge.com wrote:

> Thanks Ivan, my second mail crossed yours. There doesn't seem to be  
> that
> much difference between what you've posted here and what I had in my  
> second
> mail, does there?
>
> I suppose it's an expensive query because it's looking through the  
> whole
> node table, getting all of them, and then sorting them. It will only  
> be in
> one block on the front page, so hopefully it shouldn't cause too many
> issues.
>
> thanks a lot
>
> Neil
>
>
> ----- Original Message -----
> From: "Ivan Sergio Borgonovo" <mail at webthatworks.it>
> To: <support at drupal.org>
> Sent: Wednesday, September 10, 2008 12:56 PM
> Subject: Re: [support] couple of issues with an sql query and  
> theming a db
> query
>
>
>> On Wed, 10 Sep 2008 12:12:27 +0200
>> "Neil: esl-lounge.com" <neil at esl-lounge.com> wrote:
>>
>>> $query= "SELECT node.title, node.nid, node_counter.totalcount FROM
>>> node INNER JOIN node_counter ON node.nid = node_counter.nid WHERE
>>> uid = $uid ORDER BY totalcount DESC LIMIT 5";
>>
>>
>>> which gives me the top 5 viewed pages for each user on their
>>> main /user page. It's very handy and I think I'll use it. Can
>>> someone give me a pointer to go the next step towards my initial
>>> aim, the total of a user's pageviews and then a list of the top X
>>
>> select sum(node_counter.totalcount) from node
>> inner join node_counter on node.nid = node_counter.nid
>> where node.uid=$uid;
>> total page viewed by a user if I interpret it right.
>>
>>> users on a site based on pageviews. I know I need to use SUM in
>>> the sql statement but I don't seem to be able to get the syntax
>>> right.
>>
>> select uid, sum(node_counter.totalcount) from node
>> inner join node_counter on node.nid = node_counter.nid
>> group by node.uid order by sum(node_counter.totalcount) desc limit 5;
>>
>> If I interpret it right.
>>
>> The second query may be pretty expensive.
>>
>>
>> -- 
>> Ivan Sergio Borgonovo
>> http://www.webthatworks.it
>>
>> -- 
>> [ Drupal support list | http://lists.drupal.org/ ]
>>
>
> -- 
> [ Drupal support list | http://lists.drupal.org/ ]

__________________
Michael Prasuhn
mike at mikeyp.net
http://mikeyp.net