Improving search

[garciademarina]

Hi all,

New on wiki.osclass.org Fine-Tuning MySQL Full-Text Search - Improving search

Some users tell us that the search is not working well when you search some words, I explain us what is happening.

At osclass 2.3 we added fulltext search on item title and item description, this is a feature of mysql server that create a index of words and after use this index for do the search.

MySQL's full-text search capability has few user-tunable parameters.

The minimum and maximum lengths of words to be indexed by default is 4 (4 characters), that means, only words more or equal than 4 will be indexed.

If you like to change the minium and maxium lengths of words to be indexed, you need update your mysql config file.

Code: [Select]

[mysqld]
ft_min_word_len=3

[mysqld]
ft_max_word_len=10
Updating minium and maximum lengths of words.

After this, if you modify full-text variables that affect indexing (ft_min_word_len, ft_max_word_len, or ft_stopword_file), you must rebuild your FULLTEXT indexes after making the changes and restarting the server. To rebuild the indexes in this case, it is sufficient to do a QUICK repair operation:

Code: [Select]

mysql> REPAIR TABLE tbl_name QUICK;

NOTE:Note that full-text search is carefully tuned for the most effectiveness. Modifying the default behavior in most cases can actually decrease effectiveness. Do not alter the MySQL sources unless you know what you are doing.

Source: Fine-Tuning MySQL Full-Text Search

[Jesse]

Thanks Carlos! That will actually be very helpful to me.

The items that my site will specialize in, typically have 3 character designations, so this will be very important to me.

[daniel13dark]

Where can i find  mysql config file.? , i have a hosting on a private company so i can control the configuration

[garciademarina]

Hi @daniel13dark,

http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION0001500000

Regards

[prospective_user]

Carlos

You answered my question as to why I cannot search on three letter words! This is important on my site.

Do you have any idea whether the my.cnf file can be used on shared hosting arrangements? I tried putting the values you list into a my.cnf in my root user directory. I then discovered that you can "REPAIR" a table from within phpmyadmin under "Operations".

But this does not seem to change the search to find 3 letter words.

Any help appreciated! If I can correct this, I would like to revise your wiki entry to clarify and correct typos. Thank you!

[prospective_user]

According to my ISP for shared hosting, I can put a my.cnf file in the directory where OSClass is located.

I did that, and made the file .my.cnf so it is hidden. I then set its permissions to 444, as MySQL docs on .cnf files say:

Note
On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional as a security measure.

I then go into phpmyadmin, go to table item_description and select command "operations" and "repair".

this does not seem to have any effect on the indexing of 3 letter words.

[garciademarina]

Hi,

I have set 664 permissions to my.cnf, can you try it yourself.

I don't know why you cannot load this configuration, try asking your hosting provider.

Regards

[zepht]

Hi all,

New on wiki.osclass.org Fine-Tuning MySQL Full-Text Search - Improving search

Some users tell us that the search is not working well when you search some words, I explain us what is happening.

At osclass 2.3 we added fulltext search on item title and item description, this is a feature of mysql server that create a index of words and after use this index for do the search.

MySQL's full-text search capability has few user-tunable parameters.

The minimum and maximum lengths of words to be indexed by default is 4 (4 characters), that means, only words more or equal than 4 will be indexed.

If you like to change the minium and maxium lengths of words to be indexed, you need update your mysql config file.

Code: [Select]

[mysqld]
ft_min_word_len=3

[mysqld]
ft_max_word_len=10
Updating minium and maximum lengths of words.

After this, if you modify full-text variables that affect indexing (ft_min_word_len, ft_max_word_len, or ft_stopword_file), you must rebuild your FULLTEXT indexes after making the changes and restarting the server. To rebuild the indexes in this case, it is sufficient to do a QUICK repair operation:

Code: [Select]

mysql> REPAIR TABLE tbl_name QUICK;

NOTE:Note that full-text search is carefully tuned for the most effectiveness. Modifying the default behavior in most cases can actually decrease effectiveness. Do not alter the MySQL sources unless you know what you are doing.

Source: Fine-Tuning MySQL Full-Text Search

Hello, garciademarina

Is there any posibility to make this improvement for version 2.3.5 without updating ? I had made some updates before and my experience wasn't qite good ....

Can you please help me ti improve this search on 2.3.5 ? I don't want to update .... For now, If I have items containic "services", the result search for "car services" also appearing ...

[serjuc11111]

where i find this my.cnf?

[serjuc11111]

u  can change this only u have a dedicate server!VPS...i got some information....

[osclassuser]

My friend, where must I change it? on the ftp server on a file or directly login to mysql server and change it there?

but where is the file I cant find the file to change

[serjuc11111]

My friend, where must I change it? on the ftp server on a file or directly login to mysql server and change it there?

but where is the file I cant find the file to change

if u have a shared hosting u don't have permission to mysqlconfig file...better ask your hosting provider!

sejuc

[byteGator]

How to do this.
At Your terminal:

1. Edit mysql sudo nano /etc/mysql/my.cnf
under [mysqld] add ft_min_word_len=3

Code: [Select]

[mysqld]
ft_min_word_len=3

2. Ctrl-X (Exit and Save)

3. Restart MySQL

Code: [Select]

sudo service mysql restart

4. Enter mysql command line

Code: [Select]

mysql -u username -p

5. Select database

Code: [Select]

mysql> show databases;
mysql> use (databasename);
mysql> show tables;

6. Repair table

Code: [Select]

mysql> REPAIR TABLE oc_t_item_description QUICK;

7. Quit and done.

[SmaRTeY]

I have the same issue but hey, in the mean time while people are unable to set indexing on three characters you could easily adjust the search limit to 4 using the code in your footer.php theme file so users get a fair warning when having entered less than 4 chars. (Instead val().length < 3 it becomes < 4)

Code: [Select]

<!-- Script check on search text -->
<script type="text/javascript">
var sQuery = '<?php echo $sQuery; ?>';
function doSearch() {
if ($('input[name=sPattern]').val() == sQuery || ($('input[name=sPattern]').val() != '' && $('input[name=sPattern]').val().length < 4)) {
$('input[name=sPattern]').css('background', '#FFC6C6');
$('#search-example').text('<?php echo osc_esc_js(__('Your search must be at least three characters long', 'minimalist')); ?>')
return false;
}
return true;
}
</script>

Regards,
Eric

[Ricky]

I have the same issue but hey, in the mean time while people are unable to set indexing on three characters you could easily adjust the search limit to 4 using the code in your footer.php theme file so users get a fair warning when having entered less than 4 chars. (Instead val().length < 3 it becomes < 4)

Code: [Select]

<!-- Script check on search text -->
<script type="text/javascript">
var sQuery = '<?php echo $sQuery; ?>';
function doSearch() {
if ($('input[name=sPattern]').val() == sQuery || ($('input[name=sPattern]').val() != '' && $('input[name=sPattern]').val().length < 4)) {
$('input[name=sPattern]').css('background', '#FFC6C6');
$('#search-example').text('<?php echo osc_esc_js(__('Your search must be at least three characters long', 'minimalist')); ?>')
return false;
}
return true;
}
</script>

Regards,
Eric

TKS YOU very much! I lov U

Finally a solution for my problems