RE: [PHP-DB] Another UNION ALL query
-Original Message- From: maarten [mailto:maarten.fo...@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
RE: [PHP-DB] Mysql completing Query fast but mysql->query() takes long time to return even single selects/updates
I see this occurring randomly on different quries with different indexes/tables. It appears like php is taking a lot longer than mysql's transmission time to cache the result be it bool TRUE/FALSE ora select of 1 - 50 records. While it does not happen all the time, since from the MySQL side I can see how long each step was and how long it took to even transmit the results to the php server. I am thinking it a small memory hole we do not normaly see for some reason but there are no sqlng parameters even that seem to be able to help in this situation. Also since this is is not really repeatable as is occurs only sometimes and not with a predictable frequency it would be very hard to do the roll back. Also since its in the mysql class of functions I cant even debug into it to try to see much of anything (since its c++ code not userland functions). David -Original Message- From: Chris [mailto:dmag...@gmail.com] Sent: Sunday, April 11, 2010 6:26 PM To: David Murphy Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Mysql completing Query fast but mysql->query() takes long time to return even single selects/updates David Murphy wrote: > As you can see PHP claims it took 20 seconds for mysql->query() to return > but mysql think is took around 1.0s > > > This is from our application > I enabled profile in mysql to determine why an update took 20seconds. As > you can see MySQL reported no where near that amount of duration took > place. > Is there any way I can dig into php and determine why mysql client libs are > so slow (this is not using mysqlnd but mysql-client-libs on CentOS using > 5.3.2) Is this a one-off thing or is it happening all the time? If it's a one-off thing it could be a spurious result (maybe someone else was doing a mysqldump when your query ran, the dump blocks your query).. What sort of mysql table is it? if it's innodb you can try it in a transaction and roll it back: begin; update blah; rollback; see how long it takes. if it takes a short time in the mysql client, then try it in a php script from your other server. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Mysql completing Query fast but mysql->query() takes long time to return even single selects/updates
As you can see PHP claims it took 20 seconds for mysql->query() to return but mysql think is took around 1.0s This is from our application I enabled profile in mysql to determine why an update took 20seconds. As you can see MySQL reported no where near that amount of duration took place. Is there any way I can dig into php and determine why mysql client libs are so slow (this is not using mysqlnd but mysql-client-libs on CentOS using 5.3.2) 04/06/2010 14:54:54 20.6899s UPDATE `calls` SET `Result`='Busy' WHERE `CallID`='144786' | Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | -- | starting | 0.39 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | checking permissions | 0.08 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | Opening tables | 0.10 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | System lock | 0.05 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | Table lock | 0.06 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | init | 0.36 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | Updating | 0.99 | 0.001000 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | end | 0.23 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | query end| 0.04 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | freeing items| 0.007410 | 0.00 | 0.00 | 4 | 1 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | logging slow query | 0.04 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | cleaning up | 0.04 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | This is to a remote system ( but on same GigE switch), however mysql profiling would log transit type if this was a select . Thanks David Murphy
