MySQL Forums
Forum List  »  General

Re: group by / order by problem (how to sort before group)
Posted by: Markus Krebs
Date: January 15, 2007 12:22PM

thanks for the fast reply.

the reasons for implementing this as a physical table are as follows:

1. book titles are rarely added to the "bookstore"
a cronjob updates the result table every 5 minutes.
2. it would be getting called quite often on a busy database server
3. i am a performance purist :)
4. the bookstore is only an abstraction of the real application.
in real, the products (titles) from the results table are union selected,
beacuse not all products have prices and those should be on the bottom
of the result set, without the results beiing sorted by price.

its a "latest product" of ten companys overview in a webapp on the index page.
besides that, multiple different frontend websites feature different groups of companys managed by one backend. so every frontend gets a quick-results table of its own.

the response times of the different index pages are a lot better than selecting live data, because the results quickly fall out of the querycache because there are so many different queries going down on the server in a short time. thats the price of many external links to the webserver...

thanks for the solution :)

edit: i managed to implement it sucessfully. the resulting query covers three joins (inluding the self join of your solution) and executes in 0,02 seconds.

thanks too for the valuable link you posted!
cu,
Markus



Edited 1 time(s). Last edit at 01/15/2007 01:10PM by Markus Krebs.

Options: ReplyQuote


Subject
Written By
Posted
Re: group by / order by problem (how to sort before group)
January 15, 2007 12:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.