Searching across tables, each with a FULLTEXT index?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Robert Oschler
    #1

    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_comme nt", 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_comme nt" 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_fiel d" 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.


    Tags: None
    • Henk Verhoeven
      #2
      Re: Searching across tables, each with a FULLTEXT index?

      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/site/index.php?zoekveld=fossiel&search=zoeken&pntHandler=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:[color=blue]
      > 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_comme nt", 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_comme nt" 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_fiel d" 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.
      >
      >[/color]

        Comment

        Previous template Next
        Working...