On 9/21/07, AD7six <[EMAIL PROTECTED]> wrote: > > > > On Sep 21, 4:57 am, "Humble Groups" <[EMAIL PROTECTED]> wrote: > > Background > > > > I have scenario where I have 15000+ rows of messags in Mysql, I > > present them with pagination. It works well till 10 pages, if the user > > suddenly press the last page, Mysql takes lot of time, > > A database will be inevitably slow(er) when running > > SELECT * from aTable LIMIT *bigNumber*,numberOfResults. > > emphasis on the bigNumber. > > You might want to consider, if it is possible, changing your logic > such that you can achieve: > > SELECT * from aTable WHERE sequence > 15000 LIMIT 0,numberOfResults. >
Hmm.. this also a good idea, but to figure out the sequence for the page would be a problem, since I don't have a continuous sequence no, the number might not be in the order. Even if i create one, if I do delete/insert, it may go out of sequence. Probably I can run a nightly job to maintain the page no and start end message no, instead of doing caching elements. After my experience with the shared hosting, probably caching might be a better idea. Thanks for the article link. I saw this post yesterday and thought that mysql is reporting wrong number of rows scanned and my query is efficient. http://www.mysqlperformanceblog.com/2006/07/24/mysql-explain-limits-and-errors/ > Or any other change such that you are modifying the condition and not > the number of pages. > > If you wanted to stick with your cachable elements aproach, I think > the mini-controller idea which is listed in the > http://groups.google.com/group/cake-php/web/frequent-discussions could > be adapted to do what you are asking (look at how it generates the > cache files, for the data). > > hth, > > AD > > > > I figured out > > that it is due to my shared hosting server is under heavy load. > > Either I do something or host is somewhere else. I want to take the > > challenge. > > > > Since I provide just pagination, no sorting based on other columns, > > after 2-3 pages, the page will always display the same set of > > messages, I am thinking of caching them and serve it instead of > > hitting the database. > > > > I am planning to run a process which will select the rows from the > > table pagewise (15 messages) and create static cache element and keep > > it ready instead of creating on demand (I can run at night when the > > load is low, instead of creating on demand on day time when the server > > load is high). > > > > How can I create more than one cachable elements from controller. ? > > > > The code might look like this, need to create elements like > > elements_project1_index_00001_000015, etc., > > > > controller > > > > function create_static_pages() { > > for ($i = 1 ; $i <= $pages - $this->index_threshold; $i++) { > > > > $messages = $this->Post->findAll(null, null, "posted_on ASC", > > $this->page_size, $i, null); > > $view->element('index-page', array('cache' => '+365 days', > > 'plugin' => $this->group_name."_" > > > > .$messages[0]['Post']['id']."_".$messages[count($messages) - > > 1]['Post']['id'])); > > } > > } > > > > How can I force creating all cacheable elements in tmp/cache/views ? > > The above $view->element doesn't seem to work. > > > > MySQL database is already disabled due to have load, I have to make > > this work in 2-3 days. > > > > Please advise. > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
