Searchlight 2.1.0 entering final development stage

Floren · #1 07-10-2009, 02:24 PM

After many white nights and a lot of debugging, Searchlight 2.1 starts to give the expected results.

The Searchlight product will be completely independent. In other words, the new OOP classes will allow you to expand beyond limits the Sphinx integration with other areas beside simple searching. You will also be able to use it in any PHP software that uses MySQL queries. I will demonstrate soon with code examples how easy you can replace a regular vBulletin query with a Searchlight one. That does not mean the traditional search is neglected. In fact, I will introduce a new feature that will make your users searching life very easy. A little patience, you will be extremely satisfied.

I expect to finalize the code within few weeks time frame. I have 3 beta testers who are confident to apply the new changes on a live board, after the demonstration I made into my 3 test servers. Then, I will be able to remove in a snap all current table locks produced by vBulletin queries... and do many other things.

Please stay tuned, Searchlight 2.1 is coming to a server near you...

Floren · #2 07-23-2009, 06:29 PM

The main advantage of the current product is that it can be easily used into any software, not just vBulletin. I want to show you quickly how you can make usage of the new Searchlight product, in every heavy area of your software. In vBulletin, let's presume I want to grab the latest (10) threadid's that contain the keyword test into title. All I have to do is execute this code:

Code:

$threadids = array();
$vbulletin->sl->query_init(true);
$threadids = array();
$threads = $vbulletin->sl->query("
	SELECT id, lastpost FROM thread
	WHERE MATCH ('@title test')
	ORDER BY lastpost DESC, id DESC
	LIMIT 0, 10
");
foreach ($threads AS $thread)
{
	$threadids[] = $thread['id'];
}
var_dump($threadids);

The result is pulled in 0.008 seconds, while scanning 850,000 threads:

Code:

array
  0 => string '980928' (length=6)
  1 => string '253598' (length=6)
  2 => string '979795' (length=6)
  3 => string '978146' (length=6)
  4 => string '979165' (length=6)
  5 => string '856057' (length=6)
  6 => string '961690' (length=6)
  7 => string '975688' (length=6)
  8 => string '975713' (length=6)
  9 => string '975532' (length=6)

Now, I execute the same query, for posts:

Code:

$postids = array();
$posts = $vbulletin->sl->query("
	SELECT id, lastpost FROM post
	WHERE MATCH ('@posttitle test')
	ORDER BY lastpost DESC, id DESC
	LIMIT 0, 10
");
foreach ($posts AS $post)
{
	$postids[] = $post['id'];
}
var_dump($postids);

The result is pulled in 0.035 seconds, while scanning 8,600,000 posts:

Code:

array
  0 => string '9328644' (length=7)
  1 => string '9328499' (length=7)
  2 => string '9317053' (length=7)
  3 => string '9310837' (length=7)
  4 => string '9306326' (length=7)
  5 => string '9306244' (length=7)
  6 => string '9305946' (length=7)
  7 => string '9304957' (length=7)
  8 => string '9303815' (length=7)
  9 => string '9226044' (length=7)

You can also execute regular queries (note that the MATCH condition was removed):

Code:

$threads = $vbulletin->sl->query("
	SELECT id, lastpost FROM thread
	WHERE lastpost < " . TIMENOW . "
	ORDER BY lastpost DESC, id DESC
	LIMIT 0, 10
");
foreach ($threads AS $thread)
{
	$threadids[] = $thread['id'];
}
var_dump($threadids);

The result is pulled in 0.007 seconds and displays the latest threads:

Code:

array
  0 => string '981047' (length=6)
  1 => string '981046' (length=6)
  2 => string '981045' (length=6)
  3 => string '981044' (length=6)
  4 => string '981043' (length=6)
  5 => string '981042' (length=6)
  6 => string '981041' (length=6)
  7 => string '981040' (length=6)
  8 => string '981039' (length=6)
  9 => string '981038' (length=6)

Floren · #3 08-12-2009, 09:39 PM

Some major breakthrough was done tonight. I can successfully bypass the "query of death", in vBulletin:

Code:

$getpostids = $db->query_read("
	SELECT post.postid
	FROM " . TABLE_PREFIX . "post AS post
	$hook_query_joins
	WHERE post.threadid = $threadid
		AND post.visible = 1
		" . ($coventry ? "AND post.userid NOT IN ($coventry)" : '') . "
		$hook_query_where
	ORDER BY post.dateline $postorder
	LIMIT $limitlower, $perpage
");

Everyone having a large forums knows the issues related to table locks generated by that query.
With Searchlight, this is now history... saving a ton of cash related to the cost of yearly server maintenance.

This is how the Searchlight query looks like:

Code:

$getpostids = $vbulletin->sl->query("
	SELECT @id AS seekid, dateline
	FROM post
	WHERE threadid = $threadid
		AND visible = 1
		" . ($coventry ? "AND userid NOT IN ($coventry)" : '') . "
	ORDER BY dateline $postorder
	LIMIT $limitlower, $perpage
");

Almost identical, really easy to implement for novice coders.

I still have to finalize some abstract classes, but pretty much everything works as expected.
Beware, PHP 5 is needed for the new version of Searchlight product.