match... against... order by
am 11.10.2002 01:53:31 von Kenneth Smart> SEND-PR: -*- send-pr -*-
> SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
> SEND-PR: will all comments (text enclosed in `<' and `>').
> SEND-PR:
> From: smartkl@telocity.com
> To: mysql@lists.mysql.com
> Subject: match... against... order by
>
> >Description:
> I have a database called kbase containing 2 tables. One table
named
> "articles"
> that contains articles in a knowledge base. The other table is
> called "searches"
> which contains search terms, and what article provides the best
> results for each
> term. When I run a query against these 2 tables I get one
result...
> but when I
> add the ORDER BY command at the end of the query, the results
> change. Here is the
> layout of the 2 tables:
>
> ### -- Layout of articles table. -- ###
> CREATE TABLE articles ( id int(10) unsigned NOT NULL
auto_increment,
> title varchar(200)
> default NULL, body text, PRIMARY KEY (id), FULLTEXT KEY title
> (title,body)) TYPE=MyISAM;
>
> ### -- Layout of searches table. -- ###
> CREATE TABLE searches ( term text, id int(11) default NULL)
> TYPE=MyISAM;
>
> >How-To-Repeat:
> Below is 2 queries, one that shows the results without ORDER BY,
and
> another that
> shows the results with ORDER BY.
>
> mysql> SELECT articles.*, SUM(IF(searches.id = articles.id AND
> (searches.term = 'security'
> --> OR searches.term = 'tutorial'), 1, 0)) AS score_1,
MATCH
> (articles.title,
> --> articles.body) AGAINST ('security tutorial' IN BOOLEAN
> MODE) AS score_2 FROM
> --> searches, articles WHERE MATCH (articles.title,
> articles.body) AGAINST ('security
> --> tutorial' IN BOOLEAN MODE) GROUP BY articles.id;
>
>
>
+----+-------------------------+---------------------------- -----------+----
> -----+---------+
> | id | title | body
> | score_1 | score_2 |
>
>
+----+-------------------------+---------------------------- -----------+----
> -----+---------+
> | 1 | MySQL Tutorial | DBMS stands for DataBase ...
> | 0 | 1 |
> | 3 | Optimising MySQL | In this tutorial we will show
....
> | 0 | 1 |
> | 6 | MySQL Security | When configured properly, MySQL
> ... | 0 | 1 |
> | 7 | MySQL Security Tutorial | A tutorial written to explain...
> | 0 | 2 |
> | 8 | Security Tutorial | MySQL Security Tutorial is
> written... | 2 | 2 |
>
>
+----+-------------------------+---------------------------- -----------+----
> -----+---------+
> 5 rows in set (0.01 sec)
>
> mysql> SELECT articles.*, SUM(IF(searches.id = articles.id AND
> (searches.term = 'security'
> --> OR searches.term = 'tutorial'), 1, 0)) AS score_1,
MATCH
> (articles.title,
> --> articles.body) AGAINST ('security tutorial' IN BOOLEAN
> MODE) AS score_2 FROM
> --> searches, articles WHERE MATCH (articles.title,
> articles.body) AGAINST ('security
> --> tutorial' IN BOOLEAN MODE) GROUP BY articles.id ORDER
BY
> score_1 DESC, score_2 DESC;
>
>
>
+----+-------------------------+---------------------------- -----------+----
> -----+---------+
> | id | title | body
> | score_1 | score_2 |
>
>
+----+-------------------------+---------------------------- -----------+----
> -----+---------+
> | 8 | Security Tutorial | MySQL Security Tutorial is
> written... | 1 | 2 |
> | 7 | MySQL Security Tutorial | A tutorial written to explain...
> | 0 | 2 |
> | 1 | MySQL Tutorial | DBMS stands for DataBase ...
> | 0 | 1 |
> | 3 | Optimising MySQL | In this tutorial we will show
....
> | 0 | 1 |
> | 6 | MySQL Security | When configured properly, MySQL
> ... | 0 | 1 |
>
>
+----+-------------------------+---------------------------- -----------+----
> -----+---------+
> 5 rows in set (0.01 sec)
>
>
>
> >Fix:
> UNKNOWN.
>
> >Submitter-Id:
> >Originator: smartkl@telocity.com
> >Organization:
>
> >MySQL support: none
> >Synopsis: MATCH... AGAINST... ORDER BY Bug
> >Severity: serious
> >Priority: <[ low | medium | high ] (one line)>
> >Category: mysql
> >Class: <[ sw-bug | doc-bug | change-request | support ] (one
line)>
> >Release: mysql-4.0.4-beta (Official MySQL RPM)
>
> >Environment:
>
> System: Linux 2.4.18-3 #1 Thu Apr 18 07:31:07 EDT 2002 i586 unknown
> Architecture: i586
>
> Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
> /usr/bin/cc
> GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
> gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110)
> Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
> CXX='gcc'
>
'-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions
> -fno-rtti -mpentium' LDFLAGS=''
> LIBC:
> lrwxrwxrwx 1 root root 13 Sep 21 07:57 /lib/libc.so.6 ->
> libc-2.2.5.so
> -rwxr-xr-x 1 root root 1343176 Apr 15 07:02 /lib/libc-2.2.5.so
> -rw-r--r-- 1 root root 2310808 Apr 15 07:02 /usr/lib/libc.a
> -rw-r--r-- 1 root root 178 Apr 15 06:55 /usr/lib/libc.so
> Configure command:
>
../configure --disable-shared --with-mysqld-ldflags=-all-static --with-client
> -ldflags=-all-static --without-berkeley-db --with-innodb --without-vio --w
it
>
hout-openssl --enable-assembler --enable-local-infile --with-mysqld-user=mys
>
ql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra
> -charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/
et
>
c --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/share/i
>
nfo --includedir=/usr/include --mandir=/usr/share/man --with-embedded-server
> --enable-thread-safe-client '--with-comment=Official MySQL RPM' CC=gcc
> 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium'
>
'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -f
> no-exceptions -fno-rtti -mpentium' CXX=gcc
>
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12693@lists.mysql.com
To unsubscribe, e-mail