CODEWITHSUNDEEP
mysqlsqldatabaseindexing
david
david

Reputation: 3360

multiple columns index vs index for each col ? mysql

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 ?

Upvotes: 1

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 1

peter_the_oak
peter_the_oak

Reputation: 3710

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' ...

Upvotes: 0

Related Questions