[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 <haya...@clear-code.com> -- 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 <haya...@clear-code.com> 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 <haya...@clear-code.com> 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 <haya...@clear-code.com> 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 <haya...@clear-code.com>
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 <haya...@clear-code.com>
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 <haya...@clear-code.com>
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 <haya...@clear-code.com> 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 <haya...@clear-code.com> 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 <haya...@clear-code.com> 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 <haya...@clear-code.com> 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 <haya...@clear-code.com> 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 <sam...@protonmail.com> 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 <haya...@clear-code.com> -- Kentaro Hayashi <haya...@clear-code.com> 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 <haya...@clear-code.com>
[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 <haya...@clear-code.com> 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 <haya...@clear-code.com> 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 <haya...@clear-code.com> 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 <haya...@clear-code.com> 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 <yokoy...@clear-code.com> ClearCode Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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: help with fulltext search
Stefan,
On Sun, Mar 8, 2009 at 1:50 PM, Stefan Onken supp...@stonki.de 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
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
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
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
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)
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]
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.
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.
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.
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.
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]
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: 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]
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: 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: mysql@lists.mysql.com 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: mysql@lists.mysql.com 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]
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]
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]
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: mysql@lists.mysql.com 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.
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.
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]
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]
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)
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) [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 optimization
Most of the queries you listed below probably aren't taking advantage of the full text index. MySQL doesn't consider song.mp3 or
document.pdf words, they would be considered two words because the period is considered a word separator. Keep in mind MySQL indexes
words not character strings. Punctuation (ACE-HIGH) and numbers (007) aren't indexed either. Again the hyphen is a word separator.
Also, searching on ends with won't use an index. So your *.mp3 won't be able to use the index. That's probably why everything is
taking so long. You might as well be searching on LIKE %.mp3%, probably would be about just as fast, and you could use a memory
table if you wanted.
- Original Message -
From: Svilen Spasov (Ancient Media) [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, July 13, 2006 9:57 AM
Subject: Re: fulltext search optimization
Thanks for your respond.
Here is the CREATE TABLE:
CREATE TABLE `results_1` (
`id` int(11) NOT NULL auto_increment,
`filename` varchar(255) collate cp1251_bulgarian_ci default NULL,
`fileext` varchar(10) collate cp1251_bulgarian_ci default NULL,
`username` varchar(16) collate cp1251_bulgarian_ci default NULL,
`filesize` float default NULL,
`date` datetime default NULL,
`is_dir` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `results_1_filesize` (`filesize`),
KEY `results_1_username` (`username`),
KEY `results_1_filename_1` (`filename`),
FULLTEXT KEY `results_1_filename` (`filename`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_bulgarian_ci
More details:
SELECT count(*) FROM results_1;
+--+
| count(*)
+--+
| 2399315
+--+
1 row in set (0.03 sec)
And some sample data:
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857845, '04 - Iron Maiden - Iron
Maiden.mp3', 'mp3', 'drawer', '7.6546e+06', '2006-03-05 01:28:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857844, '03 - Iron Maiden - Fear Of The
Dark.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:29:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857843, '02 - Iron Maiden - Hallowed Be
Thy Name.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:33:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857842, '01 - Iron Maiden - No More
Lies.mp3', 'mp3', 'drawer', '1.36315e+07', '2006-03-05 01:31:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize, date, is_dir) VALUES (857841, '09 - Iron Maiden - Lord Of The
Flies.mp3', 'mp3', 'drawer', '8.70318e+06', '2006-03-05 01:24:00', 0);
And I logged some of the queries which executes for more than 20sec:
query timing: 28.8102879524
SELECT id, filename, filesize, date, MATCH (filename, username) AGAINST('+*.mp3' IN BOOLEAN MODE) as coef FROM results_1 WHERE
MATCH (filename, username) AGAINST('+*.mp3' IN BOOLEAN MODE) 0 ORDER BY coef, filename DESC LIMIT 0, 50
query timing: 36.2581338882
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username)
AGAINST('+007' IN BOOLEAN MODE) 0
query timing: 31.0913391113
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username) AGAINST('+ACE-HIGH +MP3' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username) AGAINST('+ACE-HIGHMP3' IN BOOLEAN
MODE) 0
query timing: 32.1210138798
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username)
AGAINST('+.pdf' IN BOOLEAN MODE) 0
query timing: 29.8846437931
SELECT id, filename, filesize, date, MATCH (filename, username) AGAINST('+star +craft +mp3' IN BOOLEAN MODE) as coef FROM
results_1 WHERE MATCH (filename, username) AGAINST('+star +craft +mp3' IN BOOLEAN MODE) 0 UNION ALL SELECT id, filename,
filesize, date, MATCH (filename, username) AGAINST('+starcraft' IN BOOLEAN MODE) as coef FROM results_1 WHERE MATCH (filename,
username) AGAINST ('+starcraft' IN BOOLEAN MODE) 0 UNION ALL SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+starcraftmp3' IN BOOLEAN MODE) as coef FROM results_1 WHERE MATCH (filename, username) AGAINST('+starcraftmp3' IN
BOOLEAN MODE) 0 ORDER BY coef, filename LIMIT 0, 50
query timing: 28.6531541348
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username) AGAINST('+need +somebody +to +love' IN BOOLEAN
MODE) 0 UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username) AGAINST('+needsomebody' IN
BOOLEAN MODE) 0 UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename, username)
AGAINST('+needsomebodyto' IN BOOLEAN MODE) 0 UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+needsomebodytolove' IN BOOLEAN MODE) 0
I've tried
Re: fulltext search optimization
Thanks for your respond.
Here is the CREATE TABLE:
CREATE TABLE `results_1` (
`id` int(11) NOT NULL auto_increment,
`filename` varchar(255) collate cp1251_bulgarian_ci default NULL,
`fileext` varchar(10) collate cp1251_bulgarian_ci default NULL,
`username` varchar(16) collate cp1251_bulgarian_ci default NULL,
`filesize` float default NULL,
`date` datetime default NULL,
`is_dir` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `results_1_filesize` (`filesize`),
KEY `results_1_username` (`username`),
KEY `results_1_filename_1` (`filename`),
FULLTEXT KEY `results_1_filename` (`filename`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_bulgarian_ci
More details:
SELECT count(*) FROM results_1;
+--+
| count(*)
+--+
| 2399315
+--+
1 row in set (0.03 sec)
And some sample data:
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857845, '04 - Iron Maiden - Iron Maiden.mp3',
'mp3', 'drawer', '7.6546e+06', '2006-03-05 01:28:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857844, '03 - Iron Maiden - Fear Of The
Dark.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:29:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857843, '02 - Iron Maiden - Hallowed Be Thy
Name.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:33:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857842, '01 - Iron Maiden - No More Lies.mp3',
'mp3', 'drawer', '1.36315e+07', '2006-03-05 01:31:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857841, '09 - Iron Maiden - Lord Of The
Flies.mp3', 'mp3', 'drawer', '8.70318e+06', '2006-03-05 01:24:00', 0);
And I logged some of the queries which executes for more than 20sec:
query timing: 28.8102879524
SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+*.mp3' IN BOOLEAN MODE) as coef FROM results_1 WHERE MATCH
(filename, username) AGAINST('+*.mp3' IN BOOLEAN MODE) 0 ORDER BY
coef, filename DESC LIMIT 0, 50
query timing: 36.2581338882
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+007' IN BOOLEAN MODE) 0
query timing: 31.0913391113
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+ACE-HIGH +MP3' IN BOOLEAN MODE) 0 UNION ALL
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+ACE-HIGHMP3' IN BOOLEAN MODE) 0
query timing: 32.1210138798
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+.pdf' IN BOOLEAN MODE) 0
query timing: 29.8846437931
SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+star +craft +mp3' IN BOOLEAN MODE) as coef FROM results_1
WHERE MATCH (filename, username) AGAINST('+star +craft +mp3' IN
BOOLEAN MODE) 0 UNION ALL SELECT id, filename, filesize, date,
MATCH (filename, username) AGAINST('+starcraft' IN BOOLEAN MODE) as
coef FROM results_1 WHERE MATCH (filename, username) AGAINST
('+starcraft' IN BOOLEAN MODE) 0 UNION ALL SELECT id, filename,
filesize, date, MATCH (filename, username) AGAINST('+starcraftmp3' IN
BOOLEAN MODE) as coef FROM results_1 WHERE MATCH (filename, username)
AGAINST('+starcraftmp3' IN BOOLEAN MODE) 0 ORDER BY coef,
filename LIMIT 0, 50
query timing: 28.6531541348
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+need +somebody +to +love' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebody' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebodyto' IN BOOLEAN MODE) 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebodytolove' IN BOOLEAN MODE) 0
I've tried with the MEMORY storage engine, unfortunately it doesn't
support fulltext indexes.
Svilen Spasov
On Jul 12, 2006, at 7:05 PM, John Hicks wrote:
Svilen Spasov (Ancient Media) wrote:
Hello,
I have a website with a MySQL database and I have a table with ~2
millions row (usernames, filenames; ~120MB db data file and ~230MB
db index file) with I would like to search using the fulltext
indeces.
Unfortunately the server get loaded pretty much. It always stays
on 20 load average and often gets 50-60 load average.
I'm sure that this is because of slow mysql response (slow
queries) to the apache web server.
Can you give me some advice how to optimize the mysql server?
The server hardware is: 1GB RAM, 1.8GHz Celeron, 40GB HDD
(currently I have 512MB free memory)
Run a SHOW CREATE TABLE tablename on the table and post the
results
fulltext search optimization
Hello, I have a website with a MySQL database and I have a table with ~2 millions row (usernames, filenames; ~120MB db data file and ~230MB db index file) with I would like to search using the fulltext indeces. Unfortunately the server get loaded pretty much. It always stays on 20 load average and often gets 50-60 load average. I'm sure that this is because of slow mysql response (slow queries) to the apache web server. Can you give me some advice how to optimize the mysql server? The server hardware is: 1GB RAM, 1.8GHz Celeron, 40GB HDD (currently I have 512MB free memory) Thanks, Svilen Spasov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search optimization
Svilen Spasov (Ancient Media) wrote: Hello, I have a website with a MySQL database and I have a table with ~2 millions row (usernames, filenames; ~120MB db data file and ~230MB db index file) with I would like to search using the fulltext indeces. Unfortunately the server get loaded pretty much. It always stays on 20 load average and often gets 50-60 load average. I'm sure that this is because of slow mysql response (slow queries) to the apache web server. Can you give me some advice how to optimize the mysql server? The server hardware is: 1GB RAM, 1.8GHz Celeron, 40GB HDD (currently I have 512MB free memory) Run a SHOW CREATE TABLE tablename on the table and post the results here. (We're particularly interested in how it is indexed.) Tell us what queries are being run against the table. If possible post the actual SQL queries. -J Thanks, Svilen Spasov --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 for term 'c++'
Hello all,
Is there any way I can search for a term such as 'c++'
using a fulltext search index?
Here is the query that should return rows but it does
not. I suspect that the trailing ++ are treated as
wildcards and are not fulltext indexed by mysql. It
seems like mysql interprets the search string to be
just a single character ('c') and ignores the query
term since my mysql server is configured to ignore
searches for keywords shorter than 3 characters.
select f1 from t1 where match(f1) against('c++' in
boolean mode);
Any help would be greatly appreciated
James
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search for term 'c++'
I think the '+' will not get into the index in the first place. So, there's
probably no way to get a search to use the index. However if you want a
consistant query format for your search you could probably get the correct
result by wrapping the search term in double quotes:
select f1 from t1 where match(f1) against ('c++' in boolean mode);
HTH,
James
At 12:30 pm -0700 10/5/06, klute wrote:
Is there any way I can search for a term such as 'c++'
using a fulltext search index?
select f1 from t1 where match(f1) against('c++' in
boolean mode);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best practice: FULLTEXT search InnoDB transactions replication
Hi, We have moved from Mysql4 to MySQL5 and are currently planning our new database schema. In this new approach we would like to move to InnoDB's storage engine for transaction support and still want to use MySQL's FULLTEXT search capabillities. And to make things easy we also want to replicate all our data to a second database. Now I have two different possible approaches: 1. All tables are of type InnoDB, except one table which is of type MyIsam = the FULLTEXT searchable table. This searchable table would have a column with searchable text and a few meta data columns to identify the originating table, column and row. I could use the triggers to index the desired columns on Inserts, updates and deletes and insert the indexed data into the MyIsam search-table. Replication would be straigtforward 1-to-1 replication in this aproach. 2. Still all tables would be of type InnoDB, but instead of creating a single searchable MyIsam table I could also alter the storage engine type for the searchable tables on de replication slave to MyIsam and delegate all searches to the slave. Which even may improve performance, because the master wont be doing full text searches anymore. Replication would be a bit more tricky because of having the InnoDB tables in the master and their corresponding MyIsam tables in the slave. I'm wondering which, if any, of the above aproaches is advisable or if there are other aproaches which are even better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
On Sun, 18 Dec 2005, Octavian Rasnita wrote: Hi, Please tell me how can I configure MySQL 5 in order to be able to search (using fulltext indexes) for combined words like s-au. This is a single word and not 2 words but I think MySQL thinks that there are 2 words, one of them having a single character, and the second 2 chars, so it is not found because I have configured MySQL to index only the words that have at least 3 chars. I don't think it would be a good idea to configure it to index one-char words, so I hope there is another method. Thank you. Teddy Hi Teddy, you may try the following hack to make '-' a normal text character (these code fragments are from mysql-4.0.25 but it may work with 5.0.x as well): Change the source in myisam/ftdefs.h #define true_word_char(X) (isalnum(X) || (X)=='_') to #define true_word_char(X) (isalnum(X) || (X)=='_' || (X)=='-') and substitute another char for '-' in myisam/ft_static.c: const char *ft_boolean_syntax=+ -()~*:\\|; which could become something like const char *ft_boolean_syntax=+ =()~*:\\|; Then recompile and try your luck. You have to rebuild your indexes. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext search
Hi, Please tell me how can I configure MySQL 5 in order to be able to search (using fulltext indexes) for combined words like s-au. This is a single word and not 2 words but I think MySQL thinks that there are 2 words, one of them having a single character, and the second 2 chars, so it is not found because I have configured MySQL to index only the words that have at least 3 chars. I don't think it would be a good idea to configure it to index one-char words, so I hope there is another method. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
AFAIK you are right - MySQL treats a hypen as a word-break. And, AFAIK you
cannot modify that behaviour.
The only possibility, I think, would be to modify the source and compile your
own MySQL. :-(
However if you do a full-text search using IN BOOLEAN MODE, then you can put
quotes around hypenated words.
... MATCH (col_name) AGAINST ('s-au' IN BOOLEAN MODE) ...
HTH,
James Harvard
At 5:26 pm +0200 18/12/05, Octavian Rasnita wrote:
Please tell me how can I configure MySQL 5 in order to be able to search
(using fulltext indexes) for combined words like s-au.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
AFAIK you are right - MySQL treats a hypen as a word-break. And, AFAIK you
cannot modify that behaviour.
The only possibility, I think, would be to modify the source and compile
your own MySQL. :-(
However if you do a full-text search using IN BOOLEAN MODE, then you can
put quotes around hypenated words.
... MATCH (col_name) AGAINST ('s-au' IN BOOLEAN MODE) ...
HTH,
James Harvard
I have tried that, but it doesn't found anything. I think this is because
MySQL doesn't put the words s and au in the fulltext index at all, so it
is not able to find s-au.
Isn't possible to set somewhere which are the word chars?
I am not sure how other special chars (which are real chars) like s, t, â,
a, î, S, T, Â, Î, A are viewd by MySQL fulltext index... as word chars, or
as word break chars.
If I could add some of these chars, maybe I could also add the -
character.
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with fulltext search and left join
Hi,
I keep getting errors on this query and I'm not sure
why. I'm using mysql version 4.0.22.
Any ideas?
SELECT pn_coupons_store.store_name,
pn_coupons_store.store_name_short,
pn_coupons_coupons.store_id,
pn_coupons_coupons.coupon_id,
pn_coupons_dealtype.dealtype_name,
pn_coupons_coupons.coupon_desc,
pn_coupons_coupons.coupon_link,
pn_coupons_coupons.use_preurl,
pn_coupons_coupons.coupon_tracking,
pn_coupons_coupons.coupon_banner,
pn_coupons_coupons.coupon_drm,
pn_coupons_coupons.coupon_code,
pn_coupons_coupons.coupon_legal_text,
pn_coupons_coupons.coupon_submitter,
pn_coupons_coupons.coupon_added,
pn_coupons_coupons.coupon_start,
pn_coupons_coupons.coupon_exp,
pn_coupons_coupons.coupon_hits,
pn_coupons_network.network_id, MATCH (coupon_desc)
AGAINST ('electronics' IN BOOLEAN MODE) AS score FROM
pn_coupons_coupons WHERE MATCH (store_name) AGAINST
('electronics' IN BOOLEAN MODE), LEFT JOIN
pn_coupons_store ON (pn_coupons_store.store_id =
pn_coupons_coupons.store_id) LEFT JOIN
pn_coupons_dealtype ON
(pn_coupons_dealtype.dealtype_id =
pn_coupons_coupons.dealtype_id) LEFT JOIN
pn_coupons_network ON (pn_coupons_network.network_id =
pn_coupons_store.store_network_id) ORDER BY score DESC
Thanks,
Grant
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with fulltext search and left join
For me, it looks as if you confused the order of join and where. It should be:
select ... from ... join ... where ... order by ...
Stefan
Am Sunday 23 October 2005 13:21 schrieb Grant Giddens:
Hi,
I keep getting errors on this query and I'm not sure
why. I'm using mysql version 4.0.22.
Any ideas?
SELECT pn_coupons_store.store_name,
pn_coupons_store.store_name_short,
pn_coupons_coupons.store_id,
pn_coupons_coupons.coupon_id,
pn_coupons_dealtype.dealtype_name,
pn_coupons_coupons.coupon_desc,
pn_coupons_coupons.coupon_link,
pn_coupons_coupons.use_preurl,
pn_coupons_coupons.coupon_tracking,
pn_coupons_coupons.coupon_banner,
pn_coupons_coupons.coupon_drm,
pn_coupons_coupons.coupon_code,
pn_coupons_coupons.coupon_legal_text,
pn_coupons_coupons.coupon_submitter,
pn_coupons_coupons.coupon_added,
pn_coupons_coupons.coupon_start,
pn_coupons_coupons.coupon_exp,
pn_coupons_coupons.coupon_hits,
pn_coupons_network.network_id, MATCH (coupon_desc)
AGAINST ('electronics' IN BOOLEAN MODE) AS score FROM
pn_coupons_coupons WHERE MATCH (store_name) AGAINST
('electronics' IN BOOLEAN MODE), LEFT JOIN
pn_coupons_store ON (pn_coupons_store.store_id =
pn_coupons_coupons.store_id) LEFT JOIN
pn_coupons_dealtype ON
(pn_coupons_dealtype.dealtype_id =
pn_coupons_coupons.dealtype_id) LEFT JOIN
pn_coupons_network ON (pn_coupons_network.network_id =
pn_coupons_store.store_network_id) ORDER BY score DESC
Thanks,
Grant
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
--
Stefan Kuhn M. A.
Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de)
Zülpicher Str. 47, 50674 Cologne
Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786
My public PGP key is available at http://pgp.mit.edu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext search on words inside words
Hi there, I am wondering if it is possible to find words inside words with the help of fulltext search. For example: Search for: Antenne Schould also find Stabantenne Is this possible? Google does that, so somehow there should be a way. Another thing is, how do I exclude popular words like and for and similar from the search? Is there a MySQL setting for this. Like words_to_exclude = Thanx, Merlin Oh.. I am running MySQL 4.0.14 on Suse Linux 9. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search on words inside words
Merlin wrote: I am wondering if it is possible to find words inside words with the help of fulltext search. Is this possible? Google does that, so somehow there should be a way. Somehow I don't think that Google runs on a single MySQL database. Full text indexes in MySQL mean that words (MySQL's definition of a word that is) are indexed, not parts of words. Google's purpose is to provide a searchable index, so they have built their own data structures for these features. Another thing is, how do I exclude popular words like and for and similar from the search? Is there a MySQL setting for this. Like words_to_exclude = Fine tuning full-text search can be found at: http://dev.mysql.com/doc/mysql/en/fulltext-fine-tuning.html The default stop words are at: http://dev.mysql.com/doc/mysql/en/fulltext-stopwords.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT search result requirement
I am looking into using the FULLTEXT search features for our FAQ system. Problem is the 50% limitation. We aren't going to have thousands of questions or articles, so the odds of most of the questions/articles matching is high and a desireable effect for us. Is there a away to disable this requirment? Or should I be looking in a totally different direction for something of this scale? Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search result requirement
2005/8/11, Eric Jensen [EMAIL PROTECTED]: I am looking into using the FULLTEXT search features for our FAQ system. Problem is the 50% limitation. We aren't going to have thousands of questions or articles, so the odds of most of the questions/articles matching is high and a desireable effect for us. Is there a away to disable this requirment? Yes: use IN BOOLEAN MODE for your fulltext searches. See the MySQL manual for further information. Jan Pieter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem in Fulltext Search
Hi,
We have a table containing more than 15 million rows of data, can anybody
please help in this problem of fulltext search described below.
The following query is giving a good result in terms of query time.
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
when I want to search for more that one word or phrase like food, locomotive,
water tank then the query is not working up to the desired level. The following
query is taking not less than 90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
I have also tried with another version of the same previous query which is also
not giving a desired performance. The following query is taking not less than
90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food locomotive water tank' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
In these cases, I think, I am not getting the advantage of full text search,
because still it is taking a lot of time to execute the query. Can I optimise
the database to few step forward such that the query time can be reduced for
multiple words of phrases, please let us know can the query be optimized to
provide a better performance?
Regards,
Suryya
Re: Problem in Fulltext Search
Suryya Ghosh [EMAIL PROTECTED] wrote on 08/05/2005 09:50:27
AM:
Hi,
We have a table containing more than 15 million rows of data, can
anybody please help in this problem of fulltext search described below.
The following query is giving a good result in terms of query time.
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
when I want to search for more that one word or phrase like food,
locomotive, water tank then the query is not working up to the
desired level. The following query is taking not less than 90 to 100
seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
I have also tried with another version of the same previous query
which is also not giving a desired performance. The following query
is taking not less than 90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food locomotive water tank' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
In these cases, I think, I am not getting the advantage of full text
search, because still it is taking a lot of time to execute the
query. Can I optimise the database to few step forward such that the
query time can be reduced for multiple words of phrases, please let
us know can the query be optimized to provide a better performance?
Regards,
Suryya
One technique to speed up OR condition searches, tested again earlier this
week with reported success, is to split your OR-ed conditions into
UNION-ed queries like this:
(select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
UNION
(select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
UNION
(select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
ORDER BY field2 DESC LIMIT 500;
This will not provide any kind of relevancy like your second test query
would but it will give you the top 500 records in descending field2 order
that contain any of the search words in field1. Since each part of the
UNION is a simple search, the FT index lookup will be faster so if you can
running just one query in about .5 seconds, the whole thing should execute
in about 3 seconds (to allow extra time for merging, deduplication, and
re-ordering of the final assembled sets of rows)
If you would like to favor records that have more search word hits
(relevancy) then you will need a different query, more like your second
test query.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Problem in Fulltext Search
Hello.
The common solution for similar issues which appeared on the
list not recently was modifying the query to use UNIONs. For
example:
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
can be changed to:
(SELECT field1, field2
FROM tblMerge
WHERE
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
UNION
(SELECT field1, field2
FROM tblMerge
WHERE
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
ORDER BY field2 DESC LIMIT 500
Check if the query with UNION faster than with OR.
Suryya Ghosh [EMAIL PROTECTED] wrote:
Hi,
We have a table containing more than 15 million rows of data, can anybody
please help in
this problem of fulltext search described below.
The following query is giving a good result in terms of query time.
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
when I want to search for more that one word or phrase like food, locomotive,
water tank
then the query is not working up to the desired level. The following query is
taking not
less than 90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
I have also tried with another version of the same previous query which is
also not giving
a desired performance. The following query is taking not less than 90 to 100
seconds that
is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food locomotive water tank' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
In these cases, I think, I am not getting the advantage of full text search,
because still
it is taking a lot of time to execute the query. Can I optimise the database
to few step
forward such that the query time can be reduced for multiple words of phrases,
please let
us know can the query be optimized to provide a better performance?
Regards,
Suryya
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with fulltext search
I'm trying to use the following query :: SELECT movie_id, title, genre, description, TRIM( TRAILING ',' FROM actors ) , director, disclaimer, rating, year, ( MATCH ( title, description ) AGAINST ( 'another' ) ) AS score FROM movie_details WHERE ( MATCH ( title, description ) AGAINST ( 'another' ) ) AND service = 'tmn' AND last_run now( ) ORDER BY score DESC , title ASC LIMIT 200 The 'movie_details' table has 2 entries with the word 'another' in the title that have a last_run date greater than the current date, but the query is not returning any results. I modified the query to :: SELECT movie_id, title, genre, description, TRIM( TRAILING ',' FROM actors ) , director, disclaimer, rating, year, ( MATCH ( title, description ) AGAINST ( 'another' ) ) AS score FROM movie_details WHERE ( title LIKE '%another%' OR description LIKE '%another%' ) AND service = 'tmn' AND last_run now( ) ORDER BY score DESC , title ASC LIMIT 200 With this statement I'm getting the desired results, but it's obviously not efficient and takes approximately 35x longer to run than the first query. Any thoughts as to why the first query isn't returning the proper results? Thanks for the help in advance ... :: dawn :: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with fulltext search
another is a stop word. The default list of stopwords is in myisam/ft_static.c in a source distribution. See the manual for more http://dev.mysql.com/doc/mysql/en/fulltext-fine-tuning.html. Michael Dawn O'Brien wrote: I'm trying to use the following query :: SELECT movie_id, title, genre, description, TRIM( TRAILING ',' FROM actors ) , director, disclaimer, rating, year, ( MATCH ( title, description ) AGAINST ( 'another' ) ) AS score FROM movie_details WHERE ( MATCH ( title, description ) AGAINST ( 'another' ) ) AND service = 'tmn' AND last_run now( ) ORDER BY score DESC , title ASC LIMIT 200 The 'movie_details' table has 2 entries with the word 'another' in the title that have a last_run date greater than the current date, but the query is not returning any results. I modified the query to :: SELECT movie_id, title, genre, description, TRIM( TRAILING ',' FROM actors ) , director, disclaimer, rating, year, ( MATCH ( title, description ) AGAINST ( 'another' ) ) AS score FROM movie_details WHERE ( title LIKE '%another%' OR description LIKE '%another%' ) AND service = 'tmn' AND last_run now( ) ORDER BY score DESC , title ASC LIMIT 200 With this statement I'm getting the desired results, but it's obviously not efficient and takes approximately 35x longer to run than the first query. Any thoughts as to why the first query isn't returning the proper results? Thanks for the help in advance ... :: dawn :: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
accent, quote and fulltext search
Hello,
It seems it is possible to get ride of accent problems in fulltext
search (but I don't know how to). I am using 4.1.8a version of mysql and
I am not enable to find any accentuated word from its non-accent version
(says 'siecle' for 'siècle'). For example :
SELECT * FROM table WHERE MATCH (myfields) AGAINST ('siecle')
returns nothing but
SELECT * FROM table WHERE MATCH (myfields) AGAINST ('siècle')
finds many rows ...
I have got sames problems with single quote words : I look for
influence which can be found in l'influence. The only way to find
l'influence is searching for it exact syntax. Also a search with
'influence' doesn't return l'influence occurences. It seems there is a
way to get ride of ' character (quote from
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html) :
MySQL uses a very simple parser to split text into words. A
``word'' is any sequence of true word characters (letters, digits, and
underscores), optionally separated by no more than one sequential `''
character.
It says optionnally ... does it mean it is an option ? (so what is
this option?) or does it only mean the parser is enable to parse
wasn't as a single word ?
Thanks !!
Regards,
BDM.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FullText search question
Given a search string of 'NASD' my default Fulltext search doesn't find it. Wondered why? Is there a fix? Thanks for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search question
Given a search string of 'NASD' my default Fulltext search doesn't find it. Wondered why? Is there a fix? Thanks for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext search question
[snip] Given a search string of 'NASD' my default Fulltext search doesn't find it. Wondered why? [/snip] Quote from http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html: MySQL uses a very simple parser to split text into words. A word is any sequence of true word characters (letters, digits, and underscores), optionally separated by no more than one sequential `'` character. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search string less than 4 characters
I want to know on Solaris how I could lower the the minimum fulltext search string from 4 to 3. Right now using the FullText search any string less than 4 chars is ignored. I'm sure there's a link explaining how. Maybe UNIX help in general on his would be good as well. Thanks, Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search string less than 4 characters
Lee, establish the fulltext minimum word length system variable as follows... [mysqld] ft_min_word_len=3 reference: http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html cheers, --bemansell Brian E. Mansell MySQL Professional On Thu, 06 Jan 2005 20:59:23 -0500, leegold [EMAIL PROTECTED] wrote: I want to know on Solaris how I could lower the the minimum fulltext search string from 4 to 3. Right now using the FullText search any string less than 4 chars is ignored. I'm sure there's a link explaining how. Maybe UNIX help in general on his would be good as well. Thanks, Lee G. -- 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: Optimize query and/or db structure, FullText search + sort by other fields
Hi All,
The temporary table wasn't a better idea,
the problem is that if the search returns 1500+
rows ( on a 20 000 recs table), the population of
temporary table is still slow making no significant difference.
Well, I'm in a strange situation as for the moment.
I've created helper tables, eg to perform searches ordered by end_time,
I've created a separate table:
DROP TABLE IF EXISTS end_time;
CREATE TABLE end_time (
lot_idint unsigned not null,
category_id int unsigned not null,
end_time int unsigned not null,
title char(50) not null,
description text not null,
INDEXend_time (end_time),
FULLTEXT ttitle (title),
FULLTEXT ttitle_description (title, description)
) TYPE = MyISAM COMMENT = Search helper;
And filled each with sample 20 000 records (instead of 50 000),
but this seems to be enough to estimate the speed.
It have helped much, the search times dropped twice, but 5-10
seconds were still too much.
Then, I've set key_cache_block_size to 2048, and run
`repair table end_time` (hmm now I understand, that
key_cache_block_size should influence the key indexes on the disk,
so it looks like repair haven't been needed).
Miracle ! Without joining with `category_map` and `lot`,
all the searches dropped to 0.01-0.02 seconds (WOW!), and with INNER JOINing
like
SELECT end_time.lot_id, end_time.description
FROM end_time
INNER JOIN category_map
ON category_map.child_id = end_time.category_id AND
category_map.parent_id = 10
INNER JOIN lot
ON lot.id = end_time.lot_id
WHERE
MATCH(end_time.title, end_time.description) AGAINST ('+keyword' IN
BOOLEAN MODE)
ORDER BY
end_time.end_time;
the searches don't cross the line of 0.50, most of them are 0.05-0.06
seconds.
It is mysql-4.1.6-gamma.
The exact steps to reproduce, can someone check that out ?
1. fill in the data with default config.
2. put key_cache_block_size = 2048 in my.cnf
3. restart server
4. check key_cache_block_size via SHOW variables LIKE '%block%'; For me
there is 1536 (?!)
5. SET GLOBAL key_cache_block_size = 2048;
6. go REPAIR TABLE end_time (the table with fulltext search you are
querying)
7. here we are, all the MATCH() with ORDER BY queries take 0.01, only
some of them are 0.02 secs,
not more
Sincerely,
Aleksandr Guidrevitch
[EMAIL PROTECTED] wrote:
I found your query hard to understand, however it seems the optimizer
could read it just fine. I VERY MUCH dislike using the comma-separated
list of table names to declare INNER JOINS. I think it allows me too
much opportunity to accidentally create a Cartesian product by
accidentally forgetting a term in my WHERE clause. I firmly believe
that a missing ON clause is much easier to spot.
SELECT lot.id, search.title
FROM search
LEFT JOIN lot
ON lot.id = search.lot_id
INNER JOIN category_map
ON category_map.child_id = lot.category_id
AND category_map.parent_id = 10
INNER JOIN exchange_rate
ON exchange_rate.currency_id = lot.currency_id
WHERE MATCH(search.title, search.description)
AGAINST ('some query' IN BOOLEAN MODE)
ORDER BY lot.current_price * exchange_rate.exchange_rate
LIMIT 0, 50
If I just describe how each table contributes to each query I see that
you are going to scan every row of the search table and optionally
match those to records in the lot table but only if those lot
records also match up with both a category_map record and an
exchange_rate record. Are you sure those are the records you want to
query?
What I think you should do is run the full-text portion of the search
first. Then join those results to lot, category_map, and
exchange_rate. If you have the same number of records in search as
you have in lot (I think you said you are testing with 5) then
you reduce the JOIN overhead for your query by quite a bit. If you
have 5 search records and your FT search only returns 200 you just
eliminated 49800 records from an additional table join.
CREATE TEMPORARY TABLE tmpLots(key(lotID))
SELECT DISTINCT lotID, title
FROM search
WHERE MATCH(search.title, search.description)
AGAINST ('some query' IN BOOLEAN MODE)
SELECT l.ID, tl.title
FROM lot l
INNER JOIN tmpLots tl
on tl.lotId = l.id
INNER JOIN category_map
ON category_map.child_id = l.category_id
AND category_map.parent_id = 10
INNER JOIN exchange_rate
ON exchange_rate.currency_id = l.currency_id
Order by l.current_price * exchange_rate.exchange_rate
DROP TEMPORARY TABLE tmpLots
Now, to address the speed of your ORDER BY. This is a quote from
http://dev.mysql.com/doc/mysql/en/ORDER_BY_optimization.html
If you want to increase ORDER BY speed, first see whether you can get
MySQL to use indexes rather than an extra sorting phase. If this is
not possible, you can try the following strategies:
* Increase the size of the sort_buffer_size
Optimize query and/or db structure, FullText search + sort by other fields
Hi All, this was already posted on mysql forum preformance,
but forums are really slow, so sorry for the crosspost :)
My objective is to implement quick, really quick complex fulltext search
with 'order by' ( 2 seconds).
The actual table I'd like to search is `lot`. I've created 2 helper
tables : 1. `search`, which contains normalized (or stemmed) title and
description with fulltext search index 2. `category_map` which covers
all relations of categories, and is especially useful when I need to
perform search on all children categories of current category.
The total count of records in lot, and correspondingly in `search`,
tables is expected to be 50 000, each around 2048 (stemmed description)
+ 50 (stemmed title) bytes.
I need 1) fulltextsearch within all records, 2) category listing, 3)
fulltextsearch within a category.
Also, the result set should be *always* ordered either by lot.end_time,
lot.current_price * exchange_rate, lot.title or lot.bid_count. The
fulltextsearch relevance is not important, I'm ready to ignore it in
favor of performance.
Some time ago I've tried to put fulltext right inside the `lot` table,
but it seemed to produce much longer lasting queries than the current,
in plus in this case i was unable to LOAD INDEX INTO CACHE because of
different key length of FULLTEXT and other table's indexes. That's why
I've separated fields which I'd like to index fulltext into `search`
table. But now I'm stuck with performance degradation of ORDER BY. I've
tried to play with the related sort_buffer_size, myisam_sort_buffer_size
and other params described in the docs related ORDER BY and filesort,
but with no significant performance influence. I do not mind to create
as many helper tables as needed, but I cant figure out what else can I
improve. Another disappointing problem is that it is rather slow to
generate 50 000 records for different table structures (around 2 hours)
just to play with them for 10-20 mins.
Here is what I came up with at the moment:
SELECT lot.id, search.title FROM search LEFT JOIN lot ON lot.id =
search.lot_id, category_map, exchange_rate WHERE MATCH(search.title,
search.description) AGAINST ('some query' IN BOOLEAN MODE) AND
category_map.child_id = lot.category_id AND category_map.parent_id = 10
AND exchange_rate.currency_id = lot.currency_id ORDER BY
lot.current_price * exchange_rate.exchange_rate LIMIT 0, 50
Which produces almost acceptable results (which I'd like to improve
further) if I omit ORDER BY statement. I do understand that the slowdown
occurs mostly during filesort, and partially due to temporary table.
The question is
1) Which is a desirable tables structure which will allow to perform
fulltextsearch + ordering
2) Which is preferable LEFT JOIN .. ON, or WHERE ? I ahaven't noticed
any significant difference trying both of them
EXPLAIN:
++-+---+--+-+---+-+--+--+--+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+--+-+---+-+--+--+--+
| 1 | SIMPLE | search | fulltext | title_description | title_description
| 0 | | 1 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | lot | eq_ref | PRIMARY,category_id | PRIMARY | 4 |
tauction.search.lot_id | 1 | Using where |
| 1 | SIMPLE | exchange_rate | ref | currency_id | currency_id | 4 |
tauction.lot.currency_id | 2 | Using index |
| 1 | SIMPLE | category_map | ref | parent_id,child_id | child_id | 4 |
tauction.lot.category_id | 12 | Using where |
++-+---+--+-+---+-+--+--+--+
4 rows in set (0.00 sec)
Table structure as follows.
DROP TABLE IF EXISTS lot;
CREATE TABLE lot (
id int unsigned not null primary key auto_increment,
owner_id int unsigned not null default 0,
title char(50) not null,
description text not null,
current_price decimal(16,2) unsigned not null,
quantity int unsigned not null,
bid_count int unsigned not null,
start_time int unsigned not null,
end_time int unsigned not null,
city char(35) not null,
category_id int unsigned not null,
currency_id int unsigned not null,
country_id int unsigned not null,
state_id int unsigned not null,
delivery_id int unsigned not null,
enabled bool default 1 not null,
INDEX title (title),
INDEX current_price (current_price),
INDEX bid_count (bid_count),
INDEX end_time (end_time),
INDEX category_id (category_id)
) TYPE = MyISAM COMMENT = Lots;
DROP TABLE IF EXISTS search;
CREATE TABLE search (
lot_id int unsigned not null,
title char(50) not null,
description text not null,
FULLTEXT title (title),
FULLTEXT title_description
Re: Optimize query and/or db structure, FullText search + sort by other fields
I found your query hard to understand, however it seems the optimizer
could read it just fine. I VERY MUCH dislike using the comma-separated
list of table names to declare INNER JOINS. I think it allows me too much
opportunity to accidentally create a Cartesian product by accidentally
forgetting a term in my WHERE clause. I firmly believe that a missing ON
clause is much easier to spot.
SELECT lot.id, search.title
FROM search
LEFT JOIN lot
ON lot.id = search.lot_id
INNER JOIN category_map
ON category_map.child_id = lot.category_id
AND category_map.parent_id = 10
INNER JOIN exchange_rate
ON exchange_rate.currency_id = lot.currency_id
WHERE MATCH(search.title, search.description)
AGAINST ('some query' IN BOOLEAN MODE)
ORDER BY lot.current_price * exchange_rate.exchange_rate
LIMIT 0, 50
If I just describe how each table contributes to each query I see that you
are going to scan every row of the search table and optionally match
those to records in the lot table but only if those lot records also
match up with both a category_map record and an exchange_rate record.
Are you sure those are the records you want to query?
What I think you should do is run the full-text portion of the search
first. Then join those results to lot, category_map, and
exchange_rate. If you have the same number of records in search as you
have in lot (I think you said you are testing with 5) then you
reduce the JOIN overhead for your query by quite a bit. If you have 5
search records and your FT search only returns 200 you just eliminated
49800 records from an additional table join.
CREATE TEMPORARY TABLE tmpLots(key(lotID))
SELECT DISTINCT lotID, title
FROM search
WHERE MATCH(search.title, search.description)
AGAINST ('some query' IN BOOLEAN MODE)
SELECT l.ID, tl.title
FROM lot l
INNER JOIN tmpLots tl
on tl.lotId = l.id
INNER JOIN category_map
ON category_map.child_id = l.category_id
AND category_map.parent_id = 10
INNER JOIN exchange_rate
ON exchange_rate.currency_id = l.currency_id
Order by l.current_price * exchange_rate.exchange_rate
DROP TEMPORARY TABLE tmpLots
Now, to address the speed of your ORDER BY. This is a quote from
http://dev.mysql.com/doc/mysql/en/ORDER_BY_optimization.html
If you want to increase ORDER BY speed, first see whether you can get
MySQL to use indexes rather than an extra sorting phase. If this is not
possible, you can try the following strategies:
* Increase the size of the sort_buffer_size variable.
* Increase the size of the read_rnd_buffer_size variable.
* Change tmpdir to point to a dedicated filesystem with lots of empty
space. If you use MySQL 4.1 or later, this option accepts several paths
that are used in round-robin fashion. Paths should be separated by colon
characters (`:') on Unix and semicolon characters (`;') on Windows,
NetWare, and OS/2. You can use this feature to spread the load across
several directories. Note: The paths should be for directories in
filesystems that are located on different physical disks, not different
partitions of the same disk.
Since you are computing the value you are ordering by, you cannot possibly
use an index so I guess that leaves you with just the other three
suggestions. You could possibly try creating another temp table to sort
your calculated values and apply a BTREE index to that value (HASH indexes
are not useful in ORDER BY optimization) so that your (top 50 ) query will
return faster. But you have to compare the difference in performance
between the current query and the process of creating the new temporary
table, populating it, and querying against it. I am not sure the extra
overhead involved in creating another indexed table will help (as the
number of records involved are much fewer), but it could.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Aleksandr Guidrevitch [EMAIL PROTECTED] wrote on 11/05/2004 08:25:00 AM:
Hi All, this was already posted on mysql forum preformance,
but forums are really slow, so sorry for the crosspost :)
My objective is to implement quick, really quick complex fulltext search
with 'order by' ( 2 seconds).
The actual table I'd like to search is `lot`. I've created 2 helper
tables : 1. `search`, which contains normalized (or stemmed) title and
description with fulltext search index 2. `category_map` which covers
all relations of categories, and is especially useful when I need to
perform search on all children categories of current category.
The total count of records in lot, and correspondingly in `search`,
tables is expected to be 50 000, each around 2048 (stemmed description)
+ 50 (stemmed title) bytes.
I need 1) fulltextsearch within all records, 2) category listing, 3)
fulltextsearch within a category.
Also, the result set
Re: Optimize query and/or db structure, FullText search + sort by other fields
Hi,
Try to add an index on join fields ( search.lot_id,
exchange_rate.currency_id,lot.currency_id)
Your query can not use an index to sort because MySql can use only 1
index to search and sort and your sort is a function so it scans
result rows and then it sorts the working table.
Santino
At 15:25 +0200 5-11-2004, Aleksandr Guidrevitch wrote:
Hi All, this was already posted on mysql forum preformance,
but forums are really slow, so sorry for the crosspost :)
My objective is to implement quick, really quick complex fulltext
search with 'order by' ( 2 seconds).
The actual table I'd like to search is `lot`. I've created 2 helper
tables : 1. `search`, which contains normalized (or stemmed) title
and description with fulltext search index 2. `category_map` which
covers all relations of categories, and is especially useful when I
need to perform search on all children categories of current
category.
The total count of records in lot, and correspondingly in `search`,
tables is expected to be 50 000, each around 2048 (stemmed
description) + 50 (stemmed title) bytes.
I need 1) fulltextsearch within all records, 2) category listing, 3)
fulltextsearch within a category.
Also, the result set should be *always* ordered either by
lot.end_time, lot.current_price * exchange_rate, lot.title or
lot.bid_count. The fulltextsearch relevance is not important, I'm
ready to ignore it in favor of performance.
Some time ago I've tried to put fulltext right inside the `lot`
table, but it seemed to produce much longer lasting queries than the
current, in plus in this case i was unable to LOAD INDEX INTO CACHE
because of different key length of FULLTEXT and other table's
indexes. That's why I've separated fields which I'd like to index
fulltext into `search` table. But now I'm stuck with performance
degradation of ORDER BY. I've tried to play with the related
sort_buffer_size, myisam_sort_buffer_size and other params described
in the docs related ORDER BY and filesort, but with no significant
performance influence. I do not mind to create as many helper tables
as needed, but I cant figure out what else can I improve. Another
disappointing problem is that it is rather slow to generate 50 000
records for different table structures (around 2 hours) just to play
with them for 10-20 mins.
Here is what I came up with at the moment:
SELECT lot.id, search.title FROM search LEFT JOIN lot ON lot.id =
search.lot_id, category_map, exchange_rate WHERE MATCH(search.title,
search.description) AGAINST ('some query' IN BOOLEAN MODE) AND
category_map.child_id = lot.category_id AND category_map.parent_id =
10 AND exchange_rate.currency_id = lot.currency_id ORDER BY
lot.current_price * exchange_rate.exchange_rate LIMIT 0, 50
Which produces almost acceptable results (which I'd like to improve
further) if I omit ORDER BY statement. I do understand that the
slowdown occurs mostly during filesort, and partially due to
temporary table.
The question is
1) Which is a desirable tables structure which will allow to perform
fulltextsearch + ordering
2) Which is preferable LEFT JOIN .. ON, or WHERE ? I ahaven't
noticed any significant difference trying both of them
EXPLAIN:
++-+---+--+-+---+-+--+--+--+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
++-+---+--+-+---+-+--+--+--+
| 1 | SIMPLE | search | fulltext | title_description |
title_description | 0 | | 1 | Using where; Using temporary; Using
filesort |
| 1 | SIMPLE | lot | eq_ref | PRIMARY,category_id | PRIMARY | 4 |
tauction.search.lot_id | 1 | Using where |
| 1 | SIMPLE | exchange_rate | ref | currency_id | currency_id | 4 |
tauction.lot.currency_id | 2 | Using index |
| 1 | SIMPLE | category_map | ref | parent_id,child_id | child_id |
4 | tauction.lot.category_id | 12 | Using where |
++-+---+--+-+---+-+--+--+--+
4 rows in set (0.00 sec)
Table structure as follows.
DROP TABLE IF EXISTS lot;
CREATE TABLE lot (
id int unsigned not null primary key auto_increment,
owner_id int unsigned not null default 0,
title char(50) not null,
description text not null,
current_price decimal(16,2) unsigned not null,
quantity int unsigned not null,
bid_count int unsigned not null,
start_time int unsigned not null,
end_time int unsigned not null,
city char(35) not null,
category_id int unsigned not null,
currency_id int unsigned not null,
country_id int unsigned not null,
state_id int unsigned not null,
delivery_id int unsigned not null,
enabled bool default 1 not null,
INDEX title
Fulltext search Strategy - Need Help
Hello, I'm doing fairly straight forward fulltext searches, but I want to nest them - basically do a keyword search on 'phrase 1' and then search the results this returns for 'phrase 2', for example if phrase 1 is 'ford' and phrase 2 is 'focus' - I search once for 'ford' and then go through the record-set this returns for 'focus'. My first thought is use a temporary table - but is there a way of doing this with one query? Cheers, lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search Strategy - Need Help
Search '+ford +focus' [in boolean mode] Santino At 13:22 + 3-11-2004, Lee Denny wrote: Hello, I'm doing fairly straight forward fulltext searches, but I want to nest them - basically do a keyword search on 'phrase 1' and then search the results this returns for 'phrase 2', for example if phrase 1 is 'ford' and phrase 2 is 'focus' - I search once for 'ford' and then go through the record-set this returns for 'focus'. My first thought is use a temporary table - but is there a way of doing this with one query? Cheers, lee -- 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: match a fulltext search with a - dash, can't match if - exist
Louie,
Mysql treats the dash as a word separator. There's no way to change
that unless you change the source code and recompile Mysql.
Also, the default min word length is 4. So not only is E not
matched; even 018 will not be matched. The min word length is easily
configurable, though. Put a .cnf file in the conf dir of your
installation, and have the line:
ft_min_word_len=3
and restart your mysql server. If 018 is not a common word in your
index, then it will show up as a match.
-linda
On Wed, 20 Oct 2004 08:17:06 +0100, Chris Elsworth [EMAIL PROTECTED] wrote:
On Mon, Oct 18, 2004 at 11:19:55AM +0800, Louie Miranda wrote:
mysql select * from fullsearch where match (title,body) against ('018-E');
Empty set (0.00 sec)
it returns an empty set, is it possible to also search with - dash? chars?
If I remember correctly, you need to pass the string as a phrase to fulltext:
select * from fullsearch where match (title,body) against ('018-E');
I'd prefer being able to escape the - with \, since using a phrase has
other disadvantages (like partial word matching goes out the window),
but you can't.
--
Chris
--
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: match a fulltext search with a - dash, can't match if - exist
On Mon, Oct 18, 2004 at 11:19:55AM +0800, Louie Miranda wrote:
mysql select * from fullsearch where match (title,body) against ('018-E');
Empty set (0.00 sec)
it returns an empty set, is it possible to also search with - dash? chars?
If I remember correctly, you need to pass the string as a phrase to fulltext:
select * from fullsearch where match (title,body) against ('018-E');
I'd prefer being able to escape the - with \, since using a phrase has
other disadvantages (like partial word matching goes out the window),
but you can't.
--
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: match a fulltext search with a - dash, can't match if - exist
mysql select * from fullsearch where match (title,body) against
('018-E');
Empty set (0.00 sec)
it returns an empty set, is it possible to also search with - dash?
chars?
I'm not an expert but others will correct me :
In a fulltext search, the search string must be at least 4 characters ?
Otherwise, Mysql ignore it ?
Bertrand.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
match a fulltext search with a - dash, can't match if - exist
this is a working example i found on mysql.com
this is my example of fullsearch
mysql desc fullsearch;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| id| int(10) unsigned | | PRI | NULL| auto_increment |
| title | varchar(200) | YES | MUL | NULL||
| body | text | YES | | NULL||
+---+--+--+-+-++
3 rows in set (0.00 sec)
my data:
mysql select * from fullsearch;
++---+---+
| id | title | body |
++---+---+
| 1 | MySQL Tutorial| DBMS stands for DataBase ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 6 | 018-E | Test for Title Item COde search language, etc |
| 7 | MySQL Security| When configured properly, MySQL ... |
++---+---+
my search the title with a - code
mysql select * from fullsearch where match (title,body) against ('018-E');
Empty set (0.00 sec)
it returns an empty set, is it possible to also search with - dash? chars?
--
Louie Miranda
http://www.axishift.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search help
Try to search in keyword table:
select * from keywords WHERE MATCH(keyword_txt) AGAINST
('$radio_keyword' IN BOOLEAN MODE);
If it works the problem is in the join.
Santino
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST
('$radio_keyword' IN BOOLEAN MODE);
At 20:05 -0400 15-10-2004, leegold wrote:
On Fri, 15 Oct 2004 15:00:10 -0700, Chris W. Parker
[EMAIL PROTECTED] said:
leegold mailto:[EMAIL PROTECTED]
on Friday, October 15, 2004 2:32 PM said:
I do fulltext search on work. And AFAIK the search will not find
work. For that matter the seach will not find ingm. How do I
implement in MYSQL/PHP a search that will have this action?
please share the current query you are trying to use and we can go from
there.
Well, my current query is below, it searchs a text type field full of
keywords with each record. The eventual user now said she wants a search
action like I described above, kinda like a find this string that a
text editor does. I would love to keep using Fulltext cause it has
awesome features, but the wildcard can not be prepended *and* appended
to a search term ie. *work*, no can do AFAIK. He's my current query in
php,
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST
('$radio_keyword' IN BOOLEAN MODE);
So I guess I could remove the fulltext index and reindex and do
...WHERE field LIKE '%string%';. But maybe there's a better way? I
wish I could do *searchstring* in Fulltext even if the speed was slow as
molasass it's the spec the user wants.
Lee G.
however, something simple is the following:
SELECT *
FROM table
WHERE field LIKE '%string%';
hth,
chris.
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search help
On Fri, 15 Oct 2004 20:05:57 -0400, leegold [EMAIL PROTECTED] said: On Fri, 15 Oct 2004 15:00:10 -0700, Chris W. Parker ... But maybe there's a better way? I wish I could do *searchstring* in Fulltext even if the speed was slow as molasass it's the spec the user wants. Of course *searchstring* would invalidate the indexing. Thanks. Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext Search help
I'm running into a limitation in the fulltext search though I think by defination a fulltext search will not - or even should not do this, but I'd like to implement this this functionality somehow, given in a text doc. the string: Yesterday I was superduperworkingman at times. I do fulltext search on work. And AFAIK the search will not find work. For that matter the seach will not find ingm. How do I implement in MYSQL/PHP a search that will have this action? Thanks, Lee G. Wash DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
