0

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?

2 Answers 2

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.