CODEWITHSUNDEEP
mysql
Jeet Singh
Jeet Singh

Reputation: 402

how to decide correct index for mysql table

Table structure:

Column  Type    Null    Default
id  bigint(20)  No  
title   varchar(255)    Yes NULL
category_id int(11) Yes NULL
street  varchar(255)    Yes NULL
city    varchar(255)    Yes NULL
state_code  varchar(255)    Yes NULL
zip varchar(11) Yes NULL
phone   varchar(255)    Yes NULL
website varchar(255)    Yes NULL
rating  varchar(5)  Yes NULL
hours   text    Yes NULL
country varchar(255)    Yes NULL
tags    text    Yes NULL
email   varchar(255)    No  
Address varchar(255)    Yes NULL
SecondaryPhone  varchar(255)    Yes NULL
ProductsServices    text    Yes NULL
Specialties text    Yes NULL
BrandsCarried   text    Yes NULL
PaymentMethods  text    Yes NULL
edit_title  varchar(255)    Yes NULL
subcategory_id  int(11) Yes NULL
edit_street varchar(255)    Yes NULL
edit_city   varchar(255)    Yes NULL
state   varchar(255)    Yes NULL
edit_state_code varchar(255)    Yes NULL
edit_country    varchar(255)    Yes NULL
edit_phone  varchar(255)    Yes NULL
your    varchar(255)    Yes NULL
edit_hours  varchar(255)    Yes NULL
edit_email  varchar(255)    Yes NULL
image   varchar(255)    Yes NULL
banner  varchar(255)    Yes NULL
alias_name  varchar(255)    Yes NULL
user_id int(11) Yes NULL
about_us    text    Yes NULL
status  varchar(1)  Yes 0
featured    varchar(1)  Yes 0
views   int(11) Yes 0
your_name   varchar(255)    Yes NULL
your_email  varchar(255)    Yes NULL
contact_no  varchar(255)    Yes NULL
time_to_contact varchar(255)    Yes NULL
request_sent    varchar(255)    Yes NULL
edit_website    varchar(255)    Yes NULL
edit_tagline    varchar(255)    Yes NULL
tagline varchar(255)    Yes NULL
edit_tags   varchar(255)    Yes NULL
national_level  varchar(1)  Yes 0
edit_about_us   text    Yes NULL
about_us_status varchar(1)  Yes 0
plan_status varchar(1)  Yes 1
served_city varchar(255)    Yes NULL
created datetime    Yes NULL
modified    datetime    Yes NULL

SQL query:

EXPLAIN SELECT `Business`.`id`, `Business`.`alias_name`, `Business`.`title`,
    `Business`.`city`, `Business`.`state_code`, `Business`.`image`, `Business`.`about_us`,
    `Business`.`tagline`, `Business`.`phone`, `Business`.`website`, `Business`.`email`
FROM `fp_businesses_jtest1` AS `Business`
WHERE ((((`Business`.`title` LIKE '%Flower Cottage of Landrum%') OR (`Business`.`tagline` LIKE '%Flower Cottage of Landrum%') OR (MATCH (`Business`.`tags`) AGAINST ("Flower Cottage of Landrum" IN BOOLEAN MODE)))) AND (((FIND_IN_SET('Scarborough',`Business`.`served_city`)) OR (`Business`.`city` LIKE 'Scarborough%'))))
ORDER BY `Business`.`modified` DESC;

Result

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE        Business  ALL     fusedindex1     NULL    NULL        NULL    0       Using where; Using filesort

New sql Query: I have removed the like from where clause and added all the column in matach & against. 

      SELECT `Business`.`id`, `Business`.`alias_name`, `Business`.`title`, Business`.`city`, `Business`.`state_code`, `Business`.`image`, `Business`.`about_us`, Business`.`tagline`, `Business`.`phone`, `Business`.`website`, `Business`.`email` FROM `fp_businesses_jtest1` AS `Business` WHERE (MATCH `Business`.`tags`,`Business`.`title`,`Business`.`tagline`) AGAINST ("Flower Cottage of Landrum" IN BOOLEAN MODE)) AND (MATCH (`Business`.`served_city`,`Business`.`city`) AGAINST("Scarborough" IN BOOLEAN MODE)) ORDER BY `Business`.`modified` DESC

Can anyone please advice how i can identify index and optimize table.

Upvotes: 0

Views: 100

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

Your table structure and your query are very hard to optimize.

The use of ORs means the query has to evaluate every single comparison in your where clause.

Some of your comparisons use wild cards at the start of a string comparison (Business.title` LIKE '%Flower Cottage of Landrum%'). The query engine can't use the index in this case; as Vyktor writes, you may be better of using full text searching.

Not sure how FIND_IN_SET behaves with indexing - this question suggest it results in a table scan.

Long and short - I think there's little you can do by adding indexes.

I'd use full text searches for the first part of your query, and I'd split the served_city field into a many-to-many join.

Upvotes: 1

Vyktor
Vyktor

Reputation: 21007

You are using full text string search which is impossible (at least for mysql) to use "classic" index on.

You may try using FULLTEXT INDEX as shown here and you can take a look at mysql full text search tuning but I have always ended up using pre-calculated index table or system like Sphinx.

Upvotes: 1

Related Questions