Mysql LEFT JOIN is slow

Question

I have the below code, which returns 10 entries. It seems slow now.

SELECT a.id
       , a.patient_name
       , a.phone
       , p2.card_number 
 FROM patients as a
 LEFT JOIN patient_insurance as p2
 ON a.id=p2.patient 
 WHERE a.patient_name LIKE '%$_GET[term]%' 
       OR a.id LIKE '%$_GET[term]%' 
       OR a.phone LIKE '%$_GET[term]%' 
       OR p2.card_number LIKE '%$_GET[term]%' 
 LIMIT 10

I have 12,107 Entries in the patients table 2,296 entries in the patient_insurance table

It is a xeon 2 processor linux server, with 16GB ram.. Locally Hosted

Maybe your table is not indexed properly. Should it be indexed by the patient id? — icanc, Jul 16 '13 at 18:39
(Aside: **Your code is vulnerable to SQL injection.**) Any indexes? Any way of remodelling your data to avoid wildcard prefixes? Considered fulltext search? — eggyal, Jul 16 '13 at 18:39
give me a better code, this site is hosted locally, not even connected to the internet — Vish K, Jul 16 '13 at 18:41
Run the a explain of your query and post the results here. It will show us what the query is doing. This post does not contain enough information to solve it — Namphibian, Jul 16 '13 at 23:19
its 2 tables, table 1 with patient information, 2 with insurance information. the ID(primary key), is added to the insurance table also. so the search will look in patient table (id, patient name, phone number), then the insurance card table(only insurance card) — Vish K, Jul 20 '13 at 21:17

score 3 · Answer 1 · answered Jul 16 '13 at 23:24

Any query that uses LIKE with wildcards as you are doing is bound to be hundreds or thousands of times slower than using a fulltext search solution. There is no conventional B-tree index you can define on a string column that will make wildcard searches faster.

Please read my presentation Full Text Search Throwdown, in which I compare several fulltext search solutions for MySQL.

The short answer is: use Sphinx Search.

Mysql LEFT JOIN is slow

1 Answers1