Query optimization or database structure

Hello,
I have database with one table:

CREATE TABLE `artikel` (
`title` varchar(150) NOT NULL,
`description_short` varchar(250) NOT NULL,
KEY `title` (`title`),
FULLTEXT KEY `title_2` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=2113423 DEFAULT CHARSET=latin1 ;

MySQL: 5.0.24a-log
Server: Opteron 146, RAM 1 GB

This table is use to keep information about products. There are about 5
million products in database.
The database size is about 5 GB.

search_string = it could be few words but rather no more then 5-6
minimum string length is: 3

To find a product in database I use the following query:
"SELECT title, MATCH (title) AGAINST ('+$search_string' in boolean
mode) as score from artikel where match (title) against
('+$search_string*' in boolean mode) order by score";

It there is some way to optimization query, database structure to get
result faster.
Thank you for your help,
Adam

> I have database with one table:
>
> CREATE TABLE `artikel` (
> `title` varchar(150) NOT NULL,
> `description_short` varchar(250) NOT NULL,
> KEY `title` (`title`),
> FULLTEXT KEY `title_2` (`title`)
> ) ENGINE=MyISAM AUTO_INCREMENT=2113423 DEFAULT CHARSET=latin1 ;
>
> MySQL: 5.0.24a-log
> Server: Opteron 146, RAM 1 GB
>
> This table is use to keep information about products. There are about 5
> million products in database.
> The database size is about 5 GB.
>
> search_string = it could be few words but rather no more then 5-6
> minimum string length is: 3
>
> To find a product in database I use the following query:
> "SELECT title, MATCH (title) AGAINST ('+$search_string' in boolean mode)
> as score from artikel where match (title) against ('+$search_string*' in
> boolean mode) order by score";
>
> It there is some way to optimization query, database structure to get
> result faster.

Check http://tinyurl.com/hwrwb .



Regards
Dimitre

Radoulov, Dimitre wrote:
>> I have database with one table:
>>
>> CREATE TABLE `artikel` (
>> `title` varchar(150) NOT NULL,
>> `description_short` varchar(250) NOT NULL,
>> KEY `title` (`title`),
>> FULLTEXT KEY `title_2` (`title`)
>> ) ENGINE=MyISAM AUTO_INCREMENT=2113423 DEFAULT CHARSET=latin1 ;
>>
>> MySQL: 5.0.24a-log
>> Server: Opteron 146, RAM 1 GB
>>
>> This table is use to keep information about products. There are about 5
>> million products in database.
>> The database size is about 5 GB.
>>
>> search_string = it could be few words but rather no more then 5-6
>> minimum string length is: 3
>>
>> To find a product in database I use the following query:
>> "SELECT title, MATCH (title) AGAINST ('+$search_string' in boolean mode)
>> as score from artikel where match (title) against ('+$search_string*' in
>> boolean mode) order by score";
>>
>> It there is some way to optimization query, database structure to get
>> result faster.
>
> Check http://tinyurl.com/hwrwb .
>
>
>
> Regards
> Dimitre
>
>
Thank you.
Sphinx seems to be really interesting. Do you know where can I find some
tutorials how to build search.php file for sphinx. I was searching
google and only found one on vbulleting forum, but is really complicated.
Thank you,
Adam

[...]
>>> It there is some way to optimization query, database structure to get
>>> result faster.
>>
>> Check http://tinyurl.com/hwrwb .
>>
>>
>>
>> Regards
>> Dimitre
> Thank you.
> Sphinx seems to be really interesting. Do you know where can I find some
> tutorials how to build search.php file for sphinx. I was searching google
> and only found one on vbulleting forum, but is really complicated.

Did you check the product documentation?

http://sphinxsearch.com/docse.html


Regards
Dimitre

CUT
> Did you check the product documentation?
>
> http://sphinxsearch.com/docse.html
>
>
> Regards
> Dimitre
>
>

Thank you again for help. I used perl api to find out all options.
Sphinx is really amazing (all searching below 0.1 sec) tool if you want
to use full text searches.
Adam