1

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

enter image description here

Is there something wrong with the query ? can i improve it I added indexes on all rows in the JOIN Clause

  • If you have proper indexes on columns used in JOIN clause then make sure how much time it takes to execute if you remove LIKE operators ? And why these joins 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 – aasim.abdullah Oct 18 '14 at 16:56
  • @aasim.abdullah if i replaced like with equal operator it takes 0.07 sec but the problem is 'like' is nessecary – Hmmm Oct 18 '14 at 17:01
  • 1
    @Coninglnsane that i was trying to prove. Actual problem in your query is LIKE operator. Optimizer are not good to handle LIKE OPERATOR as cost estimation is not simple with this operator, resultantly poor execution plan is selected to execute query. If %term% is the only criteria then you can create extra column in person, a computed column with rule that if firstname like %term% then bit value 1 else 0. or like that. – aasim.abdullah Oct 18 '14 at 17:23
  • the %term% is used in case if someone didn't right the name exactly if the first_name = "Fairamay Sophie" and someone search for "Fairamay" the result set will be zero – Hmmm Oct 18 '14 at 18:10
  • You are right, but you must keep in mind that wildcard '%' on both side will lead to index scan and in result poor performance. Normally first name is a single complete word so using wildcard make no sense but still you know your business rules better. – aasim.abdullah Oct 18 '14 at 21:47
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.

  • The results were much better but there is one more problem this solution +term* does not match for example first_name = 'ABRAHAM' if i searched for 'BRAHAM' the results will be zero how do i add a wild card for the left side as well ? – Hmmm Oct 19 '14 at 20:52
  • 1
    @CodingInsane Please note that the above FULLTEXT search would match "Al Capone" if you searched '+Cap*'. Postfix match is not possible with MySQL fulltext, you may have to look for more advanced wildcard support on Sphinx or Lucene. – jynus Oct 20 '14 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.