0

Sorry this may be a noob question, but I don't know how to search about this.

User case
A full-site Search function : when the user input keyword and submit the form, the system should be search in both title & content of forum, blog, products. The search result of all those type of page should display in one single list with pagination. The user can also chose to ordering the result by relevance or recency.

What I did
I am using LMAP. I have data tables for those three page type , and I have make the title & content column as index Key.
I knew that join table is a very bad idea, so I make three separate query for searching the forum, blog, and products. I get all the data into PHP, make them into array, write a function for making a relevance value for every row of search result. For recency, there is "updateDate" column in all those table, so it is ok.
Now I have three nice array. I can implode() them and sort() them easily. I can also render pagination by array_slice().

What make me Frown
Unnecessary performance waste. Yes, what I did is able to do all the things in user case , but --- I don't know how to do (I am a beginner), --- but I am sure the performance can be a lot better.

  1. after the first time query, all the data we need has already get from database. but with my solution, whenever user click to another page of search result, or change the "sort by", the php will start over again, and do the [sql query, relevance function, implode()] again. can I someHow store the result array in someWhere , so the system can save some energy for next user action ?
  2. most of the user will not click on all page of search result. I will guess 90% of user will not keep looking after 10th page, which mean (may be) the first 200 recorded. So, can I do any thing to stop the sql query somewhere instead of all result ?
  3. furthermore, while the traffic grow, there may be some keywords be come common and repeated searching lots of time, what can I do reduce the repeat of those search ? (pls slap me if you think i am thinking too much)

Thank you for reading these, Please correct me if my concept is incorrect, or tell me if I miss something to notice in this user case. Thank you and may God's love be with you.

Edit : I am not using any php framework.

Zitty Yam
  • 135
  • 1
  • 12
  • Search for "caching". But until there *are* a significant amount of visitors (such that it is too slow), don't worry about it. Also a "join table is a very bad idea" is **not** a correct generalized belief; it is *much better to start with proper normalization* and joining tables than to worry about some as-of-yet unknown performance issues. Immediate gains that do not compromise the model can be done simply be *using correct indices* and *using MySQL's Full Text*, although there are other options like [lucene](http://stackoverflow.com/questions/6820527/lucene-or-mysql-full-text-search). – user2864740 Sep 03 '14 at 02:29
  • @user2864740 Sorry can you slow down a little bit ? I wonder "start with proper normalization" is for comman case, not for this case ? Because but there is no relationship between those there page type, They are like module, not contention in column... (except createUser & updateUser). So i will think won't be suggest to join them when select, right ? – Zitty Yam Sep 03 '14 at 02:44
  • You are pulling the entire search result into the browser but javascript is only showing a small set of rows, and the same process is happening on page 2? You should look into SQL paging. – Malk Sep 03 '14 at 02:48
  • oh, I was think "pulling the entire search result into the browser" is not a good idea. Because it making too much traffic to client side, making not friendy user experience. correct me if I am wrong. – Zitty Yam Sep 03 '14 at 02:55
  • push.push.push.push.push.push.push. – Zitty Yam Sep 05 '14 at 03:59

1 Answers1

0

To get you the full story is probably like writing a book. Here are some extracted thoughts:

  • fully blown page indicators cost you extra data set counts - just present "Next" buttons which can be made up by select ... limit [nr_of_items_per_page+1] and then if(isset($result[nr_of_items_per_page+1])) output next button
  • these days net traffic costs are not as high as ten years ago and users demand for more. Increase your nr_of_items_per_page to 100, 200, 500 (depending on the data size per record)
  • Zitty Yams comments work out - I have loaded >10000 records in one go to a client and presented those piece by piece - it just rocks - eg. a list of 10000 names with 10 characters avg makes just 100000 Bytes. Most of the images you get in the net are bigger then that. Of cause there are limits...
  • php caching via $SESSION works as well - however keep in mind that each Byte to be reserved for php cannot be dedicated to the database (at least not on a shared server). As long as not all data in the database fit into memory, in most cases it is more efficient to extend database memory rather than increasing php caches or os caches.
Quicker
  • 1,247
  • 8
  • 16