Hello all,
For those that may not know me, my name is Sami and I'm one of the Google Summer of Code lot. I am working on improving the search pages for Geeklog. Unfortunately I had a late start on coding due to exams, and as this was my last year at University I was especially busy . So I really made a start on things earlier last week.
I plan to start off by revamping the results page. This will be done by placing all the results into a single table that has fixed headings. I will then work on improving the query times on large databases and also making the most relevant results appear first. So hopefully when a search is performed the user will be presented with a simple results table and the result they want so that they can quickly navigate to the relevant page. Once this is done, Ajax will be used to allow users to reorder the results and move though pages without having to reload the entire page. Then as an extra feature a tag cloud will be implemented.
So I have broken down the project into three tasks:

1. Place all the results into a single table, with the most relevant results first.
   
2. Increase the responsiveness of the page by optimizing the SQL query times and using Ajax.
   
3. Implement a tag cloud.
   

In the future I will post up more details on how I plan to carry out each task, but for now I just want to focus on the first task.

Single Results Table
At the moment when a search is conducted each plugin returns an instance of the Plugin() class which contains the heading names and some results, which are based on the number of results to show per page and the page number. To combine all the results into a single table was relatively simple, just don't print the bits between each table (the table title and heading). The tricky part was finding similarities in the headings so that the right details appear under the correct heading.

Progress: Done!! Click here to view a screen shot.

Plugin Search API
The API in the new system will work by having plugins return a search query. Once all the queries are returned they will be appended to each other using UNION ALL, then ordering and the limits will be added. So essentially there is only one query that's executed, which will look something like this:

(SELECT id, hits ... FROM ... WHERE ...) UNION ALL (plugin2_query) UNION ALL (plugin3_query) ORDER BY hits LIMIT 0, 10;

By using UNION all the queries _MUST_ have the same column names, so the SELECT part needs to have the following headings: id, nice_name, plugin_name, title, description, date, user, hits, url.
Here is an example of the select statement for the Stories:

These heading and the structure may be subject to change, this is just what I have working at the moment.
My idea for the description column is to have a short description of the item centred around the search term, for example if the search term is 'geeklog' then the text that appears in the column may be '...release candidate for geeklog 1.5.0 is now...' with the word 'geeklog' highlighted.
I am particularly interested to hear what plugin developers think about this API compared to the previous one. It should be fairly easy to upgrade plugins as a query is already being used, they just have to return it instead of processing it. The only problems that I foresee is when a plugin does not have a record for how many hits an item has such as FAQ or the Calendar. In these cases the plugin will have to return the value 0.

Progress: Its been implemented, the column heading are not fixed yet and are subject to change.

Ordering the Results
By clicking the column heading will allow the user to sort each column in ascending or descending order. By default the results will be ordered by the 'hits' column, this will allow the most popular result to appear first. But to allow the results table to be fair amongst all plugins the results will work on a ratio basis, where each plugin will have a rank. Some plugins have less of a place in the results table where are others should dominate the table. For example users often want to search for forum posts or stories but are less likely to want to find anything in the comments sections. So by applying a rank to each plugin they will return a varying number of results. The ranks will be from lower priority, 1, to higher priority, 5. Below is an example of a Geeklog system with 3 plugins and a results page that displays a total of 50 results.

Progress: This part of the project is still in its infancy, and so far has not been implemented.

Expandable Information
As some plugins have a need to return more information but now cant due to the fixed table headings... fret not there is still hope! Each row will be made to expand to show more information on that result. This will need an extra function added to the search API, plugin_searchdetail_{my plugin}. The function should return more details on a search result it will be passed a single 'id' that will identify the result. I have a feeling that to start off with the extra details may just be a full story, comment or forum post. But this will allow plugins to have a place in the search page and hopefully some people can experiment with returning different types of details.

Progress: Its been implemented but still needs a lot more work and testing. Click here for a screen shot.

Backwards Compatibility
This has already been briefly discussed in merging the tables into one, but lets recap. Plugins that use the old system will still work, they may not be as affective in the new system but hopefully the plugin developers can make updates to accommodate these changes. Similarities will be drawn from the returned Plugin() instance so that the information appears under the correct heading. When a user expands a row of a result that is using the old system then a simple message will be displayed, some thing like 'No further details available...sorry!' The awkward part is merging the result set from the old system with the new and sorting them. The way its working at the moment is by executing the query and storing the result in an array, that array is then merged with the results from plugins using the old system and sorted programmicly. Ideally when all the available plugins are using the new system there should be huge improvements.

