CODEWITHSUNDEEP
mysql
Robbie
Robbie

Reputation: 716

Search Column after LEFT JOIN

Currently I have two tables.

Customers:

id name status
1 adam 1
2 bob 1
3 cain 2

Orders:

customer_id item
1 apple
1 banana
1 bonbon
2 carrot
3 egg

I'm trying to do an INNER JOIN first then use the resulting table to query against.

So a user can type in a partial name or partial item and get all the names and items. For example if a user type in "b" it would kick back:

customer_id name status items
1 adam 1 apple/banana/bonbon
2 bob 1 carrot

What I am currently doing is:

SELECT * FROM(
                SELECT customers.* , GROUP_CONCAT(orders.item SEPARATOR '|') as items
                FROM customers 
                LEFT JOIN orders
                ON customers.id = orders.customer_id 
                group by customers.id 
            ) as t
            WHERE t.status = 1 AND ( t.name LIKE "%b%" OR t.items LIKE "%b%")

Which does work, but it is incredibly slow (+2 seconds). The strange part though is if I run the queries individually the subquery executes in .0004 seconds and the outer query executes in .006 seconds. But for some reason combining them increases the wait time a lot.

Is there a more efficient way to do this?

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(6),
  `name` varchar(255) ,
  `status` int(6),
    PRIMARY KEY (`id`,`name`,`status`)
);
INSERT INTO `customers` (`id`, `name` , `status`) VALUES
  ('1',  'Adam' , 1),
  ('2', 'bob' , 1),
  ('3', 'cain' , 2);
  
CREATE TABLE IF NOT EXISTS `orders` (
  `customer_id` int(6),
  `item` varchar(255) ,
  PRIMARY KEY (`customer_id`,`item`)
);
INSERT INTO `orders` (`customer_id`, `item`) VALUES
  ('1',  'apple'),
  ('1',  'banana'),
  ('1',  'bonbon'),
  ('2',  'carrot'),
  ('3', 'egg');

Upvotes: 0

Views: 78

Answers (2)

Alexander Peresypkin
Alexander Peresypkin

Reputation: 61

According to the query, you are trying to perform a full-text search on the fields name and item. I would suggest adding full-text indexes to them using ngram tokenisation as you are looking up by part of a word:

ALTER TABLE customers ADD FULLTEXT INDEX ft_idx_name (name) WITH PARSER ngram;
ALTER TABLE orders ADD FULLTEXT INDEX ft_idx_item (item) WITH PARSER ngram;

In this case, your query would look as follows:

SELECT
    customers.*, GROUP_CONCAT(orders.item SEPARATOR '|')
FROM
    customers
    LEFT JOIN orders on customers.id = orders.customer_id
WHERE
    orders.customer_id IS NOT NULL
    AND customers.status = 1
    AND (MATCH(customers.name) AGAINST('bo')
        OR MATCH(orders.item) AGAINST('bo'))
GROUP BY
    customers.id

If needed, you could modify ngram_token_size MySQL system variable as its value is 2 by default, which means two or more characters should be input to perform the search.

Another approach is to implement it by means of a dedicated search engine, e.g. Elasticsearch, when requirements evolve.

Upvotes: 1

Gcode
Gcode

Reputation: 154

SELECT * FROM(
        SELECT customers.* , GROUP_CONCAT(orders.item SEPARATOR '|') as items
        FROM customers 
        LEFT JOIN orders
        ON customers.id = orders.customer_id AND customers.name LIKE "%adam" AND orders.item LIKE "%b"
        group by customers.AI

It will be faster to filter the records when starting to left join

Upvotes: 0

Related Questions