Database search with multi joins

Question

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?

Answer 1

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.

Answer 2

You could retype the query to try to work with smaller recordsets:

 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 (SELECT record
 FROM records_serial
 WHERE serial LIKE '%SEARCH_TERM%') records_serial
 ON records_serial.record = record.id
 LEFT JOIN (SELECT record
 FROM records_barcode
 WHERE barcode LIKE '%SEARCH_TERM%') records_barcode
 ON records_barcode.record = record.id