Progress: Complete...I think, needs testing.

Updating Plugins
The new search system would be a bit useless if the core plugins didn't support it. So I will be updating the main plugins, so far I have been working with the Stories and Comments. My main aim at the moment is completing the other parts of this project. Once they are done I will look into updating the plugins that come with GL such as Static Pages, Calendar and Links. I will be here also to support the plugins developers should they have any questions or requests.

Progress: Stories and Comments pretty much done. Static Pages, Calendar and Links still to do.

Why? That's sounds terrible. I like the separate listing since having separate blocks allows me to scan down the page faster, skipping sections I don't need.

OMG! That is possibly the worst query to ever send to a database. There could 10-15 different union'd selects. Show that to a professor with any database knowledge and ask him why. Can anyone imagine what this query would do on Groklaw?

In RDBMS's unions are performed by creating a temp table. So every search requires creation and destruction of a temp table with your plan. Not good. On a heavily used site, multiple people performing searches could cripple the sql engine. On a moderately used site, sql requirements could get people kicked off their shared host.

What if my plugin contains two different searchable objects? Do I pass back this query?

And then your code, being written defensively turns that into
Ugh.



How in the world are you going to write that in SQL?

And that is inside a super-select with a dozen UNIONs??? Are these searches also regexes?

Not processing the query means I can't optimize it for my data. If my data doesn't conform to your query, then searching my plugin is sub-optimal.

What I want is the stupid class called "plugin" to be renamed "searchresults". That would be an improvement.

This is well meaninged, but I think more time spent letting the user decide exactly where to search would be better time spent. Instead of a dropdown list of plugins to search, turn it into a field of checkboxes so I can search just forums and stories if I want.

Hope I didn't come across too harshly. You wanted feedback.

No, it makes perfect sense.

