[ANN] Mroonga 9.01 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 9.01 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2019/03/29/mroonga-9.01.html Changes: http://mroonga.org/docs/news.html#release-9.01 Here are some topics in this release. * Improved support for more table and comment parameter about tokenizer/normalizer/indexes. * See above blog entry about details. * Added support for latest MariaDB/Percona Server. * Percona Server 5.7.25-28. * MariaDB 10.3.13. * MariaDB 10.2.23. * Added support for building bundled MariaDB package on AppVeyor. Let's search by Mroonga! Regards, -- Kentaro Hayashi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 9.00 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 9.00 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2019/02/09/mroonga-9.00.html Changes: http://mroonga.org/docs/news.html#release-9.00 Here are some topics in this release. * Added support for MariaDB 10.3.12. * Added support for MariaDB 10.2.21. * Added support for Percona Server 5.7.24-27. * Added support for Percona Server 5.6.43 rel84.3. * Added support for MySQL 5.7.25. * Added support for MySQL 5.6.43. In Groonga 9.0.0, TokenPattern, TokenTable tokenizer and remove_blank for NormalizerNFKC100 is supported. If you upgrade to Groonga 9.0.0, you can use them from Mroonga 9.00! * See http://groonga.org/docs/news.html#release-9-0-0-2019-02-09 Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: [ANN] Mroonga 8.09 - Fast fulltext search for all languages on MySQL
Hi, Sorry, There was wrong release information in Mroonga 8.09. The MySQL 8 is not supported. That is still being handled. On 2018/11/29 14:12, Horimoto Yasuhiro wrote: > Hi, > > Mroonga 8.09 has been released! > > Mroonga is a MySQL storage engine that supports fast fulltext search > and geolocation search. It is CJK ready. It uses Groonga as a storage > and fulltext search engine. > > Document: >http://mroonga.org/docs/ > > How to install: Install Guide >http://mroonga.org/docs/install.html > > How to upgrade: Upgrade Guide >http://mroonga.org/docs/upgrade.html > > Blog: >http://mroonga.org/en/blog/2018/11/29/mroonga-8.09.html > > Changes: >http://mroonga.org/docs/news.html#release-8.09 > > Here are some topics in this release. > > * Supported Ubuntu 18.10 (Cosmic Cuttlefish). > * Supported MariaDB 10.3.10. > * Supported MariaDB 10.2.19 > * Supported MariaDB 10.1.37 > * Supported Percona Server 5.7.23-25. > * Supported MariaDB 10.3.11. > * Supported MySQL 5.6.42. > * Supported MySQL 5.7.24. > * Supported MySQL 8. > > Regards, > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 8.09 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 8.09 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2018/11/29/mroonga-8.09.html Changes: http://mroonga.org/docs/news.html#release-8.09 Here are some topics in this release. * Supported Ubuntu 18.10 (Cosmic Cuttlefish). * Supported MariaDB 10.3.10. * Supported MariaDB 10.2.19 * Supported MariaDB 10.1.37 * Supported Percona Server 5.7.23-25. * Supported MariaDB 10.3.11. * Supported MySQL 5.6.42. * Supported MySQL 5.7.24. * Supported MySQL 8. Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 8.07 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 8.07 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2018/09/29/mroonga-8.07.html Changes: http://mroonga.org/docs/news.html#release-8.07 Here are some topics in this release. * Tokenizer off option is now deprecated, use none instead * MariaDB 10.1.36 has been supported ## Tokenizer off option is now deprecated, use none instead In this release, tokenizer off option is deprecated. Use tokenizer none instead. Before: FULLTEXT INDEX (content) COMMENT 'tokenizer "off"' After: FULLTEXT INDEX (content) COMMENT 'tokenizer "none"' To keep consistency with normalizer option and PGroonga, off option has been deprecated. ## MariaDB 10.1.36 has been supported In this release, MariaDB 10.1.36 has been supported. To support MariaDB 10.1.36 changes, Mroonga has dropped support for MariaDB 10.2.2 (Shipped at Sep 27, 2016) and older MariaDB 10.2 series. Regards, -- Masafumi Yokoyama -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 8.06 - Fast fulltext search for all languages on MySQL
Mroonga 8.06 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html Blog: http://mroonga.org/en/blog/2018/08/29/mroonga-8.06.html Changes: http://mroonga.org/docs/news.html#release-8-06 In this version, MySQL will be automatically restarted if you had already installed Mroonga and not installed Groonga 8.0.4 or later. Because Mroonga 8.06 requires Groonga 8.0.4 or later but it will not reloaded until MySQL is restarted. The main changes are as follows. * Supported utf8mb4_0900 family collation. Through this change, groonga-normalizer-mysql 1.1.3 or later and Groonga 8.0.4 or later have been required. * ref: https://github.com/groonga/groonga-normalizer-mysql#description * Supported tokenizer options. * e.g.: tokenizer "TokenNgram(\'loose_symbol\', true)" * ref: http://groonga.org/docs/news.html#release-8-0-2 * [Windows] Updated bundled MariaDB to 10.3.9. * NOTICE: Before upgrading to MariaDB 10.3, you need to dump existing MariaDB 10.1 databases. Then restore it after upgrading. * [Debian GNU/Linux] Dropped Debian 8 (jessie) support. * [Ubuntu] Dropped Ubuntu 17.10 (Artful Aardvark) support. -- Masafumi Yokoyama -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 8.03 - Fast fulltext search for all languages on,MySQL
Mroonga 8.03 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html Blog: http://mroonga.org/en/blog/2018/05/29/mroonga-8.03.html Changes: http://mroonga.org/docs/news.html#release-8.03 The main changes are as follows. There are no big changes in this release. * [Docker] Added quick start guide link to Docker Hub. * http://mroonga.org/docs/install/docker.html * The contributors are helping to maintain monthly. Thank you! * [CentOS Supported MariaDB 10.3.7. * [CentOS] Supported MariaDB 10.2.15 and 10.1.33 (backported to 8.03). * [Windows] Supported MariaDB 10.1.33. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 8.02 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 8.02 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2018/04/29/mroonga-8.02.html Changes: http://mroonga.org/docs/news.html#release-8.02 Here is a topic in this release. * Supported Ubuntu 18.04 LTS (Bionic Beaver). * [CentOS][Percona Server] Supported upgrading from "< 5.6.34" and "< 5.7.21". * detail: https://github.com/mroonga/mroonga/commit/555d6683bd2a095180fb278faffbe611bf62a8a3 * Fixed a crash bug when some complex condition in `ORDER BY` such as `ORDER BY 1 + 1, id, content`. * Fixed a bug that `MATCH AGAINST` condition is ignored if SQL containing such as`AND (x = 1 OR x = 2)` when condition push down is enabled. * Fixed a memory leak for column caches. Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 8.01 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 8.01 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2018/03/29/mroonga-8.01.html Changes: http://mroonga.org/docs/news.html#release-8.01 In this release, Mroonga requires latest Groonga 8.0.1 to fix bugs. It means that MySQL/MariaDB must be restarted to upgrade package correctly. Here is a topic in this release. ## Fixed a bug that wrong cache for other database is used In this release, wrong cache related bug was fixed. This bug occurs the following conditions are met. * Multiple database are created * Use mroonga_command() against one of them If you don't use mroonga_command() in query, above conditions are not met, so this bug doesn't affect you. Regards, -- Kentaro Hayashi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 8.00 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 8.00 has been released! This is a major version up! But It keeps backward compatibility. You can upgrade to 8.0.0 without rebuilding database. Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2018/02/09/mroonga-8.00.html Changes: http://mroonga.org/docs/news.html#release-8.00 Here are some topics in this release. * When create hash index, tiny hash is used for reducing consumption of resources. * [percona57] Added gap lock detection support. ## When create hash index, used tiny hash for reducing used resource Hash index is less resouce to used from this release. Note:that rebuilding indexes are required to enable this feature for existence indexes ## [percona57] Added gap lock detection support A gap lock was being attempted while the transaction isolation level was either REPEATABLE READ or SERIALIZABLE, the following SQL error was returned to the client and no actual gap lock was taken on the affected rows. ERROR 1105 (HY000): Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need to either rewrite queries to use all unique key columns in WHERE equal conditions, or rewrite to single-table, single-statement transaction. Query: ALTER TABLE t ENGINE=InnoDB >From this release, as the above situation, gap lock will be taken on the affected rows. Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 7.11 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 7.11 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2018/01/29/mroonga-7.11.html Changes: http://mroonga.org/docs/news.html#release-7.11 Here are some topics in this release. * [Experimental] MariaDB 10.3 package has been supported on CentOS 6/7 * [Ubuntu] MariaDB 10.1 package has been supported on Ubuntu 17.10 In this release, packages for MariaDB 10.1, 10.3 support has been improved on CentOS 6/7 and Ubuntu 17.10. We are glad if you try these packages and give us feedbacks. ### [Experimental] MariaDB 10.3 package has been supported on CentOS 6/7 In this release, official MariaDB 10.3 packages has been supported on CentOS 6/7. To install on each supported environment, see install documents. * MariaDB 10.3 on CentOS 6 http://mroonga.org/docs/install/centos.html#centos-6-with-mariadb-10-3-package * MariaDB 10.3 on CentOS 7 http://mroonga.org/docs/install/centos.html#centos-7-with-mariadb-10-3-package ## [Ubuntu] MariaDB 10.1 package has been supported on Ubuntu 17.10 In this release, MariaDB 10.1 package has been supported. To install it, see install documents. * Install from Launchpad PPA http://mroonga.org/docs/install/ubuntu.html Regards, -- Kentaro Hayashi pgp_ncUlbX40a.pgp Description: PGP signature
[ANN] Mroonga 7.10 - Fast fulltext search for all languages on,MySQL
Hi, Mroonga 7.10 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. * Document: http://mroonga.org/docs/ * How to install: http://mroonga.org/docs/install.html * How to upgrade: http://mroonga.org/docs/upgrade.html * Blog (same contents as this mail): http://mroonga.org/ja/blog/2017/12/29/mroonga-7.10.html * News: http://mroonga.org/docs/news.html#release-7.10 about detailed changes since 7.09. ### Changes In this release, some improvements for optimization and speedup are included. It also have experimental features, so we are glad if you try the feature and report to us. And, Groonga 7.1.0 or later is required from this release. Note that you will need to restart MySQL after upgrading to Mroonga 7.10. The main changes are as follows. * [Experimental] Made it possible to always enable condition push down * Improved performance for to get fixed size column value * Supported count skip optimization with multi column index * [CentOS] Supported Percona Server 5.6.38 and 5.7.20 * Supported MariaDB 10.3 ### [Experimental] Made it possible to always enable condition push down Added some variables related to condition push down (Optimization to process search condition at Groonga level rather than MySQL level). *[Mroonga_condition_push_down * http://mroonga.org/docs/reference/status_variables.html#status-variable-mroonga-condition-push-down * This value is increased when condition push down is used. You can use this value to check whether condition push down is used or not. * mroonga_condition_push_down_type * http://mroonga.org/docs/reference/server_variables.html#server-variable-mroonga-condition-push-down-type * It controls how to enable condition push down support. The default value is `ONE_FULL_TEXT_SEARCH`. It means that condition push down is enabled only when `WHERE` clause has one `MATCH AGAINST` condition. It is the same behavior as before. If the value `ALL` is set, condition push down is always used (ALL is experimental for now. We are glad if you use it and tell us if it got faster or not). Setting the value of `mroonga_condition_push_down_type` to `ALL` will always use condition push down, which is expected to be faster. However, `ALL` is an experimental feature for now. We are glad if you to use it and tell us if it got faster or not. The setting method is as follows. SET mroonga_condition_push_down_type = ALL Please choose an easy-to-use way for you from the following links. Even if it goes well, it will be nice if you let me know if it does not work. If it fails, please add logs and reproduction steps to the report. * GitHub issues * https://github.com/mroonga/mroonga/issues * Community * http://mroonga.org/docs/community.html We are waiting for your report. ### Improved performance for to get fixed size column value Supported column cache when to get fixed size column value to improve performance. This function needs to Groonga 7.1.0 or later, Groonga 7.1.0 or later is required from Mroonga 7.10 as described above. ### Supported count skip optimization with multi column index Enable count skip optimization for multi-column index. For details of count skip optimization, refer to the reference manual. http://mroonga.org/docs/reference/optimizations.html#row-count ### [CentOS] Supported Percona Server 5.6.38 and 5.7.20 In this release, Percona Server 5.6.38 and 5.7.20 has been supported. RPM packages are ready for above versions on CentOS 6 and CentOS 7. ### Supported MariaDB 10.3 Supported MariaDB 10.3. If you are using MariaDB 10.3, please try it. ### Conclusion See the news http://mroonga.org/docs/news.html#release-7.10 about detailed changes since 7.09. Let's search by Mroonga! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[ANN] Mroonga 7.09 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 7.09 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2017/11/29/mroonga-7.09.html Changes: http://mroonga.org/docs/news.html#release-7.09 Here is the topic in this release. * MariaDB 10.1.29/10.2.11 package has been supported on CentOS 6/7 In this release, some test related patch files are merged into Mroonga. These patch files are deliverables from MariaDB community. Thanks, MariaDB community! ### MariaDB 10.1.29/10.2.11 package has been supported on CentOS 6/7 In this release, official MariaDB 10.1.29/10.2.11 packages has been supported on CentOS 6/7. To install on each supported environment, see install documents. * MariaDB 10.1 on CentOS 6 http://mroonga.org/docs/install/centos.html#centos-6-with-mariadb-10-1-package * MariaDB 10.2 on CentOS 6 http://mroonga.org/docs/install/centos.html#centos-6-with-mariadb-10-2-package * MariaDB 10.1 on CentOS 7 http://mroonga.org/docs/install/centos.html#centos-7-with-mariadb-10-1-package * MariaDB 10.2 on CentOS 7 http://mroonga.org/docs/install/centos.html#centos-7-with-mariadb-10-2-package Regards, -- Kentaro Hayashi pgpYaMBZ_F8Ik.pgp Description: PGP signature
[ANN] Mroonga 7.07 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 7.08 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2017/10/29/mroonga-7.08.html Changes: http://mroonga.org/docs/news.html#release-7.08 Here is the topic in this release. * Supported Ubuntu 17.10 (Artful Aardvark). * Supported table level flags option. You can specify TABLE_HASH_KEY, TABLE_PAT_KEY, TABLE_DAT_KEY, and KEY_LARGE table options. ## [ubuntu] Supported Ubuntu 17.10 (Artful Aardvark) In this release, Ubuntu 17.10 (Artful Aardvark) has been supported. To install Mroonga via PPA (Personal Package Archive), See http://mroonga.org/docs/install/ubuntu.html in details. ### Supported table level flags option. You can specify TABLE_HASH_KEY, TABLE_PAT_KEY, TABLE_DAT_KEY, and KEY_LARGE table options. In the previous version, when you create table in Mroonga, could not set Groonga's table flag. So, if you want create table for saving many big data, you needed to use mroonga_command. In this release, when you create table, you can specify KEY_LARGE flag. You can more easily make table for saving many big data from this. Here is how to specify KEY_LARGE. CREATE TABLE diaries ( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(255), FULLTEXT INDEX (content) ) ENGINE = Mroonga COMMENT = 'engine "InnoDB"' DEFAULT CHARSET utf8 flags "TABLE_HASH_KEY|KEY_LARGE"; To details of specify flags, see the below documents. * Groonga v7.0.8 documentation http://groonga.org/docs/reference/commands/table_create.html#flags Regards, -- Kentaro Hayashi pgp_up_ZHWEuF.pgp Description: PGP signature
[ANN] Mroonga 7.07 - Fast fulltext search for all languages on MySQL
Hi,
Mroonga 7.07 has been released!
Mroonga is a MySQL storage engine that supports fast fulltext search
and geolocation search. It is CJK ready. It uses Groonga as a storage
and fulltext search engine.
Document:
http://mroonga.org/docs/
How to install: Install Guide
http://mroonga.org/docs/install.html
How to upgrade: Upgrade Guide
http://mroonga.org/docs/upgrade.html
Blog:
http://mroonga.org/en/blog/2017/10/12/mroonga-7.07.html
Changes:
http://mroonga.org/docs/news.html#release-7.07
Here is the topic in this release.
* MariaDB 10.1/10.2 package has been supported on CentOS 6/7
* mroonga_query_expand UDF has been supported
### MariaDB 10.1/10.2 package has been supported on CentOS 6/7
In this release, official MariaDB 10.1/10.2 packages has been
supported on CentOS 6/7.
To install on each supported environment, see install documents.
* MariaDB 10.1 on CentOS 6
http://mroonga.org/docs/install/centos.html#centos-6-with-mariadb-10-1-package
* MariaDB 10.2 on CentOS 6
http://mroonga.org/docs/install/centos.html#centos-6-with-mariadb-10-2-package
* MariaDB 10.1 on CentOS 7
http://mroonga.org/docs/install/centos.html#centos-7-with-mariadb-10-1-package
* MariaDB 10.2 on CentOS 7
http://mroonga.org/docs/install/centos.html#centos-7-with-mariadb-10-2-package
### mroonga_query_expand UDF has been supported
In this release, mroonga_query_expand UDF has been supported.
Here is the sample schema to use this UDF.
CREATE TABLE diaries (
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(255),
FULLTEXT INDEX (content)
) ENGINE = Mroonga DEFAULT CHARSET utf8;
CREATE TABLE synonyms (
term varchar(255),
synonym varchar(255),
INDEX (term)
) ENGINE= Mroonga DEFAULT CHARSET utf8;
diaries is table for full text search. synonym table is used to define
synonyms.
Then insert sample data:
INSERT INTO synonyms VALUES ('Groonga', 'Groonga Mroonga Rroonga');
INSERT INTO diaries (content) VALUES ("Groonga is fast.");
INSERT INTO diaries (content) VALUES ("Mroonga is fast.");
INSERT INTO diaries (content) VALUES ("PGroonga is fast.");
Let's use mroonga_query_expand UDF.
If you want to search with synonym term, just use
mroonga_query_expand("synonyms", "term", "synonym", "Groonga"). This
enable you to do full text search by 'Groonga', 'Mroonga', 'Roonga' at
once even though specified term is only 'Groonga'.
mysql> SELECT * FROM diaries WHERE MATCH(content)
AGAINST(mroonga_query_expand("synonyms", "term", "synonym", "Groonga") IN
BOOLEAN MODE);
++--+
| id | content |
++--+
| 1 | Groonga is fast. |
| 2 | Mroonga is fast. |
++--+
2 rows in set (0.01 sec)
Regards,
--
Kentaro Hayashi
pgpwGRghM20cV.pgp
Description: PGP signature
[ANN] Mroonga 7.06 - Fast fulltext search for all languages on MySQL
Hi,
Mroonga 7.06 has been released!
Mroonga is a MySQL storage engine that supports fast fulltext search
and geolocation search. It is CJK ready. It uses Groonga as a storage
and fulltext search engine.
Document:
http://mroonga.org/docs/
How to install: Install Guide
http://mroonga.org/docs/install.html
How to upgrade: Upgrade Guide
http://mroonga.org/docs/upgrade.html
Blog:
http://mroonga.org/en/blog/2017/08/29/mroonga-7.06.html
Changes:
http://mroonga.org/docs/news.html#release-7.06
Here is the topic in this release.
* Generated Column has been supported
### Generated Column has been supported
In this release, Generated Column has been supported!
Here is the sample schema to use generated column.
CREATE TABLE logs (
id INT,
record JSON,
message VARCHAR(255) GENERATED ALWAYS AS (json_extract(`record`,
'$.message')) STORED,
FULLTEXT INDEX(message) comment 'tokenizer
"TokenBigramSplitSymbolAlphaDigit"'
) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4;
As you can see, the partial value of `record` column is used as `message`
column.
Then, you can do full text search against `message` column by the following
query.
> SELECT * FROM logs WHERE MATCH(message) AGAINST("ar" IN BOOLEAN MODE);
+--+-+---+
| id | record | message |
+--+-+---+
|1 | {"level": "info", "message": "start"} | "start" |
|2 | {"level": "info", "message": "restart"} | "restart" |
+--+-+---+
2 rows in set (0.02 sec)
You can also define `VIRTUAL` without actual data column.
CREATE TABLE logs (
id INT,
record JSON,
message VARCHAR(255) GENERATED ALWAYS AS (json_extract(`record`,
'$.message')) VIRTUAL
) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4;
But note that `FULLTEXT INDEX(message)` is not supported yet.
Regards,
--
Kentaro Hayashi
pgpesmZoNWTia.pgp
Description: PGP signature
[ANN] Mroonga 7.05 - Fast fulltext search for all languages on MySQL
Hi,
Mroonga 7.05 has been released!
Mroonga is a MySQL storage engine that supports fast fulltext search
and geolocation search. It is CJK ready. It uses Groonga as a storage
and fulltext search engine.
Document:
http://mroonga.org/docs/
How to install: Install Guide
http://mroonga.org/docs/install.html
How to upgrade: Upgrade Guide
http://mroonga.org/docs/upgrade.html
Blog:
http://mroonga.org/en/blog/2017/07/29/mroonga-7.05.html
Changes:
http://mroonga.org/docs/news.html#release-7.05
There are some topics in this release.
* Groonga query log has been supported
* MariaDB 10.2.7 has been supported
* Auto command syntax escape feature has been supported
* Ubuntu 16.10 (Yekkety Yak) support has been dropped
# Groonga query log has been supported
In this release, Groonga query log has been supported.
Use mroonga_query_log_file variable to use this feature.
mysql> SET GLOBAL mroonga_query_log_file = "/tmp/query.log";
It is useful to analyze query where is bottleneck in Groonga layer.
## MariaDB 10.2.7 has been supported
In this release, MariaDB 10.2.7 has been supported.
Thanks Tomohiro 'Tomo-p' KATO for contributing to fix build error
against MariaDB 10.2.7..
## Auto command syntax escape feature has been supported
In this release, auto command syntax escape feature has been
supported in mroonga_command.
mroonga_command is used to cooperate with Groonga layer.
You can specify command name and its argument separately in
mroonga_command.
Using new syntax, arguments are automatically escaped, so you don't need to
consider what query is actually passed to Groonga layer.
SELECT mroonga_command('COMMAND_NAME',
'PARAMETER_NAME_1', 'PARAMETER_VALUE_1',
...,
'PARAMETER_NAME_N', 'PARAMETER_VALUE_N');
Example:
SELECT mroonga_command('select',
'table', 'diaries',
'filter', 'title @ "Groonga"');
It is useful for active mroonga_command user.
## Ubuntu 16.10 (Yekkety Yak) support has been dropped
In this release, Ubuntu 16.10 (Yakkety Yak) support was dropped. It
has reached EOL at July 20, 2017.
Regards,
--
Kentaro Hayashi
pgp2ggO7o_aV2.pgp
Description: PGP signature
[ANN] Mroonga 7.04 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 7.04 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2017/06/29/mroonga-7.04.html Changes: http://mroonga.org/docs/news.html#release-7.04 # Supported Debian 9 (stretch) In this release, Debian 9 (stretch) has been supported! Here is the Debian 9 specific install procedure: Add apt configuration in /etc/apt/sources.list.d/groonga.list: deb https://packages.groonga.org/debian/ stretch main deb-src https://packages.groonga.org/debian/ stretch main Run the following commands to install Mroonga: % sudo apt-get install apt-transport-https % sudo apt-get update % sudo apt-get install -y --allow-unauthenticated groonga-keyring % sudo apt-get update % sudo apt-get install -y -V mariadb-server-10.1-mroonga Since Debian 9, MariaDB 10.1 is adopted as a MySQL variant. mariadb-server-10.1-mroonga is corresponding package for it. Note that mariadb-plugin-mroonga is bundled version of Mroonga in MariaDB 10.1. It is a bit old version of Mroonga. We recommends to use mariadb-server-10.1-mroonga package! Regards, -- Kentaro Hayashi pgpeNDd1NXEII.pgp Description: PGP signature
[ANN] Mroonga 7.03 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 7.03 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2017/05/29/mroonga-7.03.html Changes: http://mroonga.org/docs/news.html#release-7.03 There are some topics in this release. * Supported INDEX_MEDIUM and INDEX_SMALL flags * [CentOS] Supported recent Percona Server 5.6.36 and 5.7.18 # Supported INDEX_MEDIUM and INDEX_SMALL flags In this release, the two flags which is supported since Groonga 6.0.8 are also supported in Mroonga. This two flags are used to create compact indexes in contrast to traditional Mroonga indexes. By using above two flags, you will be able to reduce memory consumption efficiently. Here is the example how to use these flags. CREATE TABLE diaries ( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(255), FULLTEXT INDEX (content) COMMENT 'flags "INDEX_SMALL"' ) ENGINE = Mroonga DEFAULT CHARSET utf8; Regards, pgpHUHe7wUkO0.pgp Description: PGP signature
[ANN] Mroonga 7.02 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 7.02 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2017/04/29/mroonga-7.02.html Changes: http://mroonga.org/docs/news.html#release-7.02 Here are the improvements in this release. * Supported Ubuntu 17.04 (Zesty Zapus) * Dropped Ubuntu 12.04 (Precise Pangolin) because of EOL * Fixed build error with MySQL 5.6.36 and 5.7.18 -- Kentaro Hayashi pgpjy29UmdZlE.pgp Description: PGP signature
[ANN] Mroonga 7.01 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 7.01 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2017/03/29/mroonga-7.01.html Changes: http://mroonga.org/docs/news.html#release-7.01 There are some topics in this release. * Dropped CentOS 5 support because of EOL * [Storage mode] Supported fast ORDER LIMIT with ENUM. * Supported COMPRESS_ZSTD column compression flag. * Visual Studio 2015 or later is required to build from source. Thanks. -- Kentaro Hayashi pgpDYoZLrtZ7r.pgp Description: PGP signature
[ANN] Mroonga 7.00 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 7.00 has been released! Even though major version upgrade, it keeps compatibility of Mroonga database. Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2017/02/09/mroonga-7.00.html Changes: http://mroonga.org/docs/news.html#release-7.00 There are two topics in this release. * Supported FOREIGN KEY constrain on UPDATE and DELETE parent row. In the previous versions, only FOREIGN KEY constrain on INSERT is supported. * [Storage mode] Supported updating row even though its table has primary key with ROW binlog format. In the previous version, it causes update statement error. -- Kentaro Hayashi pgpHKkASWJG_I.pgp Description: PGP signature
[ANN] Mroonga 6.11 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 6.11 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2016/11/29/mroonga-6.11.html Changes: http://mroonga.org/docs/news.html#release-6.11 There is a topic in this release. * Supported MariaDB 10.x package for Debian jessie ## Supported MariaDB 10.x package for Debian jessie In this release, we began to support Mroonga packages for MariaDB 10.x on Debian jessie. This support is provided for Debian jessie because MariaDB 10.x packages are also available on this release and it aims to make it easy to try Mroonga for user of MariaDB. To install Mroonga package for MariaDB, use mariadb-server-10.0-mroonga. $ sudo apt-get install -y -V mariadb-server-10.0-mroonga Regards, -- Kentaro Hayashi pgpy0OMZLfse8.pgp Description: PGP signature
Re: [ANN] Mroonga 6.10 - Fast fulltext search for all languages on MySQL
Hi, sorry for late response. Currently we have NO RECENT benchmark for direct comparison of Mroonga vs Elasticsearch. However, there are some helpful benchmarks. 1) Groonga 4.0.1 vs Elasticsearch 1.1.1 at 2014 http://blog.createfield.com/entry/2014/04/21/120023 (Japanese) (Note: Groonga is the core component of Mroonga.) 2) Groonga 4.0.3 vs Elasticsearch 1.1.2 at 2014, around indexing http://blog.createfield.com/entry/2014/07/22/080958 (Japanese) 3) Mroonga vs InnoDB[ngram] vs InnoDB[MeCab] benchamrk on MySQL 5.7 at July 2016 https://slide.rabbit-shocker.org/authors/kou/mariadb-community-event-2016-07-21/ (Note: both Mroogna and InnoDB are storage engines for MySQL. Both ngram and MeCab are major tokenizers for indexing of Japanese text.) Summary of 1) Groonga : Elasticsearch * Update / 1 : 2.5~3.13 (Elasticsearch is faster) * Disk usage / 1 : 1.14~1.5 (Elasticsearch's disk usage is smaller) * Search performance / 2.5~4.31 : 1 (Groonga is faster) Summary of 2) * The delay for a new record, until it appears in the search result after it is added: - Groonga: 0.09sec (a new record appears in the search result immediately) - Elasticsearch: 1.01sec (you need to wait for a refresh) * Estimated time to search (tested with 1000 queries): - Groonga[dynamic indexing]: ave=0.050sec, min=0.00215sec, max=0.339sec - Groonga[static indexing]: ave=0.052sec, min=0.00182sec, max=0.401sec - Elasticsearch[not optimized]: ave=0.216sec, min=0.000960sec, max=4.313sec - Elasticsearch[optimized]: ave=0.141sec, min=0.00317sec, max=1.037sec => - Groonga is constantly fast, without optimization. (On Groonga, static indexing is similar to "optimization". However, Groonga is enough fast even if the index is built dynamically, so you don't need to do periodical "optimization".) - Elasticsearch seems to require periodical optimization. Summary of 3), search performances of Mroonga : InnoDB[ngram] : InnoDB[MeCab] * case1, 23K hits / 3454 : 1654 : 1 (Mroonga is fastest) * case2, 17K hits / 400 : 1 : 1200 (InnoDB[MeCab] is fastest, Mroonga is the second) * case3, 400 hits / 1 : N/A : 5.6 (InnoDB[MeCab] is fastest, Mroonga is the second) * case4, 630K hits / 6 : 1 : 1 (Mroonga is fastest) => - Mroonga is constantly fast. - InnoDB[ngram] is constantly slow. - InnoDB[MeCab] is sometimes fast, sometimes slow. I hope it may helps. Regards, On Sat, 29 Oct 2016 04:15:47 -0400 Sami wrote: > Can you post some benchmarks or comparison with elasticsearch? > > > Sent from ProtonMail mobile > > > > Original Message > On 29 ott 2016 05:03, Kentaro Hayashi wrote: > > Hi, > > Mroonga 6.10 has been released! > > Mroonga is a MySQL storage engine that supports fast fulltext search > and geolocation search. It is CJK ready. It uses Groonga as a storage > and fulltext search engine. > > Document: > http://mroonga.org/docs/ > > How to install: Install Guide > http://mroonga.org/docs/install.html > > How to upgrade: Upgrade Guide > http://mroonga.org/docs/upgrade.html > > Blog: > http://mroonga.org/en/blog/2016/10/29/mroonga-6.10.html > > Changes: > http://mroonga.org/docs/news.html#release-6.10 > > There are two topics in this release. > > * Supported Ubuntu 16.10 (Yakkety Yak) > * Supported Mroonga with MariaDB 10.x for Ubuntu 16.04 or later > > ## Supported Ubuntu 16.10 (Yakkety Yak) > > In this release, we began to support Ubuntu 16.10 (Yakkety Yak). > > Mroonga packages are provided on PPA, you can install as follows: > > $ sudo apt-get install -y -V software-properties-common lsb-release > $ sudo add-apt-repository -y universe > $ sudo add-apt-repository \ > "deb http://security.ubuntu.com/ubuntu $(lsb_release --short > --codename)-security main restricted" > $ sudo add-apt-repository -y ppa:groonga/ppa > $ sudo apt-get update > $ sudo apt-get install -y -V mysql-server-mroonga > > ## Supported Mroonga with MariaDB 10.x for Ubuntu 16.04 or later > > In this release, we began to support Mroonga packages for MariaDB > 10.x. > > This support is provided for Ubuntu 16.04 or later because MariaDB > 10.x packages are available on these releases and it aims to make it > easy to try Mroonga for user of MariaDB. > > To install Mroonga package for MariaDB, use mariadb-server-mroonga. > > $ sudo apt-get install -y -V mariadb-server-mroonga > > > -- > Kentaro Hayashi -- Kentaro Hayashi pgpSSQIyjKEX1.pgp Description: PGP signature
Re: [ANN] Mroonga 6.10 - Fast fulltext search for all languages on MySQL
Can you post some benchmarks or comparison with elasticsearch? Sent from ProtonMail mobile Original Message On 29 ott 2016 05:03, Kentaro Hayashi wrote: Hi, Mroonga 6.10 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2016/10/29/mroonga-6.10.html Changes: http://mroonga.org/docs/news.html#release-6.10 There are two topics in this release. * Supported Ubuntu 16.10 (Yakkety Yak) * Supported Mroonga with MariaDB 10.x for Ubuntu 16.04 or later ## Supported Ubuntu 16.10 (Yakkety Yak) In this release, we began to support Ubuntu 16.10 (Yakkety Yak). Mroonga packages are provided on PPA, you can install as follows: $ sudo apt-get install -y -V software-properties-common lsb-release $ sudo add-apt-repository -y universe $ sudo add-apt-repository \ "deb http://security.ubuntu.com/ubuntu $(lsb_release --short --codename)-security main restricted" $ sudo add-apt-repository -y ppa:groonga/ppa $ sudo apt-get update $ sudo apt-get install -y -V mysql-server-mroonga ## Supported Mroonga with MariaDB 10.x for Ubuntu 16.04 or later In this release, we began to support Mroonga packages for MariaDB 10.x. This support is provided for Ubuntu 16.04 or later because MariaDB 10.x packages are available on these releases and it aims to make it easy to try Mroonga for user of MariaDB. To install Mroonga package for MariaDB, use mariadb-server-mroonga. $ sudo apt-get install -y -V mariadb-server-mroonga -- Kentaro Hayashi
[ANN] Mroonga 6.10 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 6.10 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2016/10/29/mroonga-6.10.html Changes: http://mroonga.org/docs/news.html#release-6.10 There are two topics in this release. * Supported Ubuntu 16.10 (Yakkety Yak) * Supported Mroonga with MariaDB 10.x for Ubuntu 16.04 or later ## Supported Ubuntu 16.10 (Yakkety Yak) In this release, we began to support Ubuntu 16.10 (Yakkety Yak). Mroonga packages are provided on PPA, you can install as follows: $ sudo apt-get install -y -V software-properties-common lsb-release $ sudo add-apt-repository -y universe $ sudo add-apt-repository \ "deb http://security.ubuntu.com/ubuntu $(lsb_release --short --codename)-security main restricted" $ sudo add-apt-repository -y ppa:groonga/ppa $ sudo apt-get update $ sudo apt-get install -y -V mysql-server-mroonga ## Supported Mroonga with MariaDB 10.x for Ubuntu 16.04 or later In this release, we began to support Mroonga packages for MariaDB 10.x. This support is provided for Ubuntu 16.04 or later because MariaDB 10.x packages are available on these releases and it aims to make it easy to try Mroonga for user of MariaDB. To install Mroonga package for MariaDB, use mariadb-server-mroonga. $ sudo apt-get install -y -V mariadb-server-mroonga -- Kentaro Hayashi pgpptGHRGqava.pgp Description: PGP signature
[ANN] Mroonga 6.09 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 6.09 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2016/09/29/mroonga-6.09.html Changes: http://mroonga.org/docs/news.html#release-6.09 Here is the topic in this release. * Supported nonexistent reference insert check for FOREIGN KEY ## Supported nonexistent reference insert check for FOREIGN KEY In the previous versions, there is no support about reference insert check for FOREIGN KEY. create table HOGES( ID INT PRIMARY KEY AUTO_INCREMENT, HOGE VARCHAR(255) ) ENGINE=Mroonga; create table FOODS( ID INT PRIMARY KEY AUTO_INCREMENT, HOGE_IDINT(10), FOOVARCHAR(255), foreign key(HOGE_ID) references HOGES(ID) ) ENGINE=Mroonga ; so, invalid data was inserted without error if you use above schema. Note that FOREIGN KEY check doesn't work for old version of MySQL and folks such as MySQL 5.5 or MariaDB 5.5, and FOREIGN KEY check for INSERT/DELETE is not supported yet. (it will be supported in the future release!) -- Kentaro Hayashi pgp7gYQrFC4RI.pgp Description: PGP signature
[ANN] Mroonga 6.08 - storage engine, fast fulltext search, CJK ready
Hi, Mroonga 6.08 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2016/08/29/mroonga-6.08.html Changes: http://mroonga.org/docs/news.html#release-6.08 There are two topics in this release. * Supported MariaDB 10.2.1 * Limitation about the maximum number record is relaxed ## Supported MariaDB 10.2.1 In this release, MariaDB 10.2.1 which is released 4 Jul, 2016 has been supported. As official Mroonga packages are not provided (because MariaDB 10.2.1 is alpha release), please see the following instruction to install it from source archive. http://mroonga.org/docs/install/others.html ## Limitation about the maximum number record is relaxed A table had the following limitations. This limitation is derived from Groonga. * The maximum one key size: 4KiB * The maximum total size of keys: 4GiB * The maximum number of records: 268,435,455 (more than 268 million) In the past, the maximum number of records is announced as 268 million. With recent research, it turns out that there is a capability to store more records than ever. Here is the updated limitation about the maximum number of records: * No primary key table: 1,073,741,815 (2^30 - 9) * PRIMARY KEY or PRIMARY KEY USING BTREE table: 1,073,741,823 (2^30 - 1) * PRIMARY KEY USING HASH table: 536,870,912 (2^29) Keep in mind that these limitations may vary depending on conditions. -- Kentaro Hayashi pgpcLewJWoSNM.pgp Description: PGP signature
[ANN] Mroonga 6.07 - Fast fulltext search for all languages on MySQL
Hi, Mroonga 6.07 has been released! Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ How to install: Install Guide http://mroonga.org/docs/install.html How to upgrade: Upgrade Guide http://mroonga.org/docs/upgrade.html Blog: http://mroonga.org/en/blog/2016/08/04/mroonga-6.07.html Changes: http://mroonga.org/docs/news.html#release-6.07 There are two topics in this release. * Updated bundled MariaDB to 10.1.16 on Windows * Dropped Ubuntu 15.10 (Wily Werewolf) support ## Updated bundled MariaDB to 10.1.16 on Windows In this release, bundled MariaDB is updated to 10.1.16. MariaDB 10.1.16 is the latest stable (GA) release. You can use it with latest Groonga (6.0.7) and Mroonga (6.07). ## Dropped Ubuntu 15.10 (Wily Werewolf) support In this release, Ubuntu 15.10 support is dropped. It reached EOL on July 28, 2016. Ubuntu 15.10 (Wily Werewolf) reaches End of Life on July 28 2016. We recommends to migrate to Ubuntu 16.04 because Mroonga 6.07 package for 16.04 is provided as PPA. -- Kentaro Hayashi pgpXqVgSMlM0y.pgp Description: PGP signature
[ANN] Mroonga 6.06 - storage engine, fast fulltext search, CJK ready
Hi, Mroonga 6.06 has been released! ## What is Mroonga? Mroonga is a MySQL storage engine that supports fast fulltext search and geolocation search. It is CJK ready. It uses Groonga as a storage and fulltext search engine. Document: http://mroonga.org/docs/ The characteristics of Mroonga: http://mroonga.org/docs/characteristic.html How to install: Install Guide http://mroonga.org/docs/install.html Tutorial: http://mroonga.org/docs/tutorial.html ## Topics There are two topics in the recent release. * Supported multibyte column name in inplace ALTER TABLE for storage mode * Supported ORDER BY LIMIT optimization for multibyte column for storage mode ### Improved multibyte column name related stuffs in storage mode In this release, multibyte column name related stuffs are improved. First, INPLACE ATER TABLE are supported for multibyte column in storage mode. Note that MySQL 5.6 or later can use INPLACE ALTER TABLE for adding columns and indexes. Second, ORDER BY LIMIT optimization for multibyte column in storage mode is also supported. Since Mroonga 6.02, multibyte column name was supported, above improvements are requested from Mroonga user. Thanks! -- Masafumi Yokoyama ClearCode Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
fulltext question
Hello all-
I have a question on searching via fulltext.
I have the following SQL statement:
var('SQLResultsID') = 'select *, MATCH
(product_id,product_name,product_desc) AGAINST("' + $sqlKeywordSearch + '") AS
SCORE from products WHERE MATCH (product_id,product_name,product_desc)
AGAINST("' + $sqlKeywordSearchB + '" IN BOOLEAN MODE) AND active NOT LIKE
"%no%" ORDER BY score DESC
First off, the variable $sqlKeywordSearch contains the search keywords
separated by spaces. The second variable, $sqlKeywordSearchB, has keywords
separated by spaces but also adds a “+" sign to the beginning of each keyword
to do the Boolean search and match all the keywords in the search.
question #1 - Is this the best way to do a boolean search but also return a
usable “score”? The search is doing what I expect it to do so no problem there.
Here’s my main question: I want to be able to “boost" the rankings(score) of
the results based on the fields. Anything that matches on the product_id field
I would like to get a higher ranking, then the product_name field next, then
the product_desc last (obviously, if something matches in the product_id field
it is of greater importance than if it matches in the product_description
field). I know I can boost, or "adjust", the score to the keywords if I want
but is there a way to add to the search score rankings based on the field
searched?
as always, Thank everyone for any help!
James
James Sheffer j...@higherpowered.com
Lasso Developerhttp://www.higherpowered.com
phone: 469-256-0268
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Small issue with FULLTEXT searches
Chris,
The index on (Dep, Des) is not a full-text index. If you use SHOW
CREATE TABLE, I think this becomes much easier to see at a glance.
- Baron
On Thu, Apr 29, 2010 at 8:10 AM, Chris Knipe wrote:
> Hi List,
>
> Table structure:
> mysql> DESCRIBE FlightRoutes;
> +-++--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +-++--+-+-+---+
> | EntryID | char(36) | NO | PRI | NULL | |
> | Dep | varchar(5) | NO | MUL | NULL | |
> | Des | varchar(5) | NO | | NULL | |
> | Route | text | NO | | NULL | |
> +-++--+-+-+---+
> 4 rows in set (0.01 sec)
>
> Indexes:
> mysql> SHOW INDEXES FROM FlightRoutes;
> +--+++--+-+---+-+--++--++-+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> +--+++--+-+---+-+--++--++-+
> | FlightRoutes | 0 | PRIMARY | 1 | EntryID |
> A | 21375 | NULL | NULL | | BTREE | |
> | FlightRoutes | 1 | ixAirports | 1 | Dep |
> A | 1943 | NULL | NULL | | BTREE | |
> | FlightRoutes | 1 | ixAirports | 2 | Des |
> A | 7125 | NULL | NULL | | BTREE | |
> | FlightRoutes | 1 | ixRoutes | 1 | Dep |
> NULL | 1 | NULL | NULL | | FULLTEXT | |
> | FlightRoutes | 1 | ixRoutes | 2 | Des |
> NULL | 1 | NULL | NULL | | FULLTEXT | |
> | FlightRoutes | 1 | ixRoutes | 3 | Route |
> NULL | 1 | NULL | NULL | | FULLTEXT | |
> +--+++--+-+---+-+--++--++-+
> 6 rows in set (0.00 sec)
>
> Query:
> mysql> SELECT Dep, Des, Route, MATCH(Dep, Des) AGAINST('FACT,OMDB') AS
> Relevance FROM FlightRoutes;
> ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
>
> I don't get that.
>
> What I have is a bunch of records:
> Point A, Point B, Route 1
> Point A, Point B, Route 2
> Point A, Point B, Route 2
> Point A, Point B, Route 3
>
> What I want to achive, is to select the Route from Point A to Point B that
> has the most relavence Naturally, I'm just starting to play with this
> now, but I fail to see how I can possibly play with FULL TEXT indexes when
> mySQL doesn't see / use the FULL TEXT that has already been created.
>
> Thanks for the assistance.
>
>
>
> --
>
> Regards,
> Chris Knipe
>
--
Baron Schwartz
Percona Inc <http://www.percona.com/>
Consulting, Training, Support & Services for MySQL
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Small issue with FULLTEXT searches
Hi List,
Table structure:
mysql> DESCRIBE FlightRoutes;
+-++--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-++--+-+-+---+
| EntryID | char(36) | NO | PRI | NULL| |
| Dep | varchar(5) | NO | MUL | NULL| |
| Des | varchar(5) | NO | | NULL| |
| Route | text | NO | | NULL| |
+-++--+-+-+---+
4 rows in set (0.01 sec)
Indexes:
mysql> SHOW INDEXES FROM FlightRoutes;
+--+++--+-+---+-+--++--++-+
| Table| Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--+++--+-+---+-+--++--++-+
| FlightRoutes | 0 | PRIMARY|1 | EntryID |
A | 21375 | NULL | NULL | | BTREE | |
| FlightRoutes | 1 | ixAirports |1 | Dep |
A |1943 | NULL | NULL | | BTREE | |
| FlightRoutes | 1 | ixAirports |2 | Des |
A |7125 | NULL | NULL | | BTREE | |
| FlightRoutes | 1 | ixRoutes |1 | Dep |
NULL | 1 | NULL | NULL | | FULLTEXT | |
| FlightRoutes | 1 | ixRoutes |2 | Des |
NULL | 1 | NULL | NULL | | FULLTEXT | |
| FlightRoutes | 1 | ixRoutes |3 | Route |
NULL | 1 | NULL | NULL | | FULLTEXT | |
+--+++--+-+---+-+--++--++-+
6 rows in set (0.00 sec)
Query:
mysql> SELECT Dep, Des, Route, MATCH(Dep, Des) AGAINST('FACT,OMDB') AS
Relevance FROM FlightRoutes;
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
I don't get that.
What I have is a bunch of records:
Point A, Point B, Route 1
Point A, Point B, Route 2
Point A, Point B, Route 2
Point A, Point B, Route 3
What I want to achive, is to select the Route from Point A to Point B that
has the most relavence Naturally, I'm just starting to play with this
now, but I fail to see how I can possibly play with FULL TEXT indexes when
mySQL doesn't see / use the FULL TEXT that has already been created.
Thanks for the assistance.
--
Regards,
Chris Knipe
Re: Fulltext Match BOOLEAN MODE not searching integers
Hi
I found by using the REPAIR command it is now working.
Thanks,
Neil
On Wed, Mar 31, 2010 at 5:38 PM, Tompkins Neil wrote:
> Hi Mark
>
> I did infact change the value of ft_min_word_len to 1. Rebuild the indexes
> using REPAIR table name. But it didn't appear to have any affect.
>
> Cheers
> Neil
>
> On Wed, Mar 31, 2010 at 5:29 PM, Mark Goodge wrote:
>
>> On 31/03/2010 16:52, Tompkins Neil wrote:
>>
>>> Hi
>>>
>>> I have the following fulltext search which appears to work fine for
>>> string
>>> phrases. However if I search like just "51" which is part of the string
>>> name like 51 Blue Widget in the table it doesn't return any results.
>>> However if I search like "bl" it returns the 51 Blue Widget result. My
>>> query is as follows :
>>>
>>> SELECT Name MATCH (Name) AGAINST ('51*') as Relevance
>>> FROM Products
>>> WHERE MATCH (Name) AGAINST ('51*' IN BOOLEAN MODE)
>>> ORDER BY Relevance DESC
>>>
>>> Any ideas what the problem might be ?
>>>
>>
>> "51" is too short to be included in the index by default, so will never
>> match. "Blue", on the other hand, is indexed and therefore is returned by a
>> search.
>>
>> The default minimum word length is four characters. See
>> http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html for more
>> information on how to change that if necessary.
>>
>> Mark
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
>>
>>
>
Re: Fulltext Match BOOLEAN MODE not searching integers
Hi Mark
I did infact change the value of ft_min_word_len to 1. Rebuild the indexes
using REPAIR table name. But it didn't appear to have any affect.
Cheers
Neil
On Wed, Mar 31, 2010 at 5:29 PM, Mark Goodge wrote:
> On 31/03/2010 16:52, Tompkins Neil wrote:
>
>> Hi
>>
>> I have the following fulltext search which appears to work fine for string
>> phrases. However if I search like just "51" which is part of the string
>> name like 51 Blue Widget in the table it doesn't return any results.
>> However if I search like "bl" it returns the 51 Blue Widget result. My
>> query is as follows :
>>
>> SELECT Name MATCH (Name) AGAINST ('51*') as Relevance
>> FROM Products
>> WHERE MATCH (Name) AGAINST ('51*' IN BOOLEAN MODE)
>> ORDER BY Relevance DESC
>>
>> Any ideas what the problem might be ?
>>
>
> "51" is too short to be included in the index by default, so will never
> match. "Blue", on the other hand, is indexed and therefore is returned by a
> search.
>
> The default minimum word length is four characters. See
> http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html for more
> information on how to change that if necessary.
>
> Mark
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
>
>
Re: Fulltext Match BOOLEAN MODE not searching integers
On 31/03/2010 16:52, Tompkins Neil wrote:
Hi
I have the following fulltext search which appears to work fine for string
phrases. However if I search like just "51" which is part of the string
name like 51 Blue Widget in the table it doesn't return any results.
However if I search like "bl" it returns the 51 Blue Widget result. My
query is as follows :
SELECT Name MATCH (Name) AGAINST ('51*') as Relevance
FROM Products
WHERE MATCH (Name) AGAINST ('51*' IN BOOLEAN MODE)
ORDER BY Relevance DESC
Any ideas what the problem might be ?
"51" is too short to be included in the index by default, so will never
match. "Blue", on the other hand, is indexed and therefore is returned
by a search.
The default minimum word length is four characters. See
http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html for
more information on how to change that if necessary.
Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fulltext Match BOOLEAN MODE not searching integers
Hi
I have the following fulltext search which appears to work fine for string
phrases. However if I search like just "51" which is part of the string
name like 51 Blue Widget in the table it doesn't return any results.
However if I search like "bl" it returns the 51 Blue Widget result. My
query is as follows :
SELECT Name MATCH (Name) AGAINST ('51*') as Relevance
FROM Products
WHERE MATCH (Name) AGAINST ('51*' IN BOOLEAN MODE)
ORDER BY Relevance DESC
Any ideas what the problem might be ?
Thanks,
Neil
Fulltext query expansion query
Hi, I'm currently working on a project which uses fuulltext searching. The "with query expansion" feature is useful, but I was wondering if there's any way to obtain the list of terms that the expanded query uses (other than those originally input, of course). Is that possible, and, if so, how? Thanks Mark -- Stuff: http://www.good-stuff.co.uk Blog: http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: error logging from fulltext plugin
As a followup, simply logging to stderr prints to the $host.err in the mysql data directory, which is good enough for my purposes. In case anyone else is curious, when experimenting I found I could use my_printf_error to return errors to the console where I inserted the bad data. But, that didn't log anything to the permanent log. The only trick about using my_printf_error was getting the header includes correct -- I needed mysql/my_global.h first. On Wed, Jul 15, 2009 at 7:09 PM, Tom Kleinpeter wrote: > Hello, > > I've written a fulltext plugin for Mysql 5.1. The plugin works great > and I'm happy with it, but I would like to log an error when I > encounter some unexpected data. Do plugins have access to the Mysql > error log? If so, how do I write to it? > > Thanks! > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
error logging from fulltext plugin
Hello, I've written a fulltext plugin for Mysql 5.1. The plugin works great and I'm happy with it, but I would like to log an error when I encounter some unexpected data. Do plugins have access to the Mysql error log? If so, how do I write to it? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to show highly frequent words in fulltext index ?
It looks perfect. Thanks Google for "myisam_ftdump", HTH Cor - Original Message - From: "Sebastien Moretti" To: Sent: Thursday, April 23, 2009 5:24 PM Subject: How to show highly frequent words in fulltext index ? Hi, Is there a command to see which words are highly frequent in a fulltext index ? Thanks -- Sébastien Moretti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to show highly frequent words in fulltext index ?
Yes, you can use myisam_ftdump Bye On Thu, 23 Apr 2009 17:24:09 +0200, Sebastien Moretti wrote: > Hi, > > Is there a command to see which words are highly frequent in a fulltext > index ? > > Thanks > > -- > Sébastien Moretti > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=corrado.pandi...@inter.it -- -- Corrado Pandiani web project manager / DBA inter.it F.C.Internazionale Milano Spa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to show highly frequent words in fulltext index ?
Hi, Is there a command to see which words are highly frequent in a fulltext index ? Thanks -- Sébastien Moretti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: help with fulltext search
Stefan,
On Sun, Mar 8, 2009 at 1:50 PM, Stefan Onken wrote:
> Hello,
>
> I am bit puzzled about combining mysql fulltext search into our
> current search:
>
> I am not able to combine a fulltext search with other selections,
> please see http://pastebin.com/m23622c39 for full details. The
> moment I am using "...where a=2 OR match (bla) AGAINST ('foo') mysql
> is not using the index... WHY ?
This is happening because MySQL can't use two indexes, and neither
index can satisfy all the criteria. In some cases MySQL can use more
than one index for a query, but not when one of them is fulltext and
one is a B-Tree index.
You should be able to get the results you want with UNION. Write one
query that gets what you want from the full-text index, then another
that gets the things that can be found without it, then UNION them
together.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
help with fulltext search
Hello,
I am bit puzzled about combining mysql fulltext search into our
current search:
I am not able to combine a fulltext search with other selections,
please see http://pastebin.com/m23622c39 for full details. The
moment I am using "...where a=2 OR match (bla) AGAINST ('foo') mysql
is not using the index... WHY ?
Stonki
--
www.stonki.de
www.proftpd.de
www.kbarcode.net
www.krename.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: accent sensitivity in fulltext search
Thanks Santino, but unfortunately didn't help .
I tried utf8_bin, just for checking, same results¸
Not even the case sensitivity is respected.
The following queries return the same results:
select name from people where match(name) against ('"königsberger"' in
boolean mode);
select name from people where match(name) against ('"koNigsberger"' in
boolean mode);
Königsberger
konigsberger
Could it be a version dependent problem?
Regards
Salam
On Mon, 2009-02-16 at 20:26 +0100, Santino wrote:
> Hi,
> try to set the collation to utf8_unicode_ci.
> I have had the inverse problem and I solved with utf8_general_ci.
>
> Santino
>
> At 19:33 +0100 16-02-2009, Salam Baker Shanawa wrote:
> >Hi,
> >
> >How can I have accent sensitive, case insensitive fulltext query?
> >
> >version: 5.0.45
> >The database, tables, connection, data etc. are all utf8.
> >
> >select name from people where match(name) against ('"königsberger"' in
> >boolean mode);
> >
> >shouldn't return konigsberger.
> >
> >Any idea, collation?
> >
> >Regards
> >Salam
> >
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:
> >http://lists.mysql.com/mysql?unsub=santino.cusim...@gmail.com
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: accent sensitivity in fulltext search
Hi,
try to set the collation to utf8_unicode_ci.
I have had the inverse problem and I solved with utf8_general_ci.
Santino
At 19:33 +0100 16-02-2009, Salam Baker Shanawa wrote:
Hi,
How can I have accent sensitive, case insensitive fulltext query?
version: 5.0.45
The database, tables, connection, data etc. are all utf8.
select name from people where match(name) against ('"königsberger"' in
boolean mode);
shouldn't return konigsberger.
Any idea, collation?
Regards
Salam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=santino.cusim...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
accent sensitivity in fulltext search
Hi,
How can I have accent sensitive, case insensitive fulltext query?
version: 5.0.45
The database, tables, connection, data etc. are all utf8.
select name from people where match(name) against ('"königsberger"' in
boolean mode);
shouldn't return konigsberger.
Any idea, collation?
Regards
Salam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[Q] FULLTEXT index question
Can one make a composite index with FULLTEXT for one column and standard indexing on another? For instance we have a table CREATE TABLE OurData ( TheText TEXT, TheLanguageID INTEGER ); We have a FULLTEXT index on TheText, but want to be able to do searches on TheText AND TheLanguageID. So, an index like ( FULLTEXT TheText, TheLanguageID ) would be nice. Is this possible? Tim... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Q) FullText (UTF8)
Have you tried "in boolean mode"?
Santino Cusimano
At 16:30 -0500 20-11-2008, Little, Timothy wrote:
We are using MySQL 5.0.22 on CENTOS/redhat
linux. The table and database character-sets
are all utf8.
We have a database supporting numerous
languages. Of course, full-text works
beautifully with most of the languages.
But Chinese and Japanese are giving us problems,
and there is NO reason why it should be a
problem since we are taking measures to help the
database see word-breaks.
When we insert the Chinese and Japanese
passages, they have spaces (normal ASCII
$14-#32) between each word (verified). So
basically if you have two words like
{APPLE}{DRUM} then we put {APPLE} then space
then {DRUM}. If you have UTF-8 then you can
look at this sample, éOçø±Í¾ó ä åíËâÀ
When we try to match either {APPLE} or {DRUM}
individually (or technically éOçø±Í¾ó ä or
åíËâÀ ) then MySQL fails to find a match
against anything. But clearly it should find
those.
MySQL is only finding matches for Japanese and
Chinese on exact full-string matches, which is
clearly less than ideal.
I have already changed the ft min length setting to 1, to no avail.
What is going wrong, and how do I fix this?
Here is my sample query (selecting for ONE word
select *
from category_attributes
where match ( value ) against ( 'éOçø±Í¾ó ä' ) > 0
When I replace the word withåíËâÀ then it
still doesn't match anything. And there is a
row with merely
éOçø±Í¾ó ä spaceåíËâÀ
Tim...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
(Q) FullText (UTF8)
We are using MySQL 5.0.22 on CENTOS/redhat linux. The table and database
character-sets are all utf8.
We have a database supporting numerous languages. Of course, full-text works
beautifully with most of the languages.
But Chinese and Japanese are giving us problems, and there is NO reason why it
should be a problem since we are taking measures to help the database see
word-breaks.
When we insert the Chinese and Japanese passages, they have spaces (normal
ASCII $14-#32) between each word (verified). So basically if you have two
words like {APPLE}{DRUM} then we put {APPLE} then space then {DRUM}. If you
have UTF-8 then you can look at this sample, 三坐标测量机 固定架
When we try to match either {APPLE} or {DRUM} individually (or technically
三坐标测量机 or 固定架 ) then MySQL fails to find a match against anything. But
clearly it should find those.
MySQL is only finding matches for Japanese and Chinese on exact full-string
matches, which is clearly less than ideal.
I have already changed the ft min length setting to 1, to no avail.
What is going wrong, and how do I fix this?
Here is my sample query (selecting for ONE word
select *
from category_attributes
where match ( value ) against ( '三坐标测量机' ) > 0
When I replace the word with固定架 then it still doesn't match anything. And
there is a row with merely
三坐标测量机 space固定架
Tim...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ignoring some characters to build FULLTEXT index
Hi all: I am trying to build a FULLTEXT index with several particularities. It must ignore some special characters inside index words. For example: If I have the text: I'll go to the ci[ne]ma. I want the FULLTEXT include the word cinema, not ci[ne]ma nor ci or ne or ma. So, I want the index ignore [ and ] to build the index. Is what in Oracle nomenclature names skipjoins. I know I can modify which characters can ben part of a word throw true_word_char() or misc_word_char() but, what about this behaviour? Is it possible to tell my sql to ignore some character for building the index? I am testing this in mysql 5.0 and 5.1 Thanks in advance, Mario Barcala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
> -Original Message- > From: Ananda Kumar [mailto:[EMAIL PROTECTED] > Sent: Friday, June 13, 2008 10:48 AM > To: [EMAIL PROTECTED] > Cc: mos; mysql@lists.mysql.com > Subject: Re: Fulltext index -first query slow, subsequent queries fast > > Hi > > Is sphinxsearch avialable only on for windows > > regards > anandkl > On 6/13/08, Rory McKinley <[EMAIL PROTECTED]> wrote: > > > > mos wrote: > > > > > >> Why not switch to Sphinx full text search for MySQL? It is faster > and can > >> handle more data than MySQL's built in fulltext search. > >> http://www.sphinxsearch.com/ ---8<--- snip GIYF: http://www.linux.com/feature/118721 I believe the *binaries* are only *pre-compiled* for Windows. Search the Sphinx site for info about compilation, or crack open one of the .tgz (an immediate "I'm probably a *nix package" flag) releases. I'll bet you can compile it yourself on *nix. (The article I've linked to also talks about using Sphinx in BSD distros.) Hope that helps, Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
Hi Is sphinxsearch avialable only on for windows regards anandkl On 6/13/08, Rory McKinley <[EMAIL PROTECTED]> wrote: > > mos wrote: > > >> Why not switch to Sphinx full text search for MySQL? It is faster and can >> handle more data than MySQL's built in fulltext search. >> http://www.sphinxsearch.com/ >> >> Mike >> > > > I have read about sphinx and the good performance boost it provides - > unfortunately there is a lot of legacy code reading off the db, so I will > need to get all sorts of stuff signed off, before I can make any major > changes ;). > > But I will definitely look into it so that I can offer it as a possible > solution. > > Regards > > Rory > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: Fulltext index -first query slow, subsequent queries fast
mos wrote: Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike I have read about sphinx and the good performance boost it provides - unfortunately there is a lot of legacy code reading off the db, so I will need to get all sorts of stuff signed off, before I can make any major changes ;). But I will definitely look into it so that I can offer it as a possible solution. Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
At 02:20 PM 6/12/2008, you wrote: Hi List I have a table with a fulltext index across five fields, with about 2.2 million records and a data size of about 5.6 GB (index another 3.5 GB). When I test a query that uses fulltext matching, the first run takes about 15-16 seconds to complete. The second run takes about 0.1 sec and subsequent runs will all all ge around the 0.1 sec time. The query that I use has a call to NOW() as one of the criteria, so i know that the query results will not be cached. Yet, the times would suggest that some sort of caching effect is being observed. Is there something I can do that can return more consistent query performance - hopefully with a time somewhere between the two extremes? Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
>From: Rory McKinley [mailto:[EMAIL PROTECTED] >Sent: Thursday, June 12, 2008 4:57 PM >To: Jerry Schwartz; mysql >Subject: Re: Fulltext index -first query slow, subsequent queries fast > >Jerry Schwartz wrote: >> File system, or disk caching, uses some kind of algorithm to hold >chunks of >> files in system RAM. That way a program can get to it more quickly >than if >> it had to go out to the disk. The algorithms vary, depending upon the >smarts >> of the program and the smarts of the file system. The system might >keep the >> most recently used stuff, the most frequently used stuff, even the >stuff it >> thinks you will need based upon the pattern of use. >> >> Regards, > > >Hi Jerry > >Thanks for the explanation. > >So, in short, I am most likely hitting a wall with the fulltext index, >and I am just getting lucky cos of the disk caching, but I am not going >to be able to get away from that initial slow load...rats. [JS] I can't agree or disagree. I used to be a performance consultant, but that was in another life and I don't know anything about MySQL's own caching algorithms. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
Jerry Schwartz wrote: File system, or disk caching, uses some kind of algorithm to hold chunks of files in system RAM. That way a program can get to it more quickly than if it had to go out to the disk. The algorithms vary, depending upon the smarts of the program and the smarts of the file system. The system might keep the most recently used stuff, the most frequently used stuff, even the stuff it thinks you will need based upon the pattern of use. Regards, Hi Jerry Thanks for the explanation. So, in short, I am most likely hitting a wall with the fulltext index, and I am just getting lucky cos of the disk caching, but I am not going to be able to get away from that initial slow load...rats. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
>-Original Message- >From: Rory McKinley [mailto:[EMAIL PROTECTED] >Sent: Thursday, June 12, 2008 3:20 PM >To: mysql >Subject: Fulltext index -first query slow, subsequent queries fast > >Hi List > >I have a table with a fulltext index across five fields, with about 2.2 >million records and a data size of about 5.6 GB (index another 3.5 GB). >When I test a query that uses fulltext matching, the first run takes >about 15-16 seconds to complete. The second run takes about 0.1 sec and >subsequent runs will all all ge around the 0.1 sec time. > >The query that I use has a call to NOW() as one of the criteria, so i >know that the query results will not be cached. Yet, the times would >suggest that some sort of caching effect is being observed. [JS] It may well be the file system that is doing the caching. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext index -first query slow, subsequent queries fast
Hi List
I have a table with a fulltext index across five fields, with about 2.2
million records and a data size of about 5.6 GB (index another 3.5 GB).
When I test a query that uses fulltext matching, the first run takes
about 15-16 seconds to complete. The second run takes about 0.1 sec and
subsequent runs will all all ge around the 0.1 sec time.
The query that I use has a call to NOW() as one of the criteria, so i
know that the query results will not be cached. Yet, the times would
suggest that some sort of caching effect is being observed.
Is there something I can do that can return more consistent query
performance - hopefully with a time somewhere between the two extremes?
The structure of the table as well as the query are below.
All help will be appreciated.
CREATE TABLE `article` (
`article_id` int(11) NOT NULL auto_increment,
`site_id` int(11) NOT NULL default '0',
`article_code` varchar(80) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`publication_id` int(11) default '0',
`rating_id` int(11) NOT NULL default '0',
`status_id` int(11) NOT NULL default '0',
`section_id` int(11) NOT NULL default '-1',
`template_id` int(11) default NULL,
`headline1` varchar(255) default NULL,
`headline2` varchar(255) default NULL,
`headline3` varchar(255) default NULL,
`live` enum('Y','N') NOT NULL default 'N',
`modified_date` datetime NOT NULL default '-00-00 00:00:00',
`original_date` datetime NOT NULL default '-00-00 00:00:00',
`flash` enum('Y','N') default NULL,
`expiry_date` datetime default NULL,
`embargo_date` datetime default NULL,
`embargo_hour` int(11) default NULL,
`embargo_day` int(11) default NULL,
`message` varchar(255) default NULL,
`section_front` enum('Y','N') NOT NULL default 'N',
`front_page` enum('Y','N') NOT NULL default 'N',
`author_id` int(11) default '0',
`is_urgent` enum('Y','N') NOT NULL default 'N',
`live_date` datetime default NULL,
`page_number` int(11) NOT NULL default '0',
`is_free` enum('Y','N') NOT NULL default 'N',
`source_id` int(11) default NULL,
`edition` int(11) default NULL,
`master_article` int(11) default NULL,
`newspapersection_id` int(11) default NULL,
`blurb` text NOT NULL,
`body` text NOT NULL,
`is_indexed` enum('Y','N') NOT NULL default 'N',
`zone` varchar(255) NOT NULL default '',
`warning` varchar(255) NOT NULL default '',
`blurb_is_intro` enum('Y','N') default 'N',
PRIMARY KEY (`article_id`),
KEY `site_id` (`site_id`),
KEY `article_code` (`article_code`),
KEY `name` (`name`),
KEY `publication_id` (`publication_id`),
KEY `rating_id` (`rating_id`),
KEY `status_id` (`status_id`),
KEY `section_id` (`section_id`),
KEY `live` (`live`),
KEY `modified_date` (`modified_date`),
KEY `original_date` (`original_date`),
KEY `expiry_date` (`expiry_date`),
KEY `section_front` (`section_front`),
KEY `front_page` (`front_page`),
KEY `live_date` (`live_date`),
KEY `is_urgent` (`is_urgent`),
KEY `page_number` (`page_number`),
KEY `author_id` (`author_id`),
KEY `embargo_date` (`embargo_date`),
KEY `master_article` (`master_article`),
KEY `newspapersection_id` (`newspapersection_id`),
KEY `site_status` (`site_id`,`status_id`),
KEY `flash` (`flash`),
FULLTEXT KEY `blurb`
(`blurb`,`body`,`headline1`,`headline2`,`headline3`)) ENGINE=MyISAM
DEFAULT CHARSET=latin1 MAX_ROWS=1000 AVG_ROW_LENGTH=2586;
SELECT SQL_CALC_FOUND_ROWS article_id AS iArticleId, MATCH(blurb, body,
headline1, headline2, headline3) AGAINST ("microsoft") AS dRelevance
FROM article WHERE embargo_date <= NOW() AND status_id IN (-1, -6, -10)
AND site_id = 45 AND MATCH(blurb, body, headline1, headline2, headline3)
AGAINST ("microsoft") ORDER BY embargo_date DESC LIMIT 0, 25
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance on FULLTEXT search.
no there is no index building or alter doing on.
On 6/13/08, mos <[EMAIL PROTECTED]> wrote:
>
> At 11:38 AM 6/12/2008, you wrote:
>
>> Hi Sebastian,
>>
>> I tried to order the column as close as possible to the table structure
>> and
>> removed all the formatn command and if conditions, but still it take 3 min
>>
>
> Are you sure when you are running the fulltext search, the table isn't
> locked because you are building the index or altering the table?
>
> Mike
>
>
>
> select
>>
>>
>> ITEM_ID,ITEM_TITL,AUCT_START_DATE,AUCT_END_DATE,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,LEAF_CATEG_ID,SLR_ID,START_PRICE_USD,RSRV_PRICE_USD,CURNT_PRICE_USD,BIN_PRICE_USD,GMS_USD,QTY_SOLD,QTY_AVAIL,
>>
>> BIDCOUNT,BIN_SOLD_FLAG,SUCCESS_YN,BOLD_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
>> GALLERY_FEE_FLAG,GALLERY_FEATURED_FEE_FLAG, HIGHLIGHT_FEE_FLAG,RSRV_FLAG
>> from ebay_Research where match(ITEM_TITL) AGAINST('BABY SUIT') and
>> CURNT_PRICE_USD between 0 and limit 1000
>>
>>
>>
>> On 6/12/08, Ananda Kumar <[EMAIL PROTECTED]> wrote:
>> >
>> >
>> >
>> > On 6/12/08, Ananda Kumar <[EMAIL PROTECTED]> wrote:
>> >>
>> >>
>> >>
>> >> On 6/12/08, Sebastian Mendel <[EMAIL PROTECTED]> wrote:
>> >>>
>> >>> Ananda Kumar schrieb:
>> >>>
>> >>>> Hi All,
>> >>>> We have table with 99 Million records, with fulltext index.
>> >>>> But when there is not load the sql's performance in just 6 sec, but
>> when
>> >>>> anyother jobs like Index creation or data load is happening its take
>> >>>> close
>> >>>> to 3 min for the same query to execute, any ways to improve the
>> >>>> performance
>> >>>> of this query.
>> >>>>
>> >>>> I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
>> >>>>
>> >>>> mysql> explain select
>> >>>>->
>> >>>>->
>> >>>>
>> >>>>
>> ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
>> >>>>->
>> >>>>
>> >>>>
>> BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
>> >>>>->
>> >>>> GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
>> >>>>
>> >>>
>> >>> are this fields in same order as in table?
>> >>
>> >>
>> >> no not in the same order will this have any performance impact.
>> >>
>> >> DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
>> >>>>'> %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
>> >>>>
>> >>>
>> >>> AUCT_START_DATE, AUCT_END_DATE
>> >>>
>> >>> why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
>> >>> H:i:s ?
>> >>
>> >>
>> >> Good catch, mysql gives in the y-m-d H:i:s format
>> >>
>> >>
>> >> %H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
>> >>>>-> AGAINST('BOOKS') > 0 and
>> >>>>
>> >>>
>> >>> why "> 0" ?
>> >>
>> >>
>> >> I need to check with developer
>> >>
>> >> CURNT_PRICE_USD between ifnull(null,0) and
>> >>>> ifnull(null,) limit 1000;
>> >>>>
>> >>>
>> >>> CURNT_PRICE_USD between 0 and
>> >>
>> >>
>> >>
>> >> Also need to check with developer, Just guessing, they want for prince
>> >> between 0 and 999
>> >>
>> >> why ifnull(null, ...) ?
>> >>>
>> >>>
>> >>>>
>> ++-+---+--+--+--+-+--+--+-+
>> >>>> | id | select_type | table | type | possible_keys
>> >>>> |
>> >>>> key | key_len | ref | rows | Extra |
>> >>>>
>> >>>>
>> ++-+---+--+--+--+-+--+--+-+
>> >>>> | 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
>> >>>> ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
>> >>>>
>> >>>>
>> ++-+---+--+--+--+-+--+--+-+
>> >>>> 1 row in set (0.05 sec)
>> >>>>
>> >>>>
>> >>> --
>> >>> Sebastian Mendel
>> >>>
>> >>
>> >>
>> >
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Re: improve performance on FULLTEXT search.
At 11:38 AM 6/12/2008, you wrote:
Hi Sebastian,
I tried to order the column as close as possible to the table structure and
removed all the formatn command and if conditions, but still it take 3 min
Are you sure when you are running the fulltext search, the table isn't
locked because you are building the index or altering the table?
Mike
select
ITEM_ID,ITEM_TITL,AUCT_START_DATE,AUCT_END_DATE,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,LEAF_CATEG_ID,SLR_ID,START_PRICE_USD,RSRV_PRICE_USD,CURNT_PRICE_USD,BIN_PRICE_USD,GMS_USD,QTY_SOLD,QTY_AVAIL,
BIDCOUNT,BIN_SOLD_FLAG,SUCCESS_YN,BOLD_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
GALLERY_FEE_FLAG,GALLERY_FEATURED_FEE_FLAG, HIGHLIGHT_FEE_FLAG,RSRV_FLAG
from ebay_Research where match(ITEM_TITL) AGAINST('BABY SUIT') and
CURNT_PRICE_USD between 0 and limit 1000
On 6/12/08, Ananda Kumar <[EMAIL PROTECTED]> wrote:
>
>
>
> On 6/12/08, Ananda Kumar <[EMAIL PROTECTED]> wrote:
>>
>>
>>
>> On 6/12/08, Sebastian Mendel <[EMAIL PROTECTED]> wrote:
>>>
>>> Ananda Kumar schrieb:
>>>
>>>> Hi All,
>>>> We have table with 99 Million records, with fulltext index.
>>>> But when there is not load the sql's performance in just 6 sec, but when
>>>> anyother jobs like Index creation or data load is happening its take
>>>> close
>>>> to 3 min for the same query to execute, any ways to improve the
>>>> performance
>>>> of this query.
>>>>
>>>> I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
>>>>
>>>> mysql> explain select
>>>>->
>>>>->
>>>>
>>>>
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
>>>>->
>>>>
>>>>
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
>>>>->
>>>> GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
>>>>
>>>
>>> are this fields in same order as in table?
>>
>>
>> no not in the same order will this have any performance impact.
>>
>> DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
>>>>'> %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
>>>>
>>>
>>> AUCT_START_DATE, AUCT_END_DATE
>>>
>>> why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
>>> H:i:s ?
>>
>>
>> Good catch, mysql gives in the y-m-d H:i:s format
>>
>>
>> %H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
>>>>-> AGAINST('BOOKS') > 0 and
>>>>
>>>
>>> why "> 0" ?
>>
>>
>> I need to check with developer
>>
>> CURNT_PRICE_USD between ifnull(null,0) and
>>>> ifnull(null,) limit 1000;
>>>>
>>>
>>> CURNT_PRICE_USD between 0 and
>>
>>
>>
>> Also need to check with developer, Just guessing, they want for prince
>> between 0 and 999
>>
>> why ifnull(null, ...) ?
>>>
>>>
>>>>
++-+---+--+--+--+-+--+--+-+
>>>> | id | select_type | table | type | possible_keys
>>>> |
>>>> key | key_len | ref | rows | Extra |
>>>>
>>>>
++-+---+--+--+--+-+--+--+-+
>>>> | 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
>>>> ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
>>>>
>>>>
++-+---+--+--+--+-+--+--+-+
>>>> 1 row in set (0.05 sec)
>>>>
>>>>
>>> --
>>> Sebastian Mendel
>>>
>>
>>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improve performance on FULLTEXT search.
Hi Sebastian,
I tried to order the column as close as possible to the table structure and
removed all the formatn command and if conditions, but still it take 3 min
select
ITEM_ID,ITEM_TITL,AUCT_START_DATE,AUCT_END_DATE,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,LEAF_CATEG_ID,SLR_ID,START_PRICE_USD,RSRV_PRICE_USD,CURNT_PRICE_USD,BIN_PRICE_USD,GMS_USD,QTY_SOLD,QTY_AVAIL,
BIDCOUNT,BIN_SOLD_FLAG,SUCCESS_YN,BOLD_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
GALLERY_FEE_FLAG,GALLERY_FEATURED_FEE_FLAG, HIGHLIGHT_FEE_FLAG,RSRV_FLAG
from ebay_Research where match(ITEM_TITL) AGAINST('BABY SUIT') and
CURNT_PRICE_USD between 0 and limit 1000
On 6/12/08, Ananda Kumar <[EMAIL PROTECTED]> wrote:
>
>
>
> On 6/12/08, Ananda Kumar <[EMAIL PROTECTED]> wrote:
>>
>>
>>
>> On 6/12/08, Sebastian Mendel <[EMAIL PROTECTED]> wrote:
>>>
>>> Ananda Kumar schrieb:
>>>
>>>> Hi All,
>>>> We have table with 99 Million records, with fulltext index.
>>>> But when there is not load the sql's performance in just 6 sec, but when
>>>> anyother jobs like Index creation or data load is happening its take
>>>> close
>>>> to 3 min for the same query to execute, any ways to improve the
>>>> performance
>>>> of this query.
>>>>
>>>> I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
>>>>
>>>> mysql> explain select
>>>>->
>>>>->
>>>>
>>>> ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
>>>>->
>>>>
>>>> BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
>>>>->
>>>> GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
>>>>
>>>
>>> are this fields in same order as in table?
>>
>>
>> no not in the same order will this have any performance impact.
>>
>> DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
>>>>'> %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
>>>>
>>>
>>> AUCT_START_DATE, AUCT_END_DATE
>>>
>>> why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
>>> H:i:s ?
>>
>>
>> Good catch, mysql gives in the y-m-d H:i:s format
>>
>>
>> %H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
>>>>-> AGAINST('BOOKS') > 0 and
>>>>
>>>
>>> why "> 0" ?
>>
>>
>> I need to check with developer
>>
>> CURNT_PRICE_USD between ifnull(null,0) and
>>>> ifnull(null,) limit 1000;
>>>>
>>>
>>> CURNT_PRICE_USD between 0 and
>>
>>
>>
>> Also need to check with developer, Just guessing, they want for prince
>> between 0 and 999
>>
>> why ifnull(null, ...) ?
>>>
>>>
>>>> ++-+---+--+--+--+-+--+--+-+
>>>> | id | select_type | table | type | possible_keys
>>>> |
>>>> key | key_len | ref | rows | Extra |
>>>>
>>>> ++-+---+--+--+--+-+--+--+-+
>>>> | 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
>>>> ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
>>>>
>>>> ++-+---+--+--+--+-+--+--+-+
>>>> 1 row in set (0.05 sec)
>>>>
>>>>
>>> --
>>> Sebastian Mendel
>>>
>>
>>
>
Re: improve performance on FULLTEXT search.
On 6/12/08, Ananda Kumar <[EMAIL PROTECTED]> wrote:
>
>
>
> On 6/12/08, Sebastian Mendel <[EMAIL PROTECTED]> wrote:
>>
>> Ananda Kumar schrieb:
>>
>>> Hi All,
>>> We have table with 99 Million records, with fulltext index.
>>> But when there is not load the sql's performance in just 6 sec, but when
>>> anyother jobs like Index creation or data load is happening its take
>>> close
>>> to 3 min for the same query to execute, any ways to improve the
>>> performance
>>> of this query.
>>>
>>> I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
>>>
>>> mysql> explain select
>>>->
>>>->
>>>
>>> ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
>>>->
>>>
>>> BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
>>>->
>>> GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
>>>
>>
>> are this fields in same order as in table?
>
>
> no not in the same order will this have any performance impact.
>
> DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
>>>'> %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
>>>
>>
>> AUCT_START_DATE, AUCT_END_DATE
>>
>> why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
>> H:i:s ?
>
>
> Good catch, mysql gives in the y-m-d H:i:s format
>
>
> %H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
>>>-> AGAINST('BOOKS') > 0 and
>>>
>>
>> why "> 0" ?
>
>
> I need to check with developer
>
> CURNT_PRICE_USD between ifnull(null,0) and
>>> ifnull(null,) limit 1000;
>>>
>>
>> CURNT_PRICE_USD between 0 and
>
>
>
> Also need to check with developer, Just guessing, they want for prince
> between 0 and 999
>
> why ifnull(null, ...) ?
>>
>>
>>> ++-+---+--+--+--+-+--+--+-+
>>> | id | select_type | table | type | possible_keys
>>> |
>>> key | key_len | ref | rows | Extra |
>>>
>>> ++-+---+--+--+--+-+--+--+-+
>>> | 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
>>> ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
>>>
>>> ++-+---+--+--+--+-+--+--+-+
>>> 1 row in set (0.05 sec)
>>>
>>>
>> --
>> Sebastian Mendel
>>
>
>
Re: improve performance on FULLTEXT search.
On 6/12/08, Sebastian Mendel <[EMAIL PROTECTED]> wrote:
>
> Ananda Kumar schrieb:
>
>> Hi All,
>> We have table with 99 Million records, with fulltext index.
>> But when there is not load the sql's performance in just 6 sec, but when
>> anyother jobs like Index creation or data load is happening its take close
>> to 3 min for the same query to execute, any ways to improve the
>> performance
>> of this query.
>>
>> I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
>>
>> mysql> explain select
>>->
>>->
>>
>> ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
>>->
>>
>> BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
>>->
>> GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
>>
>
> are this fields in same order as in table?
no not in the same order
DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
>>'> %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
>>
>
> AUCT_START_DATE, AUCT_END_DATE
>
> why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d
> H:i:s ?
Good catch, mysql gives in the y-m-d H:i:s format
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
>>-> AGAINST('BOOKS') > 0 and
>>
>
> why "> 0" ?
I need to check with developer
CURNT_PRICE_USD between ifnull(null,0) and
>> ifnull(null,) limit 1000;
>>
>
> CURNT_PRICE_USD between 0 and
Also need to check with developer, Just guessing, they want for prince
between 0 and 999
why ifnull(null, ...) ?
>
>
>> ++-+---+--+--+--+-+--+--+-+
>> | id | select_type | table | type | possible_keys|
>> key | key_len | ref | rows | Extra |
>>
>> ++-+---+--+--+--+-+--+--+-+
>> | 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
>> ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
>>
>> ++-+---+--+--+--+-+--+--+-+
>> 1 row in set (0.05 sec)
>>
>>
> --
> Sebastian Mendel
>
Re: improve performance on FULLTEXT search.
Ananda Kumar schrieb:
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take close
to 3 min for the same query to execute, any ways to improve the performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql> explain select
->
->
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
->
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
->
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,
are this fields in same order as in table?
DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
'> %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
AUCT_START_DATE, AUCT_END_DATE
why DATE_FORMAT? doesn't MySQL return datetime fields already as Y-m-d H:i:s ?
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
-> AGAINST('BOOKS') > 0 and
why "> 0" ?
CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
CURNT_PRICE_USD between 0 and
why ifnull(null, ...) ?
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
improve performance on FULLTEXT search.
Hi All,
We have table with 99 Million records, with fulltext index.
But when there is not load the sql's performance in just 6 sec, but when
anyother jobs like Index creation or data load is happening its take close
to 3 min for the same query to execute, any ways to improve the performance
of this query.
I have set innodb_buffer=11GB, key_buffer=3GB, 8 CPU, total 16 GB RAM
mysql> explain select
->
->
ITEM_ID,LEAF_CATEG_ID,SLR_ID,ITEM_TITL,CURNT_PRICE_USD,START_PRICE_USD,RSRV_PRICE_USD,BIN_PRICE_USD,QTY_AVAIL,QTY_SOLD,
->
BIDCOUNT,AUCT_DURTN_DAYS,AUCT_TYPE_CODE,SUCCESS_YN,BIN_SOLD_FLAG,BOLD_FEE_FLAG,HIGHLIGHT_FEE_FLAG,GALLERY_FEE_FLAG,
->
GALLERY_FEATURED_FEE_FLAG,FEATURED_FEE_FLAG,GIFT_FEE_FLAG,DATE_FORMAT(AUCT_START_DATE,'%Y-%m-%d
'> %H:%i:%s'),DATE_FORMAT(AUCT_END_DATE,'%Y-%m-%d
%H:%i:%s'),GMS_USD,RSRV_FLAG from amc_rch where match(ITEM_TITL)
-> AGAINST('BOOKS') > 0 and CURNT_PRICE_USD between ifnull(null,0) and
ifnull(null,) limit 1000;
++-+---+--+--+--+-+--+--+-+
| id | select_type | table | type | possible_keys|
key | key_len | ref | rows | Extra |
++-+---+--+--+--+-----+--+--+-+
| 1 | SIMPLE | amc_rch | fulltext | ER_IT_CTX_IDX_0805201045 |
ER_IT_CTX_IDX_0805201045 | 0 | |1 | Using where |
++-+---+--+--+--+-+--+--+-+
1 row in set (0.05 sec)
spurious select ERROR 1191 when insert into ... select * is done on fulltext table
>Description: copying a table with a fulltext index via insert into ft1 select * from ft2; into a identical table sometimes leads to select error 1191 when concurrent select's are running. this happens in an enviroment where the searched table is periodically created as a temporary table and finally copied into the searched table. so the locking time is restricted to copying and the searches running parallel are not locked while creating the new search table. the error 1191 occurs to the search selects after copying, but only sometimes not always. without running concurrent selects while copying, the error seems not to happen. the mysql database version is fresh fetched from the server: mysql-5.0.51a-linux-i686-glibc23.tar.gz but this error could be reproduced with v5.0.45 too. >How-To-Repeat: script 1: create a table with a fulltext indexed column, fill with some data and run selects on this table in a loop. in this test the select result is not of interest, only if there is a problem flagged, or a success exit code. script t1.sh: --- #!/bin/bash ## ## create table ft1 with one fulltext column: ## mysql -B test < /dev/null if [ $? = 0 ] ; then echo -n . fi done exit 0 --- script 2: the second script does the copying forth and back: - drop the temp table, create and fill it. - lock - empty search table and fill from temp table - unlock running the second script with a parameter sets the count how many times this copying should be done. script t2.sh: --- #!/bin/bash if [ "$1" != "" ] ; then loop=$1 else loop=9 fi i=0 while [ $i -lt $loop ] ; do i=$(($i + 1)) mysql -B test <Fix: a fix is not known. a usable workaround seems to be adding a repair table quick: insert into ft1 select * from ft2; + repair table ft1 quick; unlock tables; >Submitter-Id: [EMAIL PROTECTED] >Originator:Erik Schoenfelder >Organization: Gaertner Datensysteme GbR, E-Mail: [EMAIL PROTECTED] 38114 Braunschweig, Hamburger Str. 273a, Germany, Gesellschafter: Christine Müller, Martin Neitzel, Ulrich Schwarz, Dr. Stefan Gärtner >MySQL support: none >Synopsis: spurious select ERROR 1191 when insert into .. select * is done >on fulltext table >Severity: serious >Priority: low >Category: mysql >Class: sw-bug >Release: mysql-5.0.51a (MySQL Community Server (GPL)) >Server: /usr/local/bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for >redhat-linux-gnu on i686 Copyright (C) 2000-2006 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.51a-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld-5.sock Uptime: 23 hours 6 min 53 sec Threads: 20 Questions: 1404381461 Slow queries: 565 Opens: 4037 Flush tables: 1 Open tables: 780 Queries per second avg: 16876.948 >C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) >C++ compiler: gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) >Environment: System: Linux d4 2.6.24.2 #1 SMP Tue Feb 12 12:38:01 CET 2008 i686 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i486-linux-gnu Configured with: ../src/configure -v --enable-languages=c,c++,fortran,objc,obj-c++,treelang --prefix=/usr --enable-shared --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --enable-nls --program-suffix=-4.1 --enable-__cxa_atexit --enable-clocale=gnu --enable-libstdcxx-debug --enable-mpfr --with-tune=i686 --enable-checking=release i486-linux-gnu Thread model: posix gcc version 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) Compilation info: CC='ccache gcc' CFLAGS='' CXX='ccache gcc' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Feb 18 14:39 /lib/libc.so.6 -> libc-2.3.6.so -rwxr-xr-x 1 root root 1153784 Apr 8 2003 /lib/libc-2.2.5.so -rwxr-xr-x 1 root root 1147548 Jan 19 13:14 /lib/libc-2.3.6.so -rw-r--r-- 1 root root 2602934 Jan 19 13:14 /usr/lib/libc.a -rwxr-xr-x 1 root root 204 Jan 19 12:53 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql'
Re: different results between FULLTEXT search and LIKE search
--- Lamp Lists <[EMAIL PROTECTED]> wrote:
>
> --- Lamp Lists <[EMAIL PROTECTED]> wrote:
>
> > hi,
> > I created table "tasks"
> > create table tasks(
> > task_id, int(4) not null primary key,
> > task text not null,
> > resolution text not null,
> > fulltext (task, resolution)
> > )engine=myisam
> >
> > when I run
> >
> > seect * from tasks match(task,resolution)
> > against('"certain service"' in boolean mode)
> >
> > I would get one record and the phrase is in
> > resolution
> > column.
> >
> > though, when I serach using LIKE
> > select * from tasks where task like '%certain
> > service%' or resolution like '%certain service%'
> > I would get 2 records. one record is the same as
> the
> > one above and the 2nd has the phrase in task
> column.
> > means there are 2 records, but fulltext shows me
> > only
> > one.
> >
> > what I'm doing wrong?
> >
> > thanks.
> >
> > -ll
> >
>
>
> just made 2nd test and got different reault too:
>
> select * from tasks match(task,resolution)
> against('+certain +service' in boolean mode)
> result: 232 records
>
> select * from tasks where (task like '%certain%' and
> task like '%service%') or (resolution like
> '%certain%'
> and resolution like '%service%')
> result: 7 records
>
> ?!?!!??
>
> -ll
>
>
>
>
>
>
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile. Try it now.
>
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
even worse:
select count(*) from tasks match(task,resolution)
against('certain')
result: 0
select count(*) from tasks where task like '%certain%'
or resolution like '%certain%';
result: 173
?
-ll
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results between FULLTEXT search and LIKE search
--- Lamp Lists <[EMAIL PROTECTED]> wrote:
> hi,
> I created table "tasks"
> create table tasks(
> task_id, int(4) not null primary key,
> task text not null,
> resolution text not null,
> fulltext (task, resolution)
> )engine=myisam
>
> when I run
>
> seect * from tasks match(task,resolution)
> against('"certain service"' in boolean mode)
>
> I would get one record and the phrase is in
> resolution
> column.
>
> though, when I serach using LIKE
> select * from tasks where task like '%certain
> service%' or resolution like '%certain service%'
> I would get 2 records. one record is the same as the
> one above and the 2nd has the phrase in task column.
> means there are 2 records, but fulltext shows me
> only
> one.
>
> what I'm doing wrong?
>
> thanks.
>
> -ll
>
just made 2nd test and got different reault too:
select * from tasks match(task,resolution)
against('+certain +service' in boolean mode)
result: 232 records
select * from tasks where (task like '%certain%' and
task like '%service%') or (resolution like '%certain%'
and resolution like '%service%')
result: 7 records
?!?!!??
-ll
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
different results between FULLTEXT search and LIKE search
hi,
I created table "tasks"
create table tasks(
task_id, int(4) not null primary key,
task text not null,
resolution text not null,
fulltext (task, resolution)
)engine=myisam
when I run
seect * from tasks match(task,resolution)
against('"certain service"' in boolean mode)
I would get one record and the phrase is in resolution
column.
though, when I serach using LIKE
select * from tasks where task like '%certain
service%' or resolution like '%certain service%'
I would get 2 records. one record is the same as the
one above and the 2nd has the phrase in task column.
means there are 2 records, but fulltext shows me only
one.
what I'm doing wrong?
thanks.
-ll
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
VIOLA!
As it turns out, the cnf file that I was shown was the cnf file on the OLD
server that we used when we had DB/Webserver combined on one server - that
conf was not updated for the new separate DB server.
Thanks muchly all, that change made the search work as expected!!!
Thanks for the starting point for finding that mistake Baron! :)
*knocks head against wall repeatedly*
On 10/30/07 5:09 PM, "Baron Schwartz" <[EMAIL PROTECTED]> wrote:
> Mike,
>
> Mike Morton wrote:
>> OK - I am at a total loss here :)
>>
>> We have added an addition fulltext field with the highest rating:
>> match(search_keywords) against ('vic*' IN BOOLEAN MODE) * 16
>>
>> And verified that in that field, there is indeed a keyword "vic" - but still
>> - that result is not returned, SO...
>>
>> That leads me to believe, that despite what my eyes see in the my.cnf:
>> ft_min_word_len = 2
>
> SHOW VARIABLES LIKE 'ft%';
>
> Should show you the variables you care about.
--
Cheers
Mike Morton
*
* Tel: 905-465-1263
* Email: [EMAIL PROTECTED]
*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
Mike,
Mike Morton wrote:
OK - I am at a total loss here :)
We have added an addition fulltext field with the highest rating:
match(search_keywords) against ('vic*' IN BOOLEAN MODE) * 16
And verified that in that field, there is indeed a keyword "vic" - but still
- that result is not returned, SO...
That leads me to believe, that despite what my eyes see in the my.cnf:
ft_min_word_len = 2
SHOW VARIABLES LIKE 'ft%';
Should show you the variables you care about.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
OK - I am at a total loss here :)
We have added an addition fulltext field with the highest rating:
match(search_keywords) against ('vic*' IN BOOLEAN MODE) * 16
And verified that in that field, there is indeed a keyword "vic" - but still
- that result is not returned, SO...
That leads me to believe, that despite what my eyes see in the my.cnf:
ft_min_word_len = 2
Is indeed, not true.
So:
1. How do I confirm that when MYSQL starts up, it is indeed paying
attention the the file that I assume it is, /etc/my.cnf
2. How can I confirm whether this setting is confirmed as min word length
of 2, rather than the default 3?
3. Am I losing my mind that this search is not returning the result set
that I expect? Am I missing something in my query, is the search being
performed trying to match something I am not expecting?
I guess I am expecting the match to work similar to the like query, except
that it is looking for a string of "vic" somewhere within the field...
Perhaps it is something to do with the "*" appended on there? I thought
that as a wildcard operator it would match " vic ", "vichon", "vickey",
"abcvicdef" equally, but am I mistaken in that? If so, how can I replicate
a "like" type search with '%vic%' using fulltext, which is our intention?
The reason that I am using fulltext is:
1. I understand that is may be faster than a simple like search
2. The number of fields we are using for searching
3. The "relevancy" ranking that we are doing in this case
Am I wrong? I am really pushing the envelope of my MYSQL knowledge here, so
assistance is appreciated ;) I am not adverse to reading documentation,
just sometimes confused by what I am reading :)
TIA!
On 10/30/07 10:12 AM, "Jerry Schwartz" <[EMAIL PROTECTED]> wrote:
> Sorry, no. I hope someone else will be able to help.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>> -Original Message-
>> From: Mike Morton [mailto:[EMAIL PROTECTED]
>> Sent: Tuesday, October 30, 2007 9:56 AM
>> To: Jerry Schwartz; mysql@lists.mysql.com
>> Subject: Re: Fulltext Relevancy not returning anticipated results?
>>
>> Jerry:
>>
>> Sorry - I should have mentioned in my previous email that we have
>> changed
>> that min. word length to 2 in the config already, as we have MANY 3
>> letter
>> searches... ;)
>>
>> Any other suggestions?
>>
>>
>> On 10/30/07 9:12 AM, "Jerry Schwartz" <[EMAIL PROTECTED]>
>> wrote:
>>
>>> By default, MySQL ignores any "word" with less than four characters
>> when
>>> doing full text searches. You can change this in my.conf, the setting
>> should
>>> be obvious.
>>>
>>> I had to do this so that customers could find inventory items that
>> were red.
>>>
>>> Regards,
>>>
>>> Jerry Schwartz
>>> The Infoshop by Global Information Incorporated
>>> 195 Farmington Ave.
>>> Farmington, CT 06032
>>>
>>> 860.674.8796 / FAX: 860.674.8341
>>>
>>> www.the-infoshop.com
>>> www.giiexpress.com
>>> www.etudes-marche.com
>>>
>>>> -Original Message-
>>>> From: Mike Morton [mailto:[EMAIL PROTECTED]
>>>> Sent: Monday, October 29, 2007 5:39 PM
>>>> To: mysql@lists.mysql.com
>>>> Subject: Fulltext Relevancy not returning anticipated results?
>>>>
>>>> I have a database of products, doing a search on them trying to
>> achieve
>>>> a
>>>> modicum of relevancy, but am getting a strange result on some
>> returned
>>>> rows:
>>>>
>>>> QUERY:
>>>> select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 +
>> match(name)
>>>> against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against
>>>> ('vic*' IN
>>>> BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN
>> MODE)
>>>> as
>>>> score from products where active='y' and site like '%,1,%' and
>>>> match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN
>> MODE)
>>>> order by score desc
>>>>
>>>> I get returned 6 rows from the product database. The strange thing
>> is,
>>>> there is a product (at l
RE: Fulltext Relevancy not returning anticipated results?
Sorry, no. I hope someone else will be able to help.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
> -Original Message-
> From: Mike Morton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 30, 2007 9:56 AM
> To: Jerry Schwartz; mysql@lists.mysql.com
> Subject: Re: Fulltext Relevancy not returning anticipated results?
>
> Jerry:
>
> Sorry - I should have mentioned in my previous email that we have
> changed
> that min. word length to 2 in the config already, as we have MANY 3
> letter
> searches... ;)
>
> Any other suggestions?
>
>
> On 10/30/07 9:12 AM, "Jerry Schwartz" <[EMAIL PROTECTED]>
> wrote:
>
> > By default, MySQL ignores any "word" with less than four characters
> when
> > doing full text searches. You can change this in my.conf, the setting
> should
> > be obvious.
> >
> > I had to do this so that customers could find inventory items that
> were red.
> >
> > Regards,
> >
> > Jerry Schwartz
> > The Infoshop by Global Information Incorporated
> > 195 Farmington Ave.
> > Farmington, CT 06032
> >
> > 860.674.8796 / FAX: 860.674.8341
> >
> > www.the-infoshop.com
> > www.giiexpress.com
> > www.etudes-marche.com
> >
> >> -Original Message-
> >> From: Mike Morton [mailto:[EMAIL PROTECTED]
> >> Sent: Monday, October 29, 2007 5:39 PM
> >> To: mysql@lists.mysql.com
> >> Subject: Fulltext Relevancy not returning anticipated results?
> >>
> >> I have a database of products, doing a search on them trying to
> achieve
> >> a
> >> modicum of relevancy, but am getting a strange result on some
> returned
> >> rows:
> >>
> >> QUERY:
> >> select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 +
> match(name)
> >> against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against
> >> ('vic*' IN
> >> BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN
> MODE)
> >> as
> >> score from products where active='y' and site like '%,1,%' and
> >> match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN
> MODE)
> >> order by score desc
> >>
> >> I get returned 6 rows from the product database. The strange thing
> is,
> >> there is a product (at least one that is known about), with the
> name:
> >> "Salchichon de Vic" that is NOT being returned as a result.
> >>
> >> When I break out the scores, I get a match on the first three
> products
> >> returned for the titles:
> >>
> >> 6-Pack Vichy Catalan Sparkling Spring Water
> >> 2-Pack Vichy Catalan Mineral Water
> >> San Vicente - Tempranillo 2001
> >>
> >> And the other three products have a match in the large_desc.
> >>
> >> What is confusing me is why I am not getting any match on the titles
> >> for the
> >> "Salchichon de Vic" - which by my thought process should be the most
> >> relevant of all returns?
> >>
> >> Any thoughts on this? The term "vic" used in this case is the
> search
> >> string
> >> submitted by the user.
> >>
> >> You can see the search in action at
> >> http://www.tienda.com/support/search.html
> >>
> >> --
> >> Cheers
> >>
> >> Mike Morton
> >>
> >>
> >> *
> >> * Tel: 905-465-1263
> >> * Email: [EMAIL PROTECTED]
> >> *
> >>
> >>
> >>
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >> infoshop.com
> >
> >
> >
> >
>
> --
> Cheers
>
> Mike Morton
>
>
> *
> * Tel: 905-465-1263
> * Email: [EMAIL PROTECTED]
> *
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
Jerry:
Sorry - I should have mentioned in my previous email that we have changed
that min. word length to 2 in the config already, as we have MANY 3 letter
searches... ;)
Any other suggestions?
On 10/30/07 9:12 AM, "Jerry Schwartz" <[EMAIL PROTECTED]> wrote:
> By default, MySQL ignores any "word" with less than four characters when
> doing full text searches. You can change this in my.conf, the setting should
> be obvious.
>
> I had to do this so that customers could find inventory items that were red.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>> -Original Message-
>> From: Mike Morton [mailto:[EMAIL PROTECTED]
>> Sent: Monday, October 29, 2007 5:39 PM
>> To: mysql@lists.mysql.com
>> Subject: Fulltext Relevancy not returning anticipated results?
>>
>> I have a database of products, doing a search on them trying to achieve
>> a
>> modicum of relevancy, but am getting a strange result on some returned
>> rows:
>>
>> QUERY:
>> select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name)
>> against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against
>> ('vic*' IN
>> BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE)
>> as
>> score from products where active='y' and site like '%,1,%' and
>> match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE)
>> order by score desc
>>
>> I get returned 6 rows from the product database. The strange thing is,
>> there is a product (at least one that is known about), with the name:
>> "Salchichon de Vic" that is NOT being returned as a result.
>>
>> When I break out the scores, I get a match on the first three products
>> returned for the titles:
>>
>> 6-Pack Vichy Catalan Sparkling Spring Water
>> 2-Pack Vichy Catalan Mineral Water
>> San Vicente - Tempranillo 2001
>>
>> And the other three products have a match in the large_desc.
>>
>> What is confusing me is why I am not getting any match on the titles
>> for the
>> "Salchichon de Vic" - which by my thought process should be the most
>> relevant of all returns?
>>
>> Any thoughts on this? The term "vic" used in this case is the search
>> string
>> submitted by the user.
>>
>> You can see the search in action at
>> http://www.tienda.com/support/search.html
>>
>> --
>> Cheers
>>
>> Mike Morton
>>
>>
>> *
>> * Tel: 905-465-1263
>> * Email: [EMAIL PROTECTED]
>> *
>>
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>> infoshop.com
>
>
>
>
--
Cheers
Mike Morton
*
* Tel: 905-465-1263
* Email: [EMAIL PROTECTED]
*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Relevancy not returning anticipated results?
By default, MySQL ignores any "word" with less than four characters when
doing full text searches. You can change this in my.conf, the setting should
be obvious.
I had to do this so that customers could find inventory items that were red.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
> -Original Message-
> From: Mike Morton [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 29, 2007 5:39 PM
> To: mysql@lists.mysql.com
> Subject: Fulltext Relevancy not returning anticipated results?
>
> I have a database of products, doing a search on them trying to achieve
> a
> modicum of relevancy, but am getting a strange result on some returned
> rows:
>
> QUERY:
> select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name)
> against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against
> ('vic*' IN
> BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE)
> as
> score from products where active='y' and site like '%,1,%' and
> match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE)
> order by score desc
>
> I get returned 6 rows from the product database. The strange thing is,
> there is a product (at least one that is known about), with the name:
> "Salchichon de Vic" that is NOT being returned as a result.
>
> When I break out the scores, I get a match on the first three products
> returned for the titles:
>
> 6-Pack Vichy Catalan Sparkling Spring Water
> 2-Pack Vichy Catalan Mineral Water
> San Vicente - Tempranillo 2001
>
> And the other three products have a match in the large_desc.
>
> What is confusing me is why I am not getting any match on the titles
> for the
> "Salchichon de Vic" - which by my thought process should be the most
> relevant of all returns?
>
> Any thoughts on this? The term "vic" used in this case is the search
> string
> submitted by the user.
>
> You can see the search in action at
> http://www.tienda.com/support/search.html
>
> --
> Cheers
>
> Mike Morton
>
>
> *
> * Tel: 905-465-1263
> * Email: [EMAIL PROTECTED]
> *
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext Relevancy not returning anticipated results?
I have a database of products, doing a search on them trying to achieve a
modicum of relevancy, but am getting a strange result on some returned rows:
QUERY:
select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name)
against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against ('vic*' IN
BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE) as
score from products where active='y' and site like '%,1,%' and
match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE)
order by score desc
I get returned 6 rows from the product database. The strange thing is,
there is a product (at least one that is known about), with the name:
"Salchichon de Vic" that is NOT being returned as a result.
When I break out the scores, I get a match on the first three products
returned for the titles:
6-Pack Vichy Catalan Sparkling Spring Water
2-Pack Vichy Catalan Mineral Water
San Vicente - Tempranillo 2001
And the other three products have a match in the large_desc.
What is confusing me is why I am not getting any match on the titles for the
"Salchichon de Vic" - which by my thought process should be the most
relevant of all returns?
Any thoughts on this? The term "vic" used in this case is the search string
submitted by the user.
You can see the search in action at
http://www.tienda.com/support/search.html
--
Cheers
Mike Morton
*
* Tel: 905-465-1263
* Email: [EMAIL PROTECTED]
*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Index / Index .. both?
Justin wrote: I'm wanting to add a new field to my table that I will both be grouping by, searching with full text hits and plain likes.. I know I'll need a FULL TEXT index, but will that full text index also index like a normal index will? or should I also add an index too.. You'll need to add a normal index also. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext Index / Index .. both?
I'm wanting to add a new field to my table that I will both be grouping by, searching with full text hits and plain likes.. I know I'll need a FULL TEXT index, but will that full text index also index like a normal index will? or should I also add an index too.. thanks.
RE: fulltext search option
So far as I know, the only way to accomplish what you want is to do what you
tried: put the Boolean match in the WHERE clause and order by a natural
match. That's what I do on our web site.
By the way, you can't use prefix stemming: "*abcd" is the same as "abcd".
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
> -Original Message-
> From: Tanner Postert [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 26, 2007 7:56 PM
> To: mysql@lists.mysql.com
> Subject: fulltext search option
>
> I'm having a problem with the fulltext searching, and was
> looking for some
> help.
>
> i'm currently working with the following query:
>
> select table.*
> from table where match(title, description) against ('*search term*' IN
> BOOLEAN MODE)
>
> the reason I am using boolean mode, is so that it matches things like
> "search terms", rather than excluding those results if i left off the
> boolean mode.
>
> my problem, is that the exact phrase "search term" shows up
> in the result
> set, but is not ranked the highest since it's only a boolean
> search, its
> just checking if it exits.
>
> if I add: match(title,description) against ('search term') as score
> to my select and order by score, it works fine, but my query
> time goes from
> 0.0623 to 3.7 seconds. which seems like a huge give in
> performance, which I
> assume comes from ordering by a dynamically created field, so
> I tried to
> fake out the query by changing the query to
> select table.* from table where (match(title, description)
> against ('search
> term') or match(title, description) against ('*search term*'
> in boolean
> mode))
> but the ordering is still off.
>
> is there a way to have ordered by the non-boolean query, but
> still be able
> to include those results?
>
> seems bad that I can only have exact matches and proper
> ordering, or partial
> matches and no ordering.
>
>
>
>
>
> select video.id, video.title, video.description,
> (match(title, description) against ('woot monkey')) as straight,
> (match(title, description) against ('*woot monkey*' in
> boolean mode)) as
> stars,
> (match(title,description) against ('"woot monkey"' in boolean
> mode)) as
> exact
> from video where (match(title, description) against ('*woot
> monkey*' IN
> BOOLEAN MODE)
> and file_complete = 'true') order by exact DESC, stars DESC,
> straight DESC
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext search option
I'm having a problem with the fulltext searching, and was looking for some
help.
i'm currently working with the following query:
select table.*
from table where match(title, description) against ('*search term*' IN
BOOLEAN MODE)
the reason I am using boolean mode, is so that it matches things like
"search terms", rather than excluding those results if i left off the
boolean mode.
my problem, is that the exact phrase "search term" shows up in the result
set, but is not ranked the highest since it's only a boolean search, its
just checking if it exits.
if I add: match(title,description) against ('search term') as score
to my select and order by score, it works fine, but my query time goes from
0.0623 to 3.7 seconds. which seems like a huge give in performance, which I
assume comes from ordering by a dynamically created field, so I tried to
fake out the query by changing the query to
select table.* from table where (match(title, description) against ('search
term') or match(title, description) against ('*search term*' in boolean
mode))
but the ordering is still off.
is there a way to have ordered by the non-boolean query, but still be able
to include those results?
seems bad that I can only have exact matches and proper ordering, or partial
matches and no ordering.
select video.id, video.title, video.description,
(match(title, description) against ('woot monkey')) as straight,
(match(title, description) against ('*woot monkey*' in boolean mode)) as
stars,
(match(title,description) against ('"woot monkey"' in boolean mode)) as
exact
from video where (match(title, description) against ('*woot monkey*' IN
BOOLEAN MODE)
and file_complete = 'true') order by exact DESC, stars DESC, straight DESC
Re: Problem about fulltext search.
Thank you for your information.
It's really helpful.:)
It seems that I'll have to dig deep into the fulltext search functionality.
2007/7/2, ViSolve DB Team <[EMAIL PROTECTED]>:
Hi,
There are some words which are drawn as "Stop words" [Words which are not
searchable in Fulltext Database]. To know the list of stopwords,
http://dev.mysql.com/tech-resources/articles/full-text-revealed.html.
"hello" is a stopword, and hence your query fails to return rows.
Thanks
ViSolve DB Team
- Original Message -
From: "Niu Kun" <[EMAIL PROTECTED]>
To:
Sent: Sunday, July 01, 2007 7:53 AM
Subject: Re: Problem about fulltext search.
> Steve Edberg wrote:
>> At 11:23 PM +0800 6/30/07, Niu Kun wrote:
>>
>> To quote from
>>
>> http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
>>
>>
>> "... words that are present in more than 50% of the rows are considered
>> common and do not match."
>>
>> 'hello' appears in both (100%) of your records above, so it will not
>> match. You need to insert more test data before MySQL has enough words
>> to compute valid relevances.
>>
>> steve
>>
>
> Thank you for your advice.
> And again, I've got the following command.
> mysql> insert into test values(1,"aaa");
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into test values(1,"aaa");
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into test values(1,"aaa");
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into test values(1,"aaa");
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into test values(1,"aaa");
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from test where match(name) against("hello");
> Empty set (0.00 sec)
>
> mysql> select * from test where match(name) against("hello" in boolean
> mode);
> Empty set (0.00 sec)
>
> mysql> select * from test where match(name) against("world" in boolean
> mode);
> +--+-+
> | id | name|
> +--+-+
> |1 | hello world |
> +--+-+
> 1 row in set (0.00 sec)
>
> It seems that hello can't be found.
> But world can be found.
> I wonder if mysql has restrictions on the word to be found.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.476 / Virus Database: 269.9.14/883 - Release Date: 7/1/2007
> 12:19 PM
>
>
--
失业
牛坤
MSN:[EMAIL PROTECTED]
Re: Problem about fulltext search.
Hi,
There are some words which are drawn as "Stop words" [Words which are not
searchable in Fulltext Database]. To know the list of stopwords,
http://dev.mysql.com/tech-resources/articles/full-text-revealed.html.
"hello" is a stopword, and hence your query fails to return rows.
Thanks
ViSolve DB Team
- Original Message -
From: "Niu Kun" <[EMAIL PROTECTED]>
To:
Sent: Sunday, July 01, 2007 7:53 AM
Subject: Re: Problem about fulltext search.
Steve Edberg wrote:
At 11:23 PM +0800 6/30/07, Niu Kun wrote:
To quote from
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
"... words that are present in more than 50% of the rows are considered
common and do not match."
'hello' appears in both (100%) of your records above, so it will not
match. You need to insert more test data before MySQL has enough words
to compute valid relevances.
steve
Thank you for your advice.
And again, I've got the following command.
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test where match(name) against("hello");
Empty set (0.00 sec)
mysql> select * from test where match(name) against("hello" in boolean
mode);
Empty set (0.00 sec)
mysql> select * from test where match(name) against("world" in boolean
mode);
+--+-+
| id | name|
+--+-+
|1 | hello world |
+--+-+
1 row in set (0.00 sec)
It seems that hello can't be found.
But world can be found.
I wonder if mysql has restrictions on the word to be found.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.476 / Virus Database: 269.9.14/883 - Release Date: 7/1/2007
12:19 PM
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem about fulltext search.
Steve Edberg wrote:
> At 11:23 PM +0800 6/30/07, Niu Kun wrote:
>
> To quote from
>
> http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
>
>
> "... words that are present in more than 50% of the rows are considered
> common and do not match."
>
> 'hello' appears in both (100%) of your records above, so it will not
> match. You need to insert more test data before MySQL has enough words
> to compute valid relevances.
>
> steve
>
Thank you for your advice.
And again, I've got the following command.
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,"aaa");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test where match(name) against("hello");
Empty set (0.00 sec)
mysql> select * from test where match(name) against("hello" in boolean
mode);
Empty set (0.00 sec)
mysql> select * from test where match(name) against("world" in boolean
mode);
+--+-+
| id | name|
+--+-+
|1 | hello world |
+--+-+
1 row in set (0.00 sec)
It seems that hello can't be found.
But world can be found.
I wonder if mysql has restrictions on the word to be found.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem about fulltext search.
Octavian Rasnita wrote:
> Hi,
>
> Try:
>
> select * from test where match(name) against("hello" in boolean mode);
>
> Octavian
Thank you for your suggestion. I tried, but failed.:(
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem about fulltext search.
Hi,
Try:
select * from test where match(name) against("hello" in boolean mode);
Octavian
- Original Message -
From: "Niu Kun" <[EMAIL PROTECTED]>
To:
Sent: Saturday, June 30, 2007 6:23 PM
Subject: Problem about fulltext search.
Dear all,
I'm planning to add fulltext search to my database.
I've got the following test command:
create table test(id int, name varchar(20));
alter table test add fulltext(name);
insert into test values(1,"hello world");
insert into test values(1,"hello");
When I execute the fulltext search command, I've got the following.
mysql> select * from test where match(name) against("hello");
Empty set (0.00 sec)
Would anyone be kind enough to tell me how I can find hello in my text?
Any help would be appreciated.
Thanks in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem about fulltext search.
At 11:23 PM +0800 6/30/07, Niu Kun wrote:
Dear all,
I'm planning to add fulltext search to my database.
I've got the following test command:
create table test(id int, name varchar(20));
alter table test add fulltext(name);
insert into test values(1,"hello world");
insert into test values(1,"hello");
When I execute the fulltext search command, I've got the following.
mysql> select * from test where match(name) against("hello");
Empty set (0.00 sec)
Would anyone be kind enough to tell me how I can find hello in my text?
Any help would be appreciated.
Thanks in advance.
To quote from
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
"... words that are present in more than 50% of the rows are
considered common and do not match."
'hello' appears in both (100%) of your records above, so it will not
match. You need to insert more test data before MySQL has enough
words to compute valid relevances.
steve
--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem about fulltext search.
Dear all,
I'm planning to add fulltext search to my database.
I've got the following test command:
create table test(id int, name varchar(20));
alter table test add fulltext(name);
insert into test values(1,"hello world");
insert into test values(1,"hello");
When I execute the fulltext search command, I've got the following.
mysql> select * from test where match(name) against("hello");
Empty set (0.00 sec)
Would anyone be kind enough to tell me how I can find hello in my text?
Any help would be appreciated.
Thanks in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT + InnoDB = grounded?
Hello I've seen the posts of Nov 9 last year concerning the slow but steady development of FULLTEXT indexes for InnoDB. Has this feature been dropped or is it still being worked on? My background: I'm working on a Rails project which needs fulltext index search on the db layer. For now I can workaround with MyISAM, but if the feature is no longer on the list, then it would be better to choose another more cumbersome approach or switch the db entirely. Many thanks for a short update. Cheers, -sven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext substringsearches?
Sorry, you can't use a preceding asterisk. It is annoying. The only alternative is to use a LIKE operator. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Philipp Wabinski [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 20, 2007 9:31 AM > To: mysql@lists.mysql.com > Subject: fulltext substringsearches? > > > Hi, > > can somebody tell me, if it`s possible to search for substrings with a > fulltext search? > > We are using a fulltextsearch in boolean mode. I tried a lot > but the search > doesn`t find any substrings. > > The mysql reference just hast got an example like this: > > "apple*" that finds everything that begins with "apple". > > Is it possible to seachr for "*ppl*" and find entries that > contain apple or > something like this without a like-search and and a full table scan? > > Thanks for any help > > Philipp > -- > View this message in context: > http://www.nabble.com/fulltext-substringsearches--tf3433702.ht > ml#a9572461 > Sent from the MySQL - General mailing list archive at Nabble.com. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext substringsearches?
Hi, can somebody tell me, if it`s possible to search for substrings with a fulltext search? We are using a fulltextsearch in boolean mode. I tried a lot but the search doesn`t find any substrings. The mysql reference just hast got an example like this: "apple*" that finds everything that begins with "apple". Is it possible to seachr for "*ppl*" and find entries that contain apple or something like this without a like-search and and a full table scan? Thanks for any help Philipp -- View this message in context: http://www.nabble.com/fulltext-substringsearches--tf3433702.html#a9572461 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FullText Scoring With Two Databases
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 Neil, The short answer is probably "merge the two databases manually and rebuild the index". I don't think there's any real answer otherwise. Given the old version of MySQL you aren't going to be able to do anything fancy like federating the two tables to into another database and making a view out of the two or anything. Actually the only other possible alternative is to do a myisam_ftdump - -s on both databases and get the Average Global Weight for each database and then use one to "normalize" the results from the other, reading... http://www.databasejournal.com/features/mysql/article.php/3512461 ...states the the query weight (final score) also depends on the local weight but that's okay (I think). So, er, right... To get the approximate query score for database 1 from a score retrieved in database 2 multiply it by the Average Global Weight of db1 divided by the Average Global Weight of db2. I think, it's a bit late ;^). Hence... The approximate database one Query Score = database two's Query Score * (database one's Average Global Weight / database two's Average Global Weight) A) It is only an approximation, IT IS NOT accurate. Your mileage will vary depending on the similarity of the contents of the two databases. B) Get a second opinion on that. Try it and let us know how well it works. Regards, Phil > I've been using the MATCH() with FullText Scoring for quite a while now > on one table. I now need to combine the data from another database. I > have : > > Database1.Table1 > > with > > Database2.Table1 > > If I use the the FullText scoring using just one database/table it is > OK, but when I query the database1 and then database2 the scoring is > totally different because the data is different in each database. > > What would be the best way to overcome this problem. We are using a > early version of mysql 3.28 and can't upgrade at this time. > > Thanks for any help. > > Neil > > _ > Exclusive Ed Byrne daily comedy clips on MSN Video > http://specials.uk.msn.com/edbyrne/ > > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFF3jl4umMVa2pwPmkRCk62AJ9E30ogX1BqeKGiYqxcyWCpgjxH4QCfepxj qlTWplUZ3HsU3JsA4pm80Ls= =19pZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FullText Scoring With Two Databases
I've been using the MATCH() with FullText Scoring for quite a while now on one table. I now need to combine the data from another database. I have : Database1.Table1 with Database2.Table1 If I use the the FullText scoring using just one database/table it is OK, but when I query the database1 and then database2 the scoring is totally different because the data is different in each database. What would be the best way to overcome this problem. We are using a early version of mysql 3.28 and can't upgrade at this time. Thanks for any help. Neil _ Exclusive Ed Byrne daily comedy clips on MSN Video http://specials.uk.msn.com/edbyrne/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext relevance and weighting....
Guys,
I doubt I'd qualify as an expert but here's my two pence worth ( ;^)
)I wrote a search engine a while back that relies heavily on full-text
searching and the three things I found that improved results were...
1) Precisely what Dan explains, doing extra biasing per field in the SQL
with whatever extra info you've got is best. I had fields like
"seriesFK" that were null if it was a one off TV episode so I used the
result of (isnull(seriesFK) + 1) * {some multiplier}) and similar to
shove a whole TV Series above it's episodes. Perhaps you'd want to bias
the small_desc field more than the large_desc etc...
2) Adding/removing stop words to the |ft_stopword_file| and changing the
word length as you've already done.
3) Fiddling with the search requests before they get put into the SQL, I
was dealing with various people's names as well so and I knew they were
so I've got a bit of PHP that doubles two word queries up but with one
swapped around, so if the user enters "George Harrison" they'd actually
get a search for "George Harrison" and "Harrison, George".
Some of those you've done or are domain specific (i.e. the names),
sorry. ||Some other ideas I didn't get to try out were...
1) You can use the myisam_ftdump utility to dump the actual weightings
MySQL generates, load those stats back in and use it to generate a new
"meta" table then use that in combination with feedback about requested
search terms and followed links to make an engine that could to some
extent "learn".
2) the soundex() function would allow you to handle spelling mistakes
and might be of some use in your problem to bias those results that
sound most like the term? See
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html and search
for sondex.
The problem I found is that the clients (the people who are paying for
the search engine) knew, as expert in their subject what exact order
they'd like the results to come back in but you hit the problem that you
can't readily program that knowledge into a computer no matter how hard
you try or how fancy your algorithms get. I can't tell you the pain
"Charlie Chaplin" caused me, his real name is actually Charles but
without lots of extra contextual information to hand you can't program
that sort of knowledge and exception into a computer without spending
lots of time or money.
I wrote the search engine bit inside
http://ftvdb.bfi.org.uk/searches.php and they were happy with it once
we'd finished but I think this sort of area is somewhere you could spend
a lot of effort making little difference on small or medium sized
projects, there is a certain amount of "tweaking" that can be done but
eventually you'd need to move to a solution that is significantly more
technologically complex i.e. expensive & time consuming before achieve
noticeably better results.
If you want I'd be happy to outline and expand on some ideas.
Regards,
Phil
It is more of an issue to prioritizing fields for relevance, and whether it
is possible to do this within a fulltext query, or whether it needs to be
done through multiple queries, and then "outside" php processing of those
query results
You should be able to do what you need by making your 'score'
expression something like this:
select *,
match(code) against ('ham*' in boolean mode) * 8 +
match(name) against ('ham*' in boolean mode) * 4 +
match(small_desc) against ('ham*' in boolean mode) * 2 +
match(large_desc) against ('ham*' in boolean mode)
as score from products where active='y' and site like '%,1,%' and
match(code,name,small_desc,large_desc) against ('ham*' IN BOOLEAN MODE)
order by score desc
This takes advantage of the fact that boolean mode matches always
return 1 or 0, so a record matching in the "code" field will sort
higher than a record with "ham" in all 3 of the others but not in
"code".
Does anyone have any suggestions on how to solve the result
weighting problem? I have a client whose search results are
becoming more and more important, and the relevance demands on the
results are not entirely satisfactory...
The fields that are searched are code, name, small description and
large description, and are ranked in relevance in that order.
For example, a product with the name: "Bone-In Serrano Ham" should
ALWAYS outweigh the product with the name of "Boneless Jamon
Iberico", even if the Jamon Iberico has the word "ham" in the
description 20 times more than the Serrano product...
The query that is being run is: select
*,match(code,name,small_desc,large_desc) against ('ham*') as score
from products where active='y' and sit
RE: Fulltext search dilemma (IN BOOLEAN MODE) [RESOLVED]
Hello MySQL fellows,
The problem was solved after rebuilding all indices on the table by
using the table repair function.
I noticed the problem after the application returned an error issued by
MySQL that the table.myd file
was missing or corrupted.
Nevertheless, I don't know how that happened, the server was always shut
down properly.
-Andy
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 4:10 PM
To: Andreas Iwanowski
Cc: mysql@lists.mysql.com
Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
Sorry, I have no idea what is going wrong.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 01, 2007 2:22 PM
> To: Jerry Schwartz
> Cc: mysql@lists.mysql.com
> Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
>
> Hi, thank you for your reply.
>
> I have used the option ft_min_word_len=3.
> If I have something like
> 1. "Key West"
> in the database and I do a
>
> SELECT * FROM my_table WHERE MATCH(Keywords) AGAINST ('Key' IN BOOLEAN
> MODE)>0;
>
> then I don't get any results either.
> If I leave the quotation marks away, I find the row.
>
> However, the quotation marks are necessary for another part of the
> program, so I can't leave them out of the column.
>
> Any ideas why the quotation marks would cause this problem?
>
>
> -Andy
>
>
> -Original Message-
> From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 01, 2007 1:52 PM
> To: Andreas Iwanowski; mysql@lists.mysql.com
> Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
>
> Unless you changed the minimum word length, "Key" would be ignored
> because it is too short. I would think the quotation marks at the
> start or end of the words would be ignored. The asterisk operator is
> only valid at the end of a word. Those initial asterisks, and the
> quotation marks, would be treated as word delimiters as far as I can
> tell.
>
> Also, the asterisk operator is only valid at the end of a word, not in
> the beginning. The leading asterisks should be ignored, since they
> would be treated as word delimiters.
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
>
> > -Original Message-
> > From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, February 01, 2007 12:49 PM
> > To: mysql@lists.mysql.com
> > Subject: Fulltext search dilemma (IN BOOLEAN MODE)
> >
> > Hello MySQL experts,
> >
> > I'm trying to do a full text search on an indexed Keywords
> column that
>
> > contains quotation marks, and it's giving me a headache.
> >
> > Suppose there are records in the database containing the folling
> > keywords:
> > 1. "Miami Beach" City
> > 2. "Key West" Florida
> > 3. "Key West" Beach Florida
> >
> > Now I do two fulltext searches on this column like this:
> > SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Key*
> > *West*' IN BOOLEAN MODE)>0; SELECT * FROM _my_table_here WHERE
> > MATCH(Keywords) AGAINST ('*Miami* *Beach*' IN BOOLEAN MODE)>0;
> >
> > The second query returns the correct rows in spite of the "
> preceding
> > Miami.
> > The first one, however, returns no results, even though
> there are rows
>
> > in the table that *should* match the query.
> > I think is has something to do with noise words, since if I
> have a row
>
> > like this one:
> >
> > 4. Key West
> >
> > Then the problem does not occur and the search returns that row.
> >
> >
> > Any help would be appreciated.
> >
> >
> >
> > -Andy
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search dilemma (IN BOOLEAN MODE)
Sorry, I have no idea what is going wrong.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 01, 2007 2:22 PM
> To: Jerry Schwartz
> Cc: mysql@lists.mysql.com
> Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
>
> Hi, thank you for your reply.
>
> I have used the option ft_min_word_len=3.
> If I have something like
> 1. "Key West"
> in the database and I do a
>
> SELECT * FROM my_table WHERE MATCH(Keywords) AGAINST ('Key' IN BOOLEAN
> MODE)>0;
>
> then I don't get any results either.
> If I leave the quotation marks away, I find the row.
>
> However, the quotation marks are necessary for another part of the
> program,
> so I can't leave them out of the column.
>
> Any ideas why the quotation marks would cause this problem?
>
>
> -Andy
>
>
> -Original Message-
> From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 01, 2007 1:52 PM
> To: Andreas Iwanowski; mysql@lists.mysql.com
> Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
>
> Unless you changed the minimum word length, "Key" would be ignored
> because it is too short. I would think the quotation marks at
> the start
> or end of the words would be ignored. The asterisk operator is only
> valid at the end of a word. Those initial asterisks, and the quotation
> marks, would be treated as word delimiters as far as I can tell.
>
> Also, the asterisk operator is only valid at the end of a word, not in
> the beginning. The leading asterisks should be ignored, since
> they would
> be treated as word delimiters.
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
>
> > -Original Message-
> > From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, February 01, 2007 12:49 PM
> > To: mysql@lists.mysql.com
> > Subject: Fulltext search dilemma (IN BOOLEAN MODE)
> >
> > Hello MySQL experts,
> >
> > I'm trying to do a full text search on an indexed Keywords
> column that
>
> > contains quotation marks, and it's giving me a headache.
> >
> > Suppose there are records in the database containing the folling
> > keywords:
> > 1. "Miami Beach" City
> > 2. "Key West" Florida
> > 3. "Key West" Beach Florida
> >
> > Now I do two fulltext searches on this column like this:
> > SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Key*
> > *West*' IN BOOLEAN MODE)>0; SELECT * FROM _my_table_here WHERE
> > MATCH(Keywords) AGAINST ('*Miami* *Beach*' IN BOOLEAN MODE)>0;
> >
> > The second query returns the correct rows in spite of the "
> preceding
> > Miami.
> > The first one, however, returns no results, even though
> there are rows
>
> > in the table that *should* match the query.
> > I think is has something to do with noise words, since if I
> have a row
>
> > like this one:
> >
> > 4. Key West
> >
> > Then the problem does not occur and the search returns that row.
> >
> >
> > Any help would be appreciated.
> >
> >
> >
> > -Andy
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search dilemma (IN BOOLEAN MODE)
Hi, thank you for your reply.
I have used the option ft_min_word_len=3.
If I have something like
1. "Key West"
in the database and I do a
SELECT * FROM my_table WHERE MATCH(Keywords) AGAINST ('Key' IN BOOLEAN
MODE)>0;
then I don't get any results either.
If I leave the quotation marks away, I find the row.
However, the quotation marks are necessary for another part of the
program,
so I can't leave them out of the column.
Any ideas why the quotation marks would cause this problem?
-Andy
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 1:52 PM
To: Andreas Iwanowski; mysql@lists.mysql.com
Subject: RE: Fulltext search dilemma (IN BOOLEAN MODE)
Unless you changed the minimum word length, "Key" would be ignored
because it is too short. I would think the quotation marks at the start
or end of the words would be ignored. The asterisk operator is only
valid at the end of a word. Those initial asterisks, and the quotation
marks, would be treated as word delimiters as far as I can tell.
Also, the asterisk operator is only valid at the end of a word, not in
the beginning. The leading asterisks should be ignored, since they would
be treated as word delimiters.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 01, 2007 12:49 PM
> To: mysql@lists.mysql.com
> Subject: Fulltext search dilemma (IN BOOLEAN MODE)
>
> Hello MySQL experts,
>
> I'm trying to do a full text search on an indexed Keywords column that
> contains quotation marks, and it's giving me a headache.
>
> Suppose there are records in the database containing the folling
> keywords:
> 1. "Miami Beach" City
> 2. "Key West" Florida
> 3. "Key West" Beach Florida
>
> Now I do two fulltext searches on this column like this:
> SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Key*
> *West*' IN BOOLEAN MODE)>0; SELECT * FROM _my_table_here WHERE
> MATCH(Keywords) AGAINST ('*Miami* *Beach*' IN BOOLEAN MODE)>0;
>
> The second query returns the correct rows in spite of the " preceding
> Miami.
> The first one, however, returns no results, even though there are rows
> in the table that *should* match the query.
> I think is has something to do with noise words, since if I have a row
> like this one:
>
> 4. Key West
>
> Then the problem does not occur and the search returns that row.
>
>
> Any help would be appreciated.
>
>
>
> -Andy
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search dilemma (IN BOOLEAN MODE)
Unless you changed the minimum word length, "Key" would be ignored because
it is too short. I would think the quotation marks at the start or end of
the words would be ignored. The asterisk operator is only valid at the end
of a word. Those initial asterisks, and the quotation marks, would be
treated as word delimiters as far as I can tell.
Also, the asterisk operator is only valid at the end of a word, not in the
beginning. The leading asterisks should be ignored, since they would be
treated as word delimiters.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 01, 2007 12:49 PM
> To: mysql@lists.mysql.com
> Subject: Fulltext search dilemma (IN BOOLEAN MODE)
>
> Hello MySQL experts,
>
> I'm trying to do a full text search on an indexed Keywords column that
> contains quotation marks, and it's giving me a headache.
>
> Suppose there are records in the database containing the folling
> keywords:
> 1. "Miami Beach" City
> 2. "Key West" Florida
> 3. "Key West" Beach Florida
>
> Now I do two fulltext searches on this column like this:
> SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Key*
> *West*' IN BOOLEAN MODE)>0;
> SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Miami*
> *Beach*' IN BOOLEAN MODE)>0;
>
> The second query returns the correct rows in spite of the " preceding
> Miami.
> The first one, however, returns no results, even though there are rows
> in the table that *should* match the query.
> I think is has something to do with noise words, since if I have a row
> like this one:
>
> 4. Key West
>
> Then the problem does not occur and the search returns that row.
>
>
> Any help would be appreciated.
>
>
>
> -Andy
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search dilemma (IN BOOLEAN MODE)
Hello MySQL experts,
I'm trying to do a full text search on an indexed Keywords column that
contains quotation marks, and it's giving me a headache.
Suppose there are records in the database containing the folling
keywords:
1. "Miami Beach" City
2. "Key West" Florida
3. "Key West" Beach Florida
Now I do two fulltext searches on this column like this:
SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Key*
*West*' IN BOOLEAN MODE)>0;
SELECT * FROM _my_table_here WHERE MATCH(Keywords) AGAINST ('*Miami*
*Beach*' IN BOOLEAN MODE)>0;
The second query returns the correct rows in spite of the " preceding
Miami.
The first one, however, returns no results, even though there are rows
in the table that *should* match the query.
I think is has something to do with noise words, since if I have a row
like this one:
4. Key West
Then the problem does not occur and the search returns that row.
Any help would be appreciated.
-Andy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
