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:
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);
Do any of you have an idea what I might have been doing wrong? The main issue here is the query execution time.
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.