Question 72334 in Mysql Question posted in
The official MySQL documentation can be found here.

Fulltext index or composite index in MySQL?

I have two tables like this :

Enterprises

FullName      Type
ABC aze        1
DEF rty        2

Addresses

Address1          Address2 Address3    Postcode    City
165 Ward Street                        E1 7AW      London
167 Ward Street                        E1 7AW      London
10 Auckland Street                     N1 0AA      London

I will make a lot of queries to search if the address exists and/or the enterprise exists with =.

For the enterprise’s query, it will be on VARCHAR and SMALLINT (Type column) fields : WHERE Type = 1 AND FullName = '...'

For the addresse’s query, it will be on VARCHAR fields : WHERE address1 = '...' AND address2 = '...' AND address3 = '...' AND postcode = '...' AND city = '...'

To have best response time, should I use composite index or FULLTEXT index ?

Tags:

2

Answers


  1. 0

    Full-text searching is performed using MATCH() AGAINST() syntax, since you are using WHERE and operator = means you are searching the exact string, so the composite index is the best choice for you :

    create index entreprises_idx on Enterprises(FullName, Type);

create index addresses_idx on Addresses(address1, address1, address1, postcode, city);

    If you want to run a full text search then you must have a full text index on the columns you’ll be running it against.

    Login or Signup to reply.
  2. 0

    Short answer: Composite index.

    Long answer:

    WHERE Type = 1 AND FullName = '...'

    –>

    INDEX(Type, FullName)   -- (either order is ok)


WHERE address1 = '...' AND address2 = '...' AND
      address3 = '...' AND postcode = '...' AND city = '...'

    –>

    INDEX(address1, address2, address3, postcode, city)  -- (any order)

    The above indexes will be faster than FULLTEXT because it is a perfect match (all =, no ranges, etc).

    Don’t you need to worry about "fuzzy" matching? If you do, neither INDEX, nor FULLTEXT will be of much use.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search