How to fetch the data and display it in a browser simultaneously using PHP and Smarty?

I believe the bottleneck is on the looping over SQL queries. There is a standard way to rank search results on MySQL. You can simply implement full-text search.

First, you need to create a table like search_results:

SQL:

CREATE TABLE `search_results` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `result_title` varchar(128) CHARACTER SET utf8 NOT NULL,
  `result_content` text CHARACTER SET utf8 NOT NULL,
  `result_short_description` text CHARACTER SET utf8,
  `result_uri` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `result_resource_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `result_title` (`result_title`,`result_content`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

You have to insert all relevant data from the table of questions (including questions, subjects, answers, and whatever you want to search through them) into result_title and result_content here, (also update this table whenever it needs to be updated). There is also backtrack pointer to the original record of the corresponding table on result_resource_id. With a pre-defined URI result_uri pointing to the defined URL of the result in your website, you make everything faster. You don't need to create a URL each time.

Now, you can create a simple SQL query for a search query 'question?' in NATURAL LANGUAGE MODE:

SQL:

SELECT `result_title`, `result_content`, `result_uri`
FROM `search_results` WHERE MATCH(result_title, result_content) AGAINST('question?');

You can also add the relevance measurement into your query string. There are other modes for a search like boolean. Read the documents here and find the best solution.

Full-text indexing is faster and also more accurate in these use-cases.

Assuming you want your content to load in the browser while it is still being streamed from the server to the client, if you are using tables - as you do - you may run into the problem of the browser (due to layout issues) not being able to render the table until all data is loaded.

You can see these tips for authoring fast-loading HTML pages and learn about tables in the according section.

Some crucial points:

If the browser can immediately determine the height and/or width of your images and tables, it will be able to display a web page without having to reflow the content. This not only speeds the display of the page but prevents annoying changes in a page's layout when the page completes loading. For this reason, height and width should be specified for images, whenever possible.

And:

Tables should use the CSS selector:property combination:

table-layout: fixed;

... and should specify widths of columns using the COL and COLGROUP HTML tags.

As well as:

Tables are still considered valid markup, but should be used for displaying tabular data. To help the browser render your page quicker, you should avoid nesting your tables.

You might also want to look into methods of streaming output from PHP.

See this question for details.

In general, templating engines do not load content piecemeal - you'd need to send data to the browser in chunks manually, and flush between each bit. Template libraries usually compose the whole document in memory, and then dump it to the browser in one go. It's worth checking the Smarty manual though, just in case.

As an alternative, you could render the page without the large amount of data, and then load it in sections via AJAX. Whilst making, say, 10 AJAX connections serially adds a small extra overhead, it sounds like that will be minimal in comparison to your current rendering time. Even though your total rendering time may be slightly longer, the perceived rendering time for the user will be much faster, and of course they have the benefit that they can see data arriving.

I would kick off the first AJAX operation in jQuery upon domready, and when each completes, it can fire off another request. If your server can answer in JSON rather than HTML, it will allow the server to return a more_available Boolean flag, which you can use to determine if you need to do another fetch.