Laravel trying to query multiple tables

Question

Not quite sure how to fix this and if this even is an issue with my query or database, so here it goes. There are 3 tables, products, relations and tags.

'products' (
    'ID' bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    'user_id' bigint(20) unsigned NOT NULL,
    'name' varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    'primary_image' bigint(20) DEFAULT NULL,
    'description' longtext COLLATE utf8mb4_unicode_ci,
    'price' float DEFAULT NULL,
    'sale_price' float DEFAULT NULL,
    'currency' varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    'primary_color' varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    'secondary_color' varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    'status' varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    'quantity' bigint(20) DEFAULT NULL,
    'origin' varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    'type' varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    'size' varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    'processing_time' varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    'date_added' int(11) DEFAULT NULL,
    PRIMARY KEY ('ID')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

'relations' (
    'ID' bigint(20) unsigned NOT NULL,
    'relation_id' bigint(20) unsigned NOT NULL,
    'type' varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
    'options' text COLLATE utf8mb4_unicode_ci,
    KEY 'ID' ('ID'),
    KEY 'relation_id' ('relation_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

'tags' (
    'ID' bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    'name' varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
    'slug' varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY ('ID')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Relations between these categories goes as follows:

products.ID = relations.ID
relations.relation_id = tags.ID

There are 50k products, 250k relations and 25k tags added to the database for testing purposes.

I'm building a query that searches within product name, description and tags.

Executing this query:

Product::select('ID', 'name')->where( 'name', 'like', '%'.$search_query.'%' )->orWhere( 'description', 'like', '%'.$search_query.'%' )->orWhereHas('relations', function( $query ) use( $search_query ) {
    $query->where('type', 'tags')->whereHas('tags', function( $query ) use( $search_query ) {
        $query->where( 'name', 'like', '%'.$search_query.'%' );
    });
})->paginate(25);

This query takes around 0.8s to find the data, if I query for a specific tag, it takes as long as 1.8s
I built a similar query with joins and it took even longer so I stayed with the above query for now.

Do any of you have an idea what I might have been doing wrong? The main issue here is the query execution time.

In case you're interested, Laravel Scout https://laravel.com/docs/5.4/scout has a MySql driver https://github.com/damiantw/laravel-scout-mysql-driver. Is there anything wrong with your query other than it takes a long time? — Jeff, Jul 28 '17 at 17:36
Thanks, I'll look into Scout. Not that I'm aware of, just the pure execution time and as we're expecting even more data, I'm worried that the execution time is going to be a problem. — John, Jul 28 '17 at 18:37

Jeff · Accepted Answer · 2017-07-28T19:23:53.913

There is no way to optimize this query that I know. If you had 'LIKE', searchquery.'%' (with no leading %) you could index the text columns for a speed boost. If you need this functionality with the wildcard on both ends, you will have to use a full-text indexing search provider. Algolia https://www.algolia.com/ is the one I know of, and Laravel Scout is created to work with their search. This other question also references http://sphinxsearch.com/ and http://lucene.apache.org/core/ although I don't know what they do.

Edit: also https://www.elastic.co/products/elasticsearch

Laravel trying to query multiple tables

1 Answers1