Mysql Question posted in
The official MySQL documentation can be found here.

How to improve running time of mysql query that have INNER JOIN and Like search

Below is the query added by me, But it took say 0.35 second, Thoulgh it is not a large value, I am worried that the same query can cause memmory and processor load when the traffic is high on the website

SELECT count(P.id) as total FROM pages P
       INNER JOIN users U ON U.id = p.user_id
       INNER JOIN events E ON P.id = E.page_id
       WHERE (P.name LIKE '%keyword%' OR U.name LIKE '%keyword%') AND E.date > '2023-01-01';

The above mysql query took 0.35 seconds

But If I run the below query without JOIN it took only 0.02 seconds

SELECT count(P.id) as total FROM pages P

Note: I have indexed user_id on pages table & page_id in events table

Is there any way to otimise the above query to get the correct result with less running time

Tags:

2

Answers


  1. 0

    Add Indexes:
    Since you have already indexed the user_id on the pages table and page_id in the events table, you can add an index on the date column in the events table. This can improve the performance of the query by making the data retrieval faster.

    Use subqueries instead of JOINs:
    Using subqueries instead of joins can sometimes lead to better performance. You can rewrite the query as follows:

    SELECT COUNT(id) AS total FROM pages WHERE id IN (
  SELECT page_id FROM events WHERE date > '2023-01-01'
) AND name LIKE '%keyword%'
UNION
SELECT COUNT(id) AS total FROM pages WHERE user_id IN (
  SELECT id FROM users WHERE name LIKE '%keyword%'
) AND name LIKE '%keyword%';

    Here’s how it works:

    The first query retrieves the count of pages that match the keyword and have an associated event that occurred after January 1, 2023. The second query retrieves the count of pages that match the keyword and are owned by a user whose name matches the keyword.

    When we combine these two queries using UNION, we get two rows of data, one for each query. Each row contains a single column with the count of pages that matched the respective query.

    So in the final result set, you will see two rows, each with a "total" column that contains the count of pages that matched the corresponding query.

    If you only want a single row with the total count of pages that matched both conditions, you can wrap the entire query in an outer SELECT statement and use SUM to add up the counts from the two rows:

    SELECT SUM(total) AS total_count FROM (
  SELECT COUNT(id) AS total FROM pages WHERE id IN (
    SELECT page_id FROM events WHERE date > '2023-01-01'
  ) AND name LIKE '%keyword%'
  UNION
  SELECT COUNT(id) AS total FROM pages WHERE user_id IN (
    SELECT id FROM users WHERE name LIKE '%keyword%'
  ) AND name LIKE '%keyword%'
) AS subquery;

    This will give you a single row with the total count of pages that matched both conditions.

    Login or Signup to reply.
  2. 0

    LIKE with a leading wildcard cannot use an index. So, it will be slow.

    Adding a FULLTEXT(name) index will make the search for words much faster:

    WHERE MATCH(events.name) AGAINST('keyword' IN BOOLEAN MODE)

    But only one table at a time. So also use the UNION technique suggested by Ajimi.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search