473,388 Members | 1,499 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

Searching across tables, each with a FULLTEXT index?

I read a while back that MySQL will only use one index per query. (If this
is not so, please tell me and point me to a doc that gives a good
explanation of MySQL's current index usage policy). I'm using MySQL 4.2.x.

Here's my dilemma.

1) ---------

I have two tables that have records with a FULLTEXT index text field in each
of them. The problem is the relationship between the tables is a
one-to-many relationship. I'll make some dummy info to make it easier to
describe the problem:

Table 1: "tableOne"

field: "user_info", text - FULLTEXT indexed

Table 2: "tableMany"

field: "user_comment", text - FULLTEXT indexed

There are many "tableMany.user_comment" records per "tableOne.user_info"
record. So if I want to offer a FULLTEXT search of all the across all the
"user_info" records and all the "user_comment" records, my guess is that
MySQL will reduce it to a fast FULLTEXT search of one of the tables, and
then sequentially search the second? Is that correct?

If so, with the size of my database and the number of requests per day my
server handles, I could not reasonably support that search on my server.

Any thoughts or creative ideas here?

2) -------------------

Just to clarify my knowledge:

If I have a query like:

SELECT * FROM dummy_table WHERE MATCH(some_text_field) AGAINST ("+some
words" IN BOOLEAN MODE) AND user_id=9898989;

Then even if "some_text_field" is FULLTEXT indexed AND user_id is regular
indexed, only one of these indices will be used during the query, is that
correct?

3) -----------------

Also, if an SQL query accesses multiple tables, like in a JOIN, does the one
index per query rule still apply? Or is it one index per table per query (I
hope)?

Thanks.
Aug 25 '05 #1
1 2394
Hi Robert,

I have built a searchengine that indexes the words from multiple tables.
You can put a weight on each column whose values you want it to index.
The weight are used to calcualte a score to sort the result. This would
for example allow you to index newsgroup messages and to give the
subject line 10 times more weight then the message body. In other words,
to get the same ranging one word in the subject counts for 10
occurrences of the same word in the body. To try it out see
http://semantha.metaclass.nl/museum/...=WebSearchPage
As you can see there are website pages, collecters items, books and
cdroms in a single resultset. These come from three different tables
with totally different layout, and some with many fields.

An interesting option is to present the words found in the index so that
the user can select/unselect the words that are approriate. This is
especially nice for languages like german and dutch, where concatenated
words tend to mess up search results. To try this out, select the
checkbox "toon woorden" in the page begind the previous hyperlink. In
this mode you can also add new words by searching for them without
losing the ones you previously selected.

Greetings,

Henk Verhoeven,
www.metaclass.nl, www.phppeanuts.org.

(The search engine is not open source, currently it is only sold as part
of other products)

Robert Oschler wrote:
I read a while back that MySQL will only use one index per query. (If this
is not so, please tell me and point me to a doc that gives a good
explanation of MySQL's current index usage policy). I'm using MySQL 4.2.x.

Here's my dilemma.

1) ---------

I have two tables that have records with a FULLTEXT index text field in each
of them. The problem is the relationship between the tables is a
one-to-many relationship. I'll make some dummy info to make it easier to
describe the problem:

Table 1: "tableOne"

field: "user_info", text - FULLTEXT indexed

Table 2: "tableMany"

field: "user_comment", text - FULLTEXT indexed

There are many "tableMany.user_comment" records per "tableOne.user_info"
record. So if I want to offer a FULLTEXT search of all the across all the
"user_info" records and all the "user_comment" records, my guess is that
MySQL will reduce it to a fast FULLTEXT search of one of the tables, and
then sequentially search the second? Is that correct?

If so, with the size of my database and the number of requests per day my
server handles, I could not reasonably support that search on my server.

Any thoughts or creative ideas here?

2) -------------------

Just to clarify my knowledge:

If I have a query like:

SELECT * FROM dummy_table WHERE MATCH(some_text_field) AGAINST ("+some
words" IN BOOLEAN MODE) AND user_id=9898989;

Then even if "some_text_field" is FULLTEXT indexed AND user_id is regular
indexed, only one of these indices will be used during the query, is that
correct?

3) -----------------

Also, if an SQL query accesses multiple tables, like in a JOIN, does the one
index per query rule still apply? Or is it one index per table per query (I
hope)?

Thanks.

Aug 25 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Ralph Guzman | last post by:
I have to do a catalog search through multiple tables and columns for product model number, description, and name. I realize that doing pattern matching with multiple LIKE statements is slow so I...
0
by: michael calwell | last post by:
Dear all, I have googled until my eyed have fallen out, and still I cannot find what I am looking for, and I would be grateful for any help at all. I can redesign my tables, create new fields...
4
by: Michi | last post by:
I was wondering what the best solution is for making large numbers of TEXT (or BLOB?) fields searchable. For example, if I have a forum, what is the best way to be able to search for specific...
3
by: Ondernemer | last post by:
Hi all, I have been reading my books about this, but can't seem to find the solution. Example: Picture 3 tables: TABLE_1
0
by: Alex Glass | last post by:
I have a large contacts table with about 30 columns of text fields and 5 fulltext indexes spanning the different sections of the table. I'm curious if anyone could suggest a better way to find...
2
by: chernetsov | last post by:
I am creating a server indexing files in my local area network, in order to provide a searching feature. So i want to make it possible to searchsuch rows where the 'name' (VARCHAR) column contains...
8
by: Allan Ebdrup | last post by:
What would be the fastest way to search 18,000 strings of an average size of 10Kb, I can have all the strings in memory, should I simply do a instr on all of the strings? Or is there a faster way?...
1
by: saavedrajj | last post by:
Hi everybody, I need some help. I've started to develop a search for my PHP site, and I'm using FULLTEXT index After all, I created the indexs in the required search fields "title" and "text":...
8
by: Sham | last post by:
I am trying to perform the following query on a table that has been indexed using Full Text Search. The table contains multiple columns than have been indexed. (Below, all xml columns are...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.