StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Yes, you can unify them very well using a search engine such as Apache Lucene and Solr.</p> <p><a href="http://lucene.apache.org/solr/">http://lucene.apache.org/solr/</a></p> <p>If you need to do it only in MySQL, you can do this with a UNION. You'll probably want to suppress any zero-relevant results.</p> <p>You'll need to decide how you want to affect the relevance depending on which table matches. </p> <p>For example, suppose you want articles to be most important, events to be medium important, and pages to be least important. You can use multipliers like this:</p> <pre><code>set @articles_multiplier=3; set @events_multiplier=2; set @pages_multiplier=1; </code></pre> <p>Here's a working example you can try that demonstrates some of these techniques:</p> <p>Create sample data:</p> <pre><code>create database d; use d; create table articles (id int primary key, content text) ENGINE = MYISAM; create table events (id int primary key, content text) ENGINE = MYISAM; create table pages (id int primary key, content text) ENGINE = MYISAM; insert into articles values (1, "Lorem ipsum dolor sit amet"), (2, "consectetur adipisicing elit"), (3, "sed do eiusmod tempor incididunt"); insert into events values (1, "Ut enim ad minim veniam"), (2, "quis nostrud exercitation ullamco"), (3, "laboris nisi ut aliquip"); insert into pages values (1, "Duis aute irure dolor in reprehenderit"), (2, "in voluptate velit esse cillum"), (3, "dolore eu fugiat nulla pariatur."); </code></pre> <p>Make it searchable:</p> <pre><code>ALTER TABLE articles ADD FULLTEXT(content); ALTER TABLE events ADD FULLTEXT(content); ALTER TABLE pages ADD FULLTEXT(content); </code></pre> <p>Use a UNION to search all these tables:</p> <pre><code>set @target='dolor'; SELECT * from ( SELECT 'articles' as 'table_name', id, @articles_multiplier * (MATCH(content) AGAINST (@target)) as relevance from articles UNION SELECT 'events' as 'table_name', id, @events_multiplier * (MATCH(content) AGAINST (@target)) as relevance from events UNION SELECT 'pages' as 'table_name', id, @pages_multiplier * (MATCH(content) AGAINST (@target)) as relevance from pages ) as sitewide WHERE relevance > 0; </code></pre> <p>The result:</p> <pre><code>+------------+----+------------------+ | table_name | id | relevance | +------------+----+------------------+ | articles | 1 | 1.98799377679825 | | pages | 3 | 0.65545331108093 | +------------+----+------------------+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload