Search Logic

Problem description:

To enhance the effectiveness of searching movie by title feature, we have implemented conditional searching and sorting according to the relevance of the result. However, this introduces a significant processing delay in the backend server which has direct impact on the frontend displaying latency. Currently our database has over 260,000 movies stored and each searching query now takes quite a long time to be reflected on the screen.

Investigating Methodology:

By assessing the average time for sending a request to the remote server on DigitalOcean, we can therefore study which part of the searching logic results in the long delay. Using Postman, try the combination of conditional searching and sorting with and without indexing for around 10 times to see the approximate time range for the time delay.

Statistical Result:

Testing agent: Chrome Postman

HTTP Request:

• GET /api/movies/title
  • Headers:
    • title: train
    • offset: 0
    • limit: 10

Without Indexing

Time	With conditions in WHERE clause(i.e. LIKE ‘logan’ or LIKE ‘logan %’ or LIKE ‘logan%’...)	Only full substring match used in WHERE(i.e. LIKE ‘%logan%’)
With sorting by relevance in sql using ORDER BY CASE WHEN….	8-9s	~7.5s
Without sorting logic in the sql clause	7-8s	~5s

With Indexing

Time	With conditions in WHERE clause(i.e. LIKE ‘logan’ or LIKE ‘logan %’ or LIKE ‘logan%’...)	Only full substring match used in WHERE(i.e. LIKE ‘%logan%’)
With sorting by relevance in sql using ORDER BY CASE WHEN….	4-5s	1.4-1.8s
Without sorting logic in the sql clause	2-2.5s	0.8-1.2s

Disk usage with indexing:

239288492 B = 239Mb

Observation and final desicion:

Indexing has greatly reduced the time latency in searching in four sections and increased the disk usage of our database tables to around 240Mb. The increase in the data size is still manageable and thus we decided to apply indexing in the movie table to reduce the time taken in search query as a trade-off of space. From the statistics, after indexing the column in movie table, the time for search query has been largely reduced. Our original plan is to keep the search query to be within 2 seconds to make it efficient. Thus, we choose to remove the conditionals search in WHERE clause to only substring match and keep the sorting in sql. If in the future the size of database increases and sorting makes the query to be above 2 seconds, the sorting part will be removed from database sql but done in the backend.