1

I am trying to make a search in a db which contains tables poems and authors. I am using replace function to ignore accent characters in db while making the search.

If the title column matches the input it gets high order in search results. If the content matches the input it gets a lower order in search results.

The below query works correctly but I want it to be faster.

I considered an extra column that will hold the accent-ignored copies of content and title columns but this almost doubles the db size so I skipped that option. It is around 7mb now. Since it is a mobile app, I don't want its size grow to 15mb just to ignore the accents.

Select poems._id, title, name, surname, photoName 
from poems,authors 
where ( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(content, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','') 
        LIKE '%input%' 
      OR
        replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(title, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','') 
        LIKE '%input%') 
  AND authors._id = poems.authorId 
ORDER BY (CASE WHEN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(title, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','') 
                    LIKE 'input%' THEN 1 
               WHEN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(title, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','') 
                    LIKE '%input%' THEN 2 
               WHEN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(content, 'Â','a'),'İ','i'),'Î','i'),'Û','u'),'I','i'),'Ş','s'),'Ç','c'),'Ğ','g'),'Ö','o'),'Ü','u'),'â','a'),'ş','s'),'ı','i'),'ö','o'),'ç','c'),'ü','u'),'ğ','g'),'î','i'),'û','u'),'''','') 
                    LIKE '%input%' THEN 3 
               ELSE 4 END)
1
1

I considered an extra column that will hold the accent-ignored copies of content and title columns but this almost doubles the db size so I skipped that option.

Do this. Add the extra column. The cost of the additional memory is nothing compared to anything else you try.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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