jQuery AutoComplete - Query Database with JOIN

Question

I'm implementing the jQuery autocomplete library on a textbox. The SQL query I have in the back end uses a JOIN:

SELECT DISTINCT wcc.ItemCode, gwbo.ItemName FROM TBL1 gwbo 
INNER JOIN TBL2 wcc ON gwbo.ItemCode = wcc.ItemCode 
WHERE wcc.ItemCode LIKE '$term%' OR gwbo.ItemName LIKE '%$term%' 
AND wcc.CountStatus != 2 ORDER BY wcc.ItemCode LIMIT 0, 10

This takes about 25 seconds to get the autocomplete box to populate and display.. However, when I remove the JOIN and just query 1 table, the autocomplete displays almost immediately (of course the data isn't accurate). Why is my join slowing it down so drastically? I need the join.. Is there a way to speed this up that I am missing, or will I need an entirely new implementation?

Ayush · Accepted Answer · 2013-05-02 20:35:28Z

1

Run EXPLAIN in MySQL to see why the JOIN is taking so long, and what indexes you can set up to make this faster.

EXPLAIN SELECT DISTINCT wcc.ItemCode, gwbo.ItemName FROM TBL1 gwbo 
INNER JOIN TBL2 wcc ON gwbo.ItemCode = wcc.ItemCode 
WHERE wcc.ItemCode LIKE '$term%' OR gwbo.ItemName LIKE '%$term%' 
AND wcc.CountStatus != 2 ORDER BY wcc.ItemCode LIMIT 0, 10

It seems like setting an index on ItemCode could speed up your query. I would recommend adding it, and then running the EXPLAIN again to see if the db is using the newly created index.

Also, LIKE conditions tend to be very slow since no matter what index you set on that column, the db has to do a full-table scan. See if you can change that somehow. Can it be an == instead of LIKE ?

answered May 2, 2013 at 20:35

Ayush

42.5k52 gold badges170 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Rolando Over a year ago

Cool, I'll check it out and get back to you. I need the LIKE to complete the input for users, e.g. Mor input needs to match Moretti

Nick Rolando Over a year ago

Oh my.. The indexes are being used..and..it works much faster :) THANK YOU

Collectives™ on Stack Overflow

jQuery AutoComplete - Query Database with JOIN

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related