Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    14

    Question Unanswered: Speeding up MATCH AGAINST

    Hi,
    I have a query for retrieving related content of an article. It join articles with categories and makes 2 match againsts both tables.
    The problem is the query takes 4 seconds and the server its crashing

    The query is:
    Code:
    SELECT page_id AS sid, page_title AS titulo MATCH(page_title, page_adicional_title) AGAINST(:keywords) * MATCH(categorie_title, categorie_title_adicional) AGAINST(:ckeywords) AS score FROM articles INNER JOIN categories ON page_categorie_id = categorie_id WHERE MATCH(page_title, page_adicional_title) AGAINST(:keywords) AND MATCH(categorie_title, categorie_title_adicional) AGAINST(:ckeywords) AND page_id <> :sid AND page_active = 1 AND page_categorie_id IN ('LOTS_OF_CATEGORIES_ID') AND page_idioma = 'LANG' ORDER BY score DESC LIMIT 5"
    When removing AND page_categorie_id IN ('LOTS_OF_CATEGORIES_ID') the query takes only 0.15 seconds. But those CATEGORIES_ID (from 1 to 300 IDs) are the categories related with the category of the article. Other categories would retreive articles from other domains and I don't want that.

    Also, when removing MATCH AGAINST from WHERE, the query is pretty fast.

    Is there a SQL way to speed up the query or removing MATCH AGAINST from WHERE work just fine retrieving the same results?

    Thank you in advance.

  2. #2
    Join Date
    Oct 2011
    Posts
    14
    Hi,
    Again with the same. I found time ago what removing the MATCH AGAINST from the WHERE clause, the query improves a lot, and the result seems to be the same.

    Code:
    SELECT page_id AS sid, page_title AS titulo MATCH(page_title, page_adicional_title) AGAINST(:keywords) * MATCH(categorie_title, categorie_title_adicional) AGAINST(:ckeywords) AS score FROM articles INNER JOIN categories ON page_categorie_id = categorie_id WHERE page_id <> :sid AND page_active = 1 AND page_categorie_id IN ('LOTS_OF_CATEGORIES_ID') AND page_idioma = 'LANG' ORDER BY score DESC LIMIT 5"
    With the original query the explain says the query is a fulltext on and the new version is a range & eq_ref SELECT.

    My question is, removing the MATCH AGAINST from the WHERE, in this case, the query returns the same results?

    Thank you in advance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •