1

I have this tables :

business table :

bussId | nameEn | nameHe | nameAr | status | favor | cityId | categoryId

category table :

categoryId | keywords

favorite table :

userId | bussId

rating table :

userId | bussId | rating

I am running this query which filter businesses with cityId and search (business.nameEn , business.nameAr , business.nameHe , categories.keywords) then order by favor and status and nameEn.

SELECT DISTINCT bussID ,businessName, bussStatus,favor, ratingCount , ratingSum 
FROM
(
     SELECT DISTINCT business.bussID , business.nameEn as businessName , bussStatus,favor,
        (SELECT COUNT(rating.bussId) FROM `rating` WHERE  rating.bussId = business.bussID) as ratingCount ,
        (SELECT SUM(rating.rating) FROM `rating` WHERE  rating.bussId = business.bussID) as ratingSum 
     FROM business LEFT JOIN favourites ON (favourites.bussID = business.bussID AND favourites.userID = '30000')
     INNER JOIN `categories` ON (`categories`.`categoryId` = `business`.`subCategoryId`  ) 
     WHERE  (bussiness.cityID = 11)
            AND (
                    ( REPLACE( REPLACE(REPLACE(LOWER(`bussiness`.`nameEn`),'أ','ا'),'أ','ا') ,'ة','ه') LIKE '%test%' )
                  OR( REPLACE( REPLACE(REPLACE(LOWER(`bussiness`.`nameHe`),'أ','ا'),'أ','ا') ,'ة','ه') LIKE '%test%' )
                  OR( REPLACE( REPLACE(REPLACE(LOWER(`bussiness`.`nameAr`),'أ','ا'),'أ','ا') ,'ة','ه') LIKE '%test%' )
                  OR( REPLACE( REPLACE(REPLACE(LOWER(`categories2`.`keyWords`),'أ','ا'),'أ','ا') ,'ة','ه') LIKE '%test%' )
                )  
           AND
              (bussiness.bussStatus IN(1,3,5,7)
)
GROUP BY bussiness.bussID  )results
ORDER BY

businessName LIKE '%test%' DESC,
FIELD(bussStatus,'1','5','3'),
FIELD(favor,'1','2','3'),
businessName LIMIT 0,10

I am using replace to search case insensitive for أ ا and ة ه letters (before adding the test word I also replace this letters) .

my question :

  1. I want to know How should I declare the indexes properly !

should I declare multiple columns index :

ALTER TABLE `bussiness` 
ADD INDEX `index9` (`nameHe` ASC, `nameEn` ASC, `nameAr` ASC, `favor` ASC, `bussStatus` ASC);

or one columns index for each col !

  1. should I create another col allNamesLanguages which contain nameAr,nameEn,nameHe then I just search this col ?
Improve this question
4
  • Not that 'business' is spelt just so. As for indexes, why not suck it and see? Commented Mar 29, 2015 at 11:45
  • @Strawberry How could I know which indexes my query use ! Commented Mar 29, 2015 at 11:54
  • EXPLAIN will tell you that (and I meant 'note', not 'not') Commented Mar 29, 2015 at 12:15
  • Is this a duplicate of stackoverflow.com/questions/5616063/… ? Commented Apr 3, 2015 at 21:13

2 Answers 2

Reset to default
1

You have two problems with this part of the query that make standard indexes unusable:

               ( REPLACE( REPLACE(REPLACE(LOWER(`bussiness`.`nameEn`),'أ','ا'),'أ','ا') ,'ة','ه') LIKE '%test%' )
              OR( REPLACE( REPLACE(REPLACE(LOWER(`bussiness`.`nameHe`),'أ','ا'),'أ','ا') ,'ة','ه') LIKE '%test%' )
              OR( REPLACE( REPLACE(REPLACE(LOWER(`bussiness`.`nameAr`),'أ','ا'),'أ','ا') ,'ة','ه') LIKE '%test%' )
              OR( REPLACE( REPLACE(REPLACE(LOWER(`categories2`.`keyWords`),'أ','ا'),'أ','ا') ,'ة','ه') LIKE '%test%' )

The first is the use of functions on the columns. The second is the use of like with a pattern that starts with a wildcard ('%').

For the functionality that you seem to want, you are going to need to use full text indexes and triggers and additional columns.

Here is my recommendation:

  • Add (at least) four addition columns that will be used for searching names. Something like business.nameEn_search and so on.
  • Add insert -- and perhaps update and delete triggers that will do the replacement of the special characters when you insert new values. That is, the massive replace( . . . ) logic goes in the trigger.
  • Add a full text index for the four columns.
  • Use match . . . against for your queries.

More information about full text functionality is in the documentation.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thank you for your response ,if I create one col "nameEn_nameAr_nameHe_search" that combine the 3 col then just create one index ! is that good ?
2 question : I am using Innodb engine , is there a problem with using fulltext and match ... against ?.
thank you , and what about creating one col that include the four columns, then create one full text for that col ?
oh according to dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html mysql require 5.6.4 and higher, but I am using 5.6.21b :(
0

Functions basically render indexes useless. Therefore, columns that are used in WHERE clauses like UPPER(name) and else, can be indexed by so-called "function based indexes". They are a feature of Oracle, but as far as I know not in MySQL.

How to use a function-based index on a column that contains NULLs in Oracle 10+?

http://www.mysqlab.net/knowledge/kb/detail/topic/oracle/id/5041

Function-based indexes have their preconditions, though. The function used must be deterministic. So if you would like to index a calculation like "age", it won't work because "age" defined as "now minus then" basically grows each time you select.

My advice is to create more columns and to store the information to be mined there, as prepared as possible.

If you use LIKE "%blabla%", any index will be useless because of the variable text start length. So try to organize the additional columns so that you can avoid LIKE "%... or avoid LIKE at all. According to my experience, adding more columns to indexes won't be a performance stopper for many columns. So just try what happens if you add 4 columns and one combined index for them.

As I understand, you win the game as soon as you can write:

... WHERE nameEn_idx = 'test' AND/OR nameEr_idx = 'test' ...
Improve this answer

2 Comments

thank you for your response , but that doesn't help me because according to your query, the business with name testXY will not be return !
Note also that Collations utf8_unicode_ci and utf8_persian_ci do case folding. Hence UPPER() and LOWER() are unnecessary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.