create database task250;
use  task250;
CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    department VARCHAR(50)
);

CREATE TABLE Subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(100)
);

CREATE TABLE Marks (
    student_id INT,
    subject_id INT,
    marks DECIMAL(5, 2),  
    PRIMARY KEY (student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(id)
);

CREATE INDEX idx_firstname ON Students(firstname); 
CREATE INDEX idx_subject_name ON Subjects(subject_name);
CREATE INDEX idx_department ON Students(department);
CREATE INDEX idx_firstname ON Students(firstname); 
CREATE INDEX idx_subject_name ON Subjects(subject_name);
CREATE INDEX idx_department ON Students(department);
SET @searchKey = 'Mana%';

SELECT * FROM (
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE s.department LIKE @searchKey
    LIMIT 10000
) AS dept_results

UNION all

SELECT * FROM (
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE s.firstname LIKE @searchKey
    LIMIT 10000
) AS name_results

UNION all

SELECT * FROM (
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE sub.subject_name LIKE @searchKey
    LIMIT 1000
) AS subject_results

ORDER BY 
    CASE 
        WHEN firstname LIKE @searchKey THEN 1   -- Names starting with searchKey first
        WHEN firstname LIKE CONCAT('%', @searchKey, '%') THEN 2  -- Names containing searchKey anywhere next
        ELSE 3
    END,
    firstname 
LIMIT 10000;

I have 250k records in the database.

API response time acccording to different character searching lengths.

UNION runs three requests in parallel to save time.

How can I get fetch time on my local system under 100ms even for one character?

create database task250;
use  task250;
CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    department VARCHAR(50)
);

CREATE TABLE Subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(100)
);

CREATE TABLE Marks (
    student_id INT,
    subject_id INT,
    marks DECIMAL(5, 2),  
    PRIMARY KEY (student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(id)
);

CREATE INDEX idx_firstname ON Students(firstname); 
CREATE INDEX idx_subject_name ON Subjects(subject_name);
CREATE INDEX idx_department ON Students(department);
CREATE INDEX idx_firstname ON Students(firstname); 
CREATE INDEX idx_subject_name ON Subjects(subject_name);
CREATE INDEX idx_department ON Students(department);
SET @searchKey = 'Mana%';

SELECT * FROM (
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE s.department LIKE @searchKey
    LIMIT 10000
) AS dept_results

UNION all

SELECT * FROM (
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE s.firstname LIKE @searchKey
    LIMIT 10000
) AS name_results

UNION all

SELECT * FROM (
    SELECT s.id, s.firstname, s.lastname, s.department, sub.subject_name, m.marks
    FROM Students s
    JOIN Marks m ON s.id = m.student_id
    JOIN Subjects sub ON m.subject_id = sub.id
    WHERE sub.subject_name LIKE @searchKey
    LIMIT 1000
) AS subject_results

ORDER BY 
    CASE 
        WHEN firstname LIKE @searchKey THEN 1   -- Names starting with searchKey first
        WHEN firstname LIKE CONCAT('%', @searchKey, '%') THEN 2  -- Names containing searchKey anywhere next
        ELSE 3
    END,
    firstname 
LIMIT 10000;

I have 250k records in the database.

API response time acccording to different character searching lengths.

UNION runs three requests in parallel to save time.

How can I get fetch time on my local system under 100ms even for one character?

• sql
• performance
• mysql-workbench

• 1 Try creating indexes on primary/foreign keys to optimize join clauses. – mr mcwolf Commented Mar 3 at 9:42
• 1 In the subqueries the LIMIT without ORDER BY is a code smell. What is the logic for determining which 1000 records are being returned? Currently, MySQL may return any random 1000 records. – Tim Biegeleisen Commented Mar 3 at 9:50
• 1 Why do you allow such input in the first place? Who would ask the DBMS to look for 'Jane' and not know that this is a first name and not a department or subject name? Anyway, this query will never get extremely fast. With WHERE something LIKE @searchKey you can get anything from no rows at all to all rows in the table, and you can't index either, because @searchKey can be anything. Disk defragmentation may be a solution to provide a quicker full table scan. But if you want this type of query really fast, invest in hardware. – Thorsten Kettner Commented Mar 3 at 10:37
• As to your measurements: While it looks like the shorter the search key the longer the run time, it is more likely that the rule is: early measures slow, late measures long. This is because for the first table scans data has to be read from disk. In the following scans, data may already be in cache. – Thorsten Kettner Commented Mar 3 at 11:25
• Here is an explanation why your indexes won't work for you usually: Let's say you look for Math subjects, and there are three Math subjects in the table: 'Basic Math', Advanced 'Math' and 'Mathematical concepts'. In the index, one is at letter B, one at letter A and one at letter M. It's like looking in a telephone book ordered by last name and searching for all last names that contain the letter group 'nde' - you'll have to read the full book in order to find all those names. – Thorsten Kettner Commented Mar 3 at 11:36

1 Answer 1

Few things...

First, remove your duplicate indexes, not sure why they are there, probably copy paste or AI confusion?

CREATE INDEX idx_firstname ON Students(firstname); 
CREATE INDEX idx_subject_name ON Subjects(subject_name);
CREATE INDEX idx_department ON Students(department);
CREATE INDEX idx_firstname ON Students(firstname); 
CREATE INDEX idx_subject_name ON Subjects(subject_name);
CREATE INDEX idx_department ON Students(department);

Further, you should add FULLTEXT indexes on the fields instead even, for example :

ALTER TABLE Students ADD FULLTEXT idx_fulltext_firstname (firstname);

Then utilize Match() Against() which is significantly faster:

WHERE MATCH(s.firstname) AGAINST (@searchKey IN BOOLEAN MODE)

Rinse and repeat for your other fields...

Let me know how it works out and I'll reply if this doesn't do the trick =)