Search Fix without Sphinx

[DarkSuyan]

Hello Everybody.

Like many of you i got a very slowly Search since VBulletin 4.
But some Days ago i found finally a Way to fix it and without installing a 2. Serch Engine like Sphinx.

You should know there are 2 Ways a Table in a Database can be in VB, "innodb" or "MyISAM".
The Problem is that VB3 gots only "MyISAM" and VB4 now use's "innodb" and "MyISAM".

For any Reason alot Server are getting Problems with innodb and VBulletin 4 together, well anyways i used a addon some Days ago which changed all Non-Fulltext Tables to "innodb" and well what do you think what happend? The Site was that slowly, it keeps 5 Minuts to open the AdminCP

Well then i got the Idea to change all to "MyISAM" (after i realize that not all was MyISAM before...)so i changed the Addon a bit which i wanna share with you

Remember: Do it at you own Risk. My Times needed for Searching changed from 16-20 sec. to 0 sec. !

Well here you go:

Goto https://vborg.vbsupport.ru/showthread.php?t=228846, download the addon and install it at you Site. (AdminCP -> Addon's & Plugins -> manage Addon's -> import)

Now go to AdminCP -> Addons & Plugins -> manage Plugins -> MyISAM 2 innoDB

In the 5. Box replace ALL for:

switch ($_REQUEST['do'])
{
	case 'chooser':
	{
		print_form_header('misc', 'change_myisam_to_innodb');
		print_table_header($vbphrase['plusvb_myisam_to_innodb']);
		print_description_row($vbphrase['plusvb_myisam_to_innodb_description']);
		print_submit_row($vbphrase['go']);
	}
	break;  
			
	case 'change_myisam_to_innodb':
	{
		$mysql_engines = $db->query_read('SHOW ENGINES');
	
		while ($engines = $db->fetch_row($mysql_engines))
		{
			if ((strtolower($engines[0]) == 'innodb') AND 
				((strtolower($engines[1]) == 'yes') OR
				(strtolower($engines[1]) == 'default')))
			{
				$innodb_enabled = true;
			}
		}
		$db->free_result($engines); 
		
		if (!$innodb_enabled)
		{
			print_stop_message('plusvb_innodb_disable');	
		}
		
		$tables = $db->query_write("SHOW TABLE STATUS");
        $db->hide_errors();
		while ($table = $db->fetch_array($tables))
		{
			$table['Engine'] = (!empty($table['Type']) ? $table['Type'] : $table['Engine']); 
			$value = $table['Name'];
			if (!in_array($table['Name'], array(
					'post', 'thread', 'groupmessage', 'language', 'pt_issue', 
					'pt_issuenote', 'socialgroup', 'blog_text')) AND 
				!in_array(strtolower($table['Engine']), array('heap', 'memory', 'MyISAM'))) //innodb //MyISAM
			{                                                                               
				echo "<br />\nConverting $value ... "; 
				vbflush();
			
				$db->query_write("ALTER TABLE $value ENGINE=MyISAM");  
				
				if ($db->errno() == 0)
				{
					echo 'done';					
				}
				else
				{
					echo 'FILED';			
				}
				vbflush(); 
			}             
		}
		$db->free_result($table); 
		$db->show_errors();
	}
	break;
}

Then Save and got to Maintence -> Update Counters -> Change tables engine to InnoDB

It will takes some time, depends how big your Database is and dont get fooled by the Name or Description of the Addon is only edited the executing Code not the Names or Descriptions...

So thats it, remember: Do it at your own Risk, it worked great for me but must not mean that it does to you too ;P


PS: Yes, im German and my English isnt that well...

Credits for original Addon goes to PlusVB