php - Laravel trying to query multiple tables -

not quite sure how fix , if issue query or database, here goes. there 3 tables, products, relations , 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 follows:

• products.id = relations.id
• relations.relation_id = tags.id

there 50k products, 250k relations , 25k tags added database testing purposes.

i'm building query searches within product name, description , tags.

executing 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 find data, if query specific tag, takes long 1.8s
• i built similar query joins , took longer stayed above query now.

do of have idea might have been doing wrong? main issue here query execution time.

there no way optimize query know. if had 'like', searchquery.'%' (with no leading %) index text columns speed boost. if need functionality wildcard on both ends, have use full-text indexing search provider. algolia https://www.algolia.com/ 1 know of, , laravel scout created work search. other question references http://sphinxsearch.com/ , http://lucene.apache.org/core/ although don't know do.

edit: https://www.elastic.co/products/elasticsearch