I have 3 Tables Person, Family, City
Person Table (6 Million Rows)
- id (primary key auto_increment unsigned)
- family_id (index)
- city_id (index)
- full_name
- first_name (index)
- last_name (index)
- date_of_birth
- record_number
- page_number
Family Table (100k Rows)
- id (primary key auto_increment unsigned)
- area
- street
- house
City Table (120 Rows)
- id (primary key auto_increment unsigned)
- city_name
The Query
SELECT SQL_CALC_FOUND_ROWS
a.id,a.full_name,a.date_of_birth,a.record_number,a.page_number,
family.area,family.street,family.house,
family.family_number,city.city_name
FROM person a
INNER JOIN family ON a.family_id = family.id
INNER JOIN city ON a.city_id = city.id AND a.first_name like '%term%'
JOIN person c ON a.family_id = c.id
JOIN person d ON a.family_id = d.id
JOIN person e ON a.family_id = e.id
AND c.first_name like '%term%'
AND d.first_name like '%term%' AND e.first_name like '%term%' LIMIT 0, 30
This query is taking about 14 seconds! on a Core i7 PC with 8GB RAM
is there any way to improve it ?
this is the EXPLAIN for the query
Is there something wrong with the query ? can i improve it
I added indexes on all rows in the JOIN Clause
Best Answer
Looking at your comments, I can see that the problem is not the
JOIN
s, but theLIKE '%term%'
operators.There are several options here, but assuming you are using MyISAM for your tables, or InnoDB and a MySQL version equal or newer than 5.6, you may use FULLTEXT indexes. MySQL implementation is not perfect, but it will work way better than using '%LIKE%'.
Please note that it is not exactly the same query- it will only match whole words, it may have length restrictions, controlled by
ft_max_word_len
andft_min_word_len
(orinnodb_ft_min_token_size
andinnodb_ft_max_token_size
) and stop words are into play.