When people are looking for information on "X", they don't care if that information is in a story, a comment, a forum post or whatever. Your average user doesn't (and shouldn't) know that Geeklog divides the content by plugins.

bye, Dirk

Ever use Google? If I search for "rabbits", I don't see a separated list of items. The whole point of the search revamp project is to start to move away from such a hard coded and rigid layout mechanism and move towards something that is flexible and customizable by the admin of the site. Order by relevance is much more useful to the average surfer than order by plugin.





Sami and I have talked about a few approaches that can eliminate the idea of using a UNION. Question becomes: On a system with, say, 15 plugins -- what's faster? UNION or 15 separate calls returned into an array, merged and then sorted? The latter is probably the final solution. However... I'm curious. What do you suggest?




A query in the portal will always have a "stock" set of attributes associated with it -- such as what is there now (Date range, keyword, plugin, author etc etc). Not sure what you're after with respect to optimizing queries?

Searching is never good especially when you need to cross so many tables. I think the proposed approach is fine. My only recommendation is to please factor in scaling. There aren't many Geeklog sites where searching is truly a problem but for the ones it is, it is a big problem. One of the ways to make this scale for larger sites is to have two pools of database servers, one pool for serving site content, the pool for searching only. With a little replication on the backend this really helps a ton because now you don't slow down a site with expensive search queries.

That said, my only recommendation is to simply have a few configuration settings that allow for the setup of a different set of DB connection parameters for the search pool and then make the 1.x DB plumbing work with these settings.

Obvious improving the performance of searching needs to be priority but if you also allow for a second pool of database servers it allows the Groklaw's of our community the ability to better scale without having to make any changes to the codebase.

--Tony

---

The reason people blame things on previous generations is that there's only one other choice.

How about making the search results formatter an extendable class or atleast a function that could easily be replaced. Having a way for GL to change the results layout formatting or offer it as a user setting. There may be a google layout, std layout with source groupings and others...

Seeing views and author may not be useful and while sites can alter the template files and strip that out, it's not as easy as having configurable options. Google does not show you that info, but some sites will still want a structured listing by plugin.

Plugins often have a search inside the applications like 'forum' and it would be nice to be able to call the new search API but use a custom formatter to present the results inside the plugin.

Also, as part of the extended search, if I do a search and select just 1 plugin, do I use the GL default search results view or the plugins custom results formatter - this could be a plugin option.

If plugins return extra fields, they should be able to use this extra data in their custom formatter functiion.

---

Geeklog components by PortalParts -- www.portalparts.com

Did you run simulations? Did you try it with several large tables? Did you try it with multiple simultaneous searches? My suggestion remains don't do a big union.

The order by and limit on the end is the real killer:

(SELECT id, hits ... FROM ... WHERE ...) UNION ALL (plugin2_query) UNION ALL (plugin3_query) ORDER BY hits LIMIT 0, 10;

Someone paging through results is going to send that terrible query to the database multiple times.

How about putting together a sample query with 5-6 unions and asking for advice over at mysql.com? I'm curious what they would say.

Feature: I was expecting to be able to add search criteria when searching a specific plugin.

Yeah -- Sami will be implementing a formatter class for the search results. Allows the admin/maintainer be able to customize the output.

I think any constructive ideas here will only make the final search product that much better for all parties.



Indeed -- this is why the array approach *may* be better and perhaps avoid any multi-database issues that may arise (This is my reasoning behind an array approach vs. pure SQL). A plugin passing BACK either the single query and/or an array of results can at least allow the search API to allow multiple different output columns and only use those that apply to the desired output.

Ok there seems to be a lot of discussion about plugins returning a query instead of an array of results. Let me explain why I think this is a good idea and how I came up with it. While looking through the plugin_dopluginsearch_() function in plugins I kept seeing the same thing, and it seemed there was a lot of redundant code that bloats the system affecting the performance. Pretty much all plugins went through the same process:

I have looked through the following plugins: Story, Comments, Static Pages, Links, Filemgmt and Forums.
They all follow the same or similar patterns making a lot of redundant code. For example extracting the username of the author takes another query, the number or queries is reduced by storing the username and id in an array. But when the plugin finishes executing, the array is lost and the next plugin has to build it again. So it occurred to me why not make the process a lot simpler and perform the query processing outside the plugin, so long as we know the heading names we can do what we want with the query. Also checking if a plugin should perform the search should really be done in the core. So essentially we can reduce the processing a plugins has to do to just this

This is far simpler to work with in the core and improves the performance a little.
There can also be a huge security advantage by performing the queries in the core. Before a query is executed it can be checked for an SQL Injection attack, this means if a plugin developer didn't pay much attention to preventing these kinds of attacks it can be avoided in the core. One way of doing so is by using the PREPARE statement then placing the search item into to the query. But this could get a tad bit complicated so I am not proposing I do it but at least the option is there should someone choose to implement it. This flexibility is due to the core having access to the query.

Essentially the main aim of the new API is to remove the un-necessary processing from the plugin to the core, where it can be controlled and optimized accordingly, it also adds security and standardization. If anyone has any serious objections to this then present them, but please back them up. Don't forget that, for the time being, developers will still have the option to return a Plugin() instance due to the backwards compatibility support.

As for the UNION malarkey, this was just an idea I had at the time and what has worked so far. Optimization is not really my priority at the moment (look at the three tasks at the start of my first post). At this point I am concentrating on the look and feel of the search page. But as this caused some debate I may as well talk a bit about my plans on the optimization. By having each plugin return the query it allows the core to optimize the query as much as it wants, it could perform one query after the other appending the results to an array then processing it. It could join them up into a single query and do the whole lot at once, if thats too slow it could add limits onto each query then union them. If we really wanted to we could even perform each query in a separate thread then process the results. There really is a lot of flexibility here. The best way will only be found through testing. Then any further optimization is down to the individual queries. I have already mentioned I will be upgrading the queries in the main plugins and I hope to do this by getting rid of the LIKE and OR clauses and replacing them with FULLTEXT and...your gonna hate this but UNION, hehe. So for the Groklaw's amongst us (that only use the main plugins) there will be huge improvements to the query times. Of course there will also be fall-back options for database servers that don't support full text. This is the last word I have to say on this topic until I complete the first task!

Tony, I am really interested in your idea. How could this be implemented though? Would both databases be exact copies of each other, and searches only be performed on one? Just thinking out loudly here, how about having extra admin pages for the optimization, allowing the admin to select which database to perform individual queries on. ie perform story and comment searches on server x, perform forum searches on y etc. Is this the sort of thing you are talking about?

Blaine, Randy and I have already been discussing ways of implementing this, I have got a small mock up of a class, but its a bit sketchy at the moment and really just a concept. But from what I gather the feature should be configurable by an admin that does not want to mess around with templates. So adding admin pages to change the style of the search page, eg Google layout, table format, etc.
The aim of the class is to work something like this

This is really just a concept and still needs work. But is this something that would be appealing to users and admins?

Geeklog links against the mysql interface, not the mysqli interface and cannot perform prepared statements. Very few shared hosts provide PHP with the mysqli library. Also, many shared hosts still only provide access to mysql 4.1.
I'm confused. Chrome on a search page should not have higher priority over the speed of search. Optimization should always have precedence over look and feel when you are working on a search page. Google's main search page is boring and bland and yet Google became what it is because they spent a lot of time making search as fast as the could.

I didn't say I had plans on implementing it, I said if it was to be implemented it would be relatively simple, pass a '?' as the search term to a plugin, when the plugin returns the query perform the substitutions. Just because mysqli is not supported by all hosts does not mean everyone has to suffer. It could be set as a configurable option.

I have already done some work on SQL optimization in previous projects so this is a strong area. Just off the top of my head you can make the query times a lot faster by removing the wild card at the beginning of every LIKE statement, look at the queries all the plugins use, they all have a wild card at the start. Doing that will instantly improve query times. Check out numbers 8, 18 and 20 here. The reason I am leaving the optimization till later is that I need to know the code base I am working with, eg how plugins interact with the core. I am very new to the Geeklog sources so diving in and making changes to the layout is an excellent way to get familiar with it.

Geeklog does not support the mysqli interface.
Those tips you reference are for how to speed up normal queries. Site Search queries are never normal. How do you find all stories with the word 'boxcar' in them when the story text is stored in introtext and bodytext (both are text datatypes) and the word boxcar could be anywhere in the field?

Tip #20 only applies to ORs of fields that are indexed individually. Most text fields are not indexed and won't be improved by UNION.

And I throw tips #5 and #15 back at you.

Hello all,
Its been a while since I posted an update so I think one is due. I have got a lot done in the last couple of weeks. Most of the work has been on revamping the results page. The layout of the results is now handled by a formatter class called ListFactory. This means that the search class doesn't worry too much about the paging, ordering and layout of the results...that is all now done in the ListFactory. All the search class does is provide the list factory with the information on where to get the results and how to display them. The class is now probably 95% complete, there is just one or two niggling things that I need to sort out. Some of the work in the class has been based on the ADMIN_list() function. But I have added more features and tried to make the class as flexible as possible so hopefully it can be used else where on the Geeklog site or even replace the ADMIN_list() function. If you want to see it in action all the work can be taken from Mercurial and there is an example of its usage in the listfactory.class.php file. Let me know of any problems you may find.

The class has now been fully integrated into the search results page. The style of the search page can be changed through the site's configuration by the admin, so no more messing around with templates! Fields can be enabled or disabled depending on how much you want the user to see, also the page limits can be easily modified. Included are two styles, a table layout and a Google list style, but more styles can be included in the future with relative ease. HTML design is not really my forte so I apologise if the Google style looks a bit bland and ripped off. If anyone has suggestions on more styles or even on how to improve the current ones let me know. I should also mention that to get it working you need to follow these steps.

1. Clone or Pull the Mercurial repository to a local testing directory. Details can be found here.
2. Run through the Geeklog installation process as you would do normally.
3. Make a new temporary PHP file in the public_html folder, lets call it install_search.php.
4. Then copy and past the following code into that file and run it though a web browser.

5. Try out some searches and play around with the configuration settings. You may want to fill the database with some data first though.

Steps 3 and 4 will be included into the normal install/upgrade scripts. I haven't done it yet as there are other GSoCers working on the Mercurial repository and I didn't want to break anything they had done until my project was complete. So its included here for those eager beavers that want to try this out

As mentioned previously the new improvements have required some modification to the plugin API, particularly the plugin_dopluginsearch_ function. After doing some testing I have found that returning the query gives better performance then returning the results array. So this will be the basis of the new API. The SQL statements will then be executed one after the other storing all the results in an array. There are several variables that need to be passed back to the search class. This leads me to question how developers would prefer passing back the information. So I have made another post here under the Extensions > Plugins section of the forum to try and draw the attention of plugin developers that may not look here. Please discuss API in that post.