1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

mysql fulltext order by rank and keywords in titles

Discussion in 'MySQL' started by downloadsonline.org, Dec 23, 2010.

  1. #1
    Hello,
    I have a price comparison site.
    It has a mysql table with 6,00,000 rows. (MyISAM)
    The table has the product details of 80 online merchants.

    These merchants are given ranks according to their popularity.

    The table structure looks like this :

    Merchant_Name
    Product_title
    Product_description
    category
    keywords
    merchant_rank

    I have a fulltext index on all the 5 fields except merchant_rank.

    My present search query looks like this :

    select * from table 
    where match (Merchant_Name, Product_title, Product_description, category, keywords) 
    against ('key1 key2') 
    Code (markup):
    Now the results are sorted according to relevancy.
    However, they are not sorted on the basis of merchant_rank. So, products from popular merchants are not being shown before less popular merchants.


    Also, I want to give more priority to keywords in Product_titles compared to Product_descriptions. So, if a keyword is found in Product_titles, those products should be shown before those products that have keywords in product_descriptions.


    So, please inform me how to give priority to keywords in titles and also merchant_ranks.

    I have tried this sql :
    
    select *, match (Merchant_Name, Product_title, Product_description, category, keywords) 
    against ('key1 key2') as score 
    from table 
    where match (Merchant_Name, Product_title, Product_description, category, keywords) 
    against ('key1 key2') 
    order by ((merchant_rank*0.5) + score) desc
    Code (markup):
    However, this query is taking around 5 seconds time to process and using filesort while the previous query is always taking less than one second.


    I have created a new fulltext index on merchant_title and tried this query :

    select *, 
    match (Merchant_Name) against ('key1 key2') as score1, 
    match (Merchant_Name, Product_title, Product_description, category, keywords) 
    against ('key1 key2') as score2 
    from table 
    where match (Merchant_Name, Product_title, Product_description, category, keywords) 
    against ('key1 key2') 
    order by ((merchant_rank*0.5) + score1*2 + score2) desc
    Code (markup):
    However, this query is also taking over 5 seconds and using filesort.

    I tried boolean fulltext search, but it is also taking more time for the above queries.

    Please suggest me what query to use ?

    Thanks.
     
    downloadsonline.org, Dec 23, 2010 IP
  2. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #2
    Sometimes breaking queries into multiple queries gives fast results.

    Following should help.

    1. Make a list of preference you wish to give in result, say Title, Content, popularity of merchant and so on.
    2. If preference is relative to one column, use ordering based on calculations within query.
    3. If preference is based on multiple columns, use multiple queries ordered by the queries on preferred columns first.

    Above will not only help make queries easier to make and fast to execute, but your logic will be more clear all the time with neat small queries which collectively runs faster than one big complex queries.

    However, based on scenarios you may choose when to split queries and when to use one big query.
     
    mastermunj, Dec 27, 2010 IP