Large Data Long Query A Lot of Joins horibble performance

Question

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

Answer 1

Looking at your comments, I can see that the problem is not the JOINs, but the LIKE '%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%'.

mysql> CREATE TABLE person (id SERIAL, first_name VARCHAR(100));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into  person (first_name) VALUES('AARON'), ('ABDUL'), ('ABE'), ('ABEL'), ('ABRAHAM'), ('ABRAM'), ('ADALBERTO'), ('ADAM'), ('ADAN'), ('ADOLFO'), ('ADOLPH'), ('ADRIAN'), ('AGUSTIN'), ('AHMAD'), ('AHMED'), ('AL'), ('ALAN'), ('ALBERT'), ('ALBERTO');
Query OK, 19 rows affected (0.01 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE person ADD FULLTEXT INDEX(first_name);
Query OK, 0 rows affected, 1 warning (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM person WHERE MATCH(first_name) AGAINST ('+al*' IN BOOLEAN MODE);
+----+------------+
| id | first_name |
+----+------------+
| 17 | ALAN       |
| 18 | ALBERT     |
| 19 | ALBERTO    |
+----+------------+
3 rows in set (0.00 sec)

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 and ft_min_word_len (or innodb_ft_min_token_size and innodb_ft_max_token_size) and stop words are into play.