Another UNION ALL query

I have a 'Highlights' heading on my home page. It is for links to content
on the site ... like specific web pages

I have designed the query below to select the two most popular and least
popular pages used on the site to be the Highlights. (Each time a web
page is accessed user_hits is increased by 1.)

I would like to display a fifth one that is " ORDER BY RAND () LIMIT 1 "
--- Only I don't know how to ensure it isn't one of the four that are
being displayed already. Any suggestions?

Ron



SELECT `highlights`.`reference`, `highlights`.`page_command`,
`highlights`.`page_title` FROM (

( SELECT `reference`, `page_command`, `page_title` FROM `user_pages` WHERE
`include_in_highlights` =1 ORDER BY `user_hits` DESC LIMIT 2 )

UNION ALL

( SELECT `reference`, `page_command`, `page_title` FROM `user_pages` WHERE
`include_in_highlights` =1 ORDER BY `user_hits` ASC LIMIT 2 )

) AS highlights ORDER BY `highlights`.`page_title` ASC


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Another UNION ALL query

Hi,

while I can not help you with your direct question, I don't know of a
way to order randomly and select one you hadn't selected yet in SQL,
there is another way to do this.

With your UNION query your already executing two 'expensive' queries,
the entire table get's ordered (twice) before the limits are applied,
every time someone goes to your website.

Better might be to run just one query:=20
SELECT reference,page_command,page_title,user_hits FROM ... ORDER BY
user_hits

Then use php to select the first 2 and the last 2, and maybe a random
fifth using something like n=3Drandint(2,#rows_returned-2-1) to get the
n'th row. (which will exclude the first 2 and last 2 rows)

regards,
Maarten

On Thu, 2010-04-29 at 05:33 -0400, Ron Piggott wrote:
> I have a 'Highlights' heading on my home page. It is for links to conten=
t
> on the site ... like specific web pages
>=20
> I have designed the query below to select the two most popular and least
> popular pages used on the site to be the Highlights. (Each time a web
> page is accessed user_hits is increased by 1.)
>=20
> I would like to display a fifth one that is " ORDER BY RAND () LIMIT 1 "
> --- Only I don't know how to ensure it isn't one of the four that are
> being displayed already. Any suggestions?
>=20
> Ron
>=20
>=20
>=20
> SELECT `highlights`.`reference`, `highlights`.`page_command`,
> `highlights`.`page_title` FROM (
>=20
> ( SELECT `reference`, `page_command`, `page_title` FROM `user_pages` WHER=
E
> `include_in_highlights` =3D1 ORDER BY `user_hits` DESC LIMIT 2 )
>=20
> UNION ALL
>=20
> ( SELECT `reference`, `page_command`, `page_title` FROM `user_pages` WHER=
E
> `include_in_highlights` =3D1 ORDER BY `user_hits` ASC LIMIT 2 )
>=20
> ) AS highlights ORDER BY `highlights`.`page_title` ASC
>=20
>=20


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Another UNION ALL query

-----Original Message-----
From: maarten [mailto:maarten.foque@edchq.com]
Sent: Thursday, April 29, 2010 10:23 AM
To: php-db@lists.php.net
Subject: Re: [PHP-DB] Another UNION ALL query

Hi,

while I can not help you with your direct question, I don't know of a
way to order randomly and select one you hadn't selected yet in SQL,
there is another way to do this.

With your UNION query your already executing two 'expensive' queries,
the entire table get's ordered (twice) before the limits are applied,
every time someone goes to your website.

Better might be to run just one query:
SELECT reference,page_command,page_title,user_hits FROM ... ORDER BY
user_hits

Then use php to select the first 2 and the last 2, and maybe a random
fifth using something like n=randint(2,#rows_returned-2-1) to get the
n'th row. (which will exclude the first 2 and last 2 rows)

regards,
Maarten

On Thu, 2010-04-29 at 05:33 -0400, Ron Piggott wrote:
> I have a 'Highlights' heading on my home page. It is for links to content
> on the site ... like specific web pages
>
> I have designed the query below to select the two most popular and least
> popular pages used on the site to be the Highlights. (Each time a web
> page is accessed user_hits is increased by 1.)
>
> I would like to display a fifth one that is " ORDER BY RAND () LIMIT 1 "
> --- Only I don't know how to ensure it isn't one of the four that are
> being displayed already. Any suggestions?
>
> Ron
>
>
>
> SELECT `highlights`.`reference`, `highlights`.`page_command`,
> `highlights`.`page_title` FROM (
>
> ( SELECT `reference`, `page_command`, `page_title` FROM `user_pages` WHERE
> `include_in_highlights` =1 ORDER BY `user_hits` DESC LIMIT 2 )
>
> UNION ALL
>
> ( SELECT `reference`, `page_command`, `page_title` FROM `user_pages` WHERE
> `include_in_highlights` =1 ORDER BY `user_hits` ASC LIMIT 2 )
>
> ) AS highlights ORDER BY `highlights`.`page_title` ASC
>
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Its very hard to say which is more effective. For example id both
include_in_highlights and user_hits are index'ed, it would likely take
much more time for MYSQL -> PHP Transimiation of the entire result set than
for you to use a union like this.


Let assume they are index'ed you could do...

CREATE VIEW TopBottomPages AS
( SELECT `id`,`reference`, `page_command`, `page_title` FROM
`user_pages` WHERE `include_in_highlights` = 1 ORDER BY `user_hits` DESC
LIMIT 2 )
UNION ALL
( SELECT `id`,`reference`, `page_command`, `page_title` FROM
`user_pages` WHERE `include_in_highlights` = 1 ORDER BY `user_hits` ASC
LIMIT 2 );

SELECT
`highlights`.`reference`, `highlights`.`page_command`,
`highlights`.`page_title`
FROM
(SELECT * from TopBottomPages)
UNION ALL
(SELECT `id` `reference`,`page_command`,`page_title` from
`user_pages` where `include_in_hightlights` = 1 and id NOT IN
TopBottomPages.id ORDER BY RAND() LIMIT,1) as highlights
ORDER BY `highlights`.`page_title` ASC


Since you already ran TopBottomPages it would be in the query cache for the
NOT IN check. Granted you could do this with a single transaction using
just union's if it innodb by im assuming this might be another engine.

David


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php