MYSQL query faster

Question

How can I make this query faster? It just a way to select fields where you can input varchar text.

    SELECT
        F.field_name,
        FI.farmer_name,
        FI.lname_farmer,
        FI.farmer_email,
        FI.farmer_group,
        L.first_name,
        L.last_name,
        L.email,
        EI.extension_name,
        EI.lname_ext,
        EI.other_prof,
        EI.extension_name
    FROM
        anReference AS R
    INNER JOIN anFieldInfo AS F ON R.ref_id = F.ref_id
    INNER JOIN anFarmerInfo AS FI ON F.ref_id = R.ref_id
    INNER JOIN anCropInfo AS C ON C.ref_id = R.ref_id
    INNER JOIN anLocalTechnician AS L ON L.ref_id = R.ref_id
    INNER JOIN anSessionExtension AS SE ON SE.ref_id = R.ref_id
    INNER JOIN anExtensionInfo AS EI ON EI.extension_id = SE.extension_id
    where   R.date_accessed BETWEEN '2016-03-20 00:00:00'
AND NOW() AND (
            F.field_name LIKE '%test%'
            OR FI.farmer_name LIKE '%test%'
            OR FI.lname_farmer LIKE '%test%'
            OR FI.farmer_email LIKE '%test%'
            OR FI.farmer_group LIKE '%test%'
            OR L.first_name LIKE '%test%'
            OR L.last_name LIKE '%test%'
            OR L.email LIKE '%test%'
            OR EI.extension_name LIKE '%test%'
            OR EI.lname_ext LIKE '%test%'
            OR EI.other_prof LIKE '%test%'
            OR EI.extension_name LIKE '%test%'
        ) AND r.category = 0;

I already tried using concat but it has no to little effect.

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE L ALL \N \N \N \N 84428 1 SIMPLE SE ref ref_id ref_id 4 phdraft.L.ref_id 1 Using index 1 SIMPLE EI eq_ref PRIMARY PRIMARY 4 phdraft.SE.extension_id 1 1 SIMPLE FI ALL \N \N \N \N 717882 Using join buffer 1 SIMPLE C eq_ref ref_id ref_id 4 phdraft.SE.ref_id 1 Using where; Using index 1 SIMPLE F eq_ref ref_id ref_id 4 phdraft.L.ref_id 1 Using where 1 SIMPLE R eq_ref PRIMARY PRIMARY 4 phdraft.F.ref_id 1 Using where — Sandy Pabilonia
– Sandy Pabilonia, Commented Mar 21, 2016 at 5:04

Rick James · Accepted Answer · 2016-03-21 05:44:26Z

Plan A. Create a FULLTEXT index on each set of fields in each of the tables, then use one MATCH..AGAINST per table to do the text searching. But, since the stuff is scattered across 4 tables, do a UNION of 4 SELECTs, each one has a MATCH for one table. Caveat: It works only on "words" and has other restrictions. Please provide SHOW CREATE TABLE; without it, I can't do much more than this "hand waving".

Here's how you could tackle it: Think of how to write the SELECT to test the field(s) for only 1 table, yet do all the JOINing to get the desired result. Repeat for the other 3 tables. Then UNION DISTINCT the 4 SELECTs together.

Plan B: Build another table with the CONCAT of all those fields in a single column; search that table.

Plan C: Change the UI.

Plan D: The UNION describe in Plan A might be a little faster, even with the awful LIKE '%text%'. This is because the temp table would have less to shovel around.

Plan A will be much faster, but the caveats may prevent use of it. Plan B won't be much faster.

A VIEW us syntactic sugar; not a performance booster.

Ashish Vaghasiya · Accepted Answer · 2016-03-21 04:43:12Z

0

Please Create A View For your above query and Get data using view its faster than your current query.

answered Mar 21, 2016 at 4:43

Ashish Vaghasiya

3275 silver badges20 bronze badges

2 Comments

Sandy Pabilonia Over a year ago

How do I state in the query that if it found '%test%' in farmer_name I don't need to look into field_name if it has '%test%'

Ashish Vaghasiya Over a year ago

you not included where in when you creating view.

Collectives™ on Stack Overflow

MYSQL query faster

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related