I have a MySQL database and I want to perform a little bigger search.
I have about 10k records in one of the tables and It's expected to grow, but slowly.
The biggest problem is that to perform the search I have to make a query with 4 JOINS which I think causes the search to be slow.
So here is some example struct:
[table records]
id INT unsigned PRIMARY KEY auto_increment
description text
label INT unsigned
type INT unsigned
price DECIMAL
[table records_labels]
id INT unsigned PRIMARY KEY auto_increment
label varchar
[table records_types]
id INT unsigned PRIMARY KEY auto_increment
type varchar
[table records_serial]
id INT unsigned PRIMARY KEY auto_increment
serial varchar
record INT unsigned
[table records_barcode]
id INT unsigned PRIMARY KEY auto_increment
barcode varchar
record INT unsigned
So here is how the things run:
I run a query which selects records.id, records.description, records.price, records_labels.label, records_types.type, records_serial.serial, records_barcode.barcode;
So the full query is like this:
SELECT
records.id,
records.description,
records.price, records_labels.label,
records_types.type,
records_serial.serial,
records_barcode.barcode
FROM
records
JOIN records_labels ON records_labels.id = records.label
JOIN records_types ON records_types.id = records.type
LEFT JOIN records_serial ON records_serial.record = record.id
LEFT JOIN records_barcode ON records_barcode.record = record.id
WHERE records_serial.serial LIKE %SEARCH_TERM%
OR records_barcode LIKE %SEARCH_TERM%
I think that the solution here is indexing I guess, but I'm not very familiar with it.
So shortly, how to speed up and optimize query of this kind?