Database search with multi joins 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?
To speed up query I would recommend indexes on the columns you join in:


records.id, records.type, records.label (combined index)
records_labels.id
records_types.id
records_serial.record


Depending on the data in records_serial.serial and records_barcode.* (which column do you want to query here?) it may be also useful to add indexes here.

That index suggestion is optimized for this single query. You should also consider the disadvantages for other queries.