Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 tables : Company and category (category table has tags field, I wrote keywords for each category, I used comma(,) as delimiter)

When I search something, it will first search in the company table and then search the query in the tags field.

I wrote the query but I have a problem. When I search "emlak", everything is fine but if I search "emlakçı" it can't find anything. Do I have to enter "emlakçı" as a keyword in tags field or is it possible to solve this with an another way?

Here is my query :

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    MATCH(company.c_title) AGAINST('SEARCH TEXT') as nameScore,
    MATCH(category.tags) AGAINST('SEARCH TEXT') as tagScore
FROM company LEFT JOIN category ON company.c_cat_id = category.c_id
WHERE MATCH(company.c_title) AGAINST('SEARCH TEXT')
OR MATCH(category.tags) AGAINST('SEARCH TEXT')
ORDER BY nameScore DESC
share|improve this question
    
Welcome to the site, Murat. I retitled your question to put an emphasis on full-text search. You can rollback to your original title, but I felt this was clearer to your problem. –  Derek Downey Jul 27 '12 at 15:05
    
thanks @DTest you are right. This is better. –  Murat Jul 28 '12 at 11:03
    
Why are you using a comma delimited field for tags instead of a tags table and a join table? Comma delimited lists in a RDBMS are possible the worst anti-pattern there is. –  Aaron Brown Jul 30 '12 at 16:51
add comment

1 Answer

Here is your original query

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    MATCH(company.c_title) AGAINST('SEARCH TEXT') as nameScore,
    MATCH(category.tags) AGAINST('SEARCH TEXT') as tagScore
FROM company LEFT JOIN category ON company.c_cat_id = category.c_id
WHERE MATCH(company.c_title) AGAINST('SEARCH TEXT')
OR MATCH(category.tags) AGAINST('SEARCH TEXT')
ORDER BY nameScore DESC

You may want to try running the fulltext searching before doing the LEFT JOIN

SELECT
    SQL_CALC_FOUND_ROWS company.*,
    company.nameScore,category.tagScore
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
ORDER BY nameScore DESC;

As for the search string emlakçı, try using a wildcard character. In other words,

MATCH(tags) AGAINST ('emlak*' IN BOOLEAN MODE)

This may pick up more occurrences but should get emlak and similar strings

UPDATE 2012-07-30 18:01 EDT

I don't know your table structure. Assuming name is a field in category, try this:

SELECT
    company.name,category.name,COUNT(1) CategoryCount
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
GROUP BY company.name,category.name;
share|improve this answer
    
Thx for the answer but i get an error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MORE) nameScore FROM company" –  Murat Jul 27 '12 at 7:33
    
I corrected the syntax. I had BOOLEAN MORE which should have been BOOLEAN MODE –  RolandoMySQLDBA Jul 27 '12 at 11:08
    
in addition to this, how can i count how many rows are found in each category –  Murat Jul 30 '12 at 21:48
    
If i use group by , then it returns only one row –  Murat Jul 30 '12 at 22:17
    
Then, the search falls under one category. Is that so ??? –  RolandoMySQLDBA Jul 30 '12 at 22:18
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.