Performance issue when joining too many table

[joelamltc]

Something like this.

    protected $searchable = [
        'columns' => [
            // trips
            'trip_translations.name'                         => 50,
            'trip_translations.about_this_trip'              => 40,
            'trip_translations.summary'                      => 20,
            'trip_translations.additional_information_title' => 10,
            'trip_translations.additional_information'       => 10,
            'trip_translations.additional_information_right' => 10,
            'trip_translations.options'                      => 10,

            // cities
            'city_translations.name'                         => 50,

            // regions
            'region_translations.name'                       => 50,

            // basic itinerary
            'basic_itinerary.name'                           => 40,

            // trip itinerary
            'trip_itinerary_translations.title'              => 40,
            'trip_itinerary_translations.description'        => 20,

            // interests
            'interest_translations.name'                     => 40
        ],
        'joins' => [
            // trips
            'trip_translations' => [
                'trips.trip_id', 
                'trip_translations.trip_id'
            ],

            // cities
            'trip_cities' => [
                'trips.trip_id',
                'trip_cities.trip_id'
            ],
            'city_translations' => [
                'trip_cities.city_id',
                'city_translations.city_id'
            ],

            // regions
            'trip_regions' => [
                'trips.trip_id',
                'trip_regions.trip_id'
            ],
            'region_translations' => [
                'trip_regions.region_id',
                'region_translations.region_id'
            ],

            // basic itinerary
            'basic_itinerary' => [
                'trips.trip_id', 
                'basic_itinerary.trip_id'
            ],

            // trip itinerary
            'trip_itinerary' => [
                'trips.trip_id', 
                'trip_itinerary.trip_id'
            ],
            'trip_itinerary_translations' => [
                'trip_itinerary.trip_itinerary_id',
                'trip_itinerary_translations.trip_itinerary_id'
            ],

            // interests
            'trip_interests' => [
                'trips.trip_id', 
                'trip_interests.trip_id'
            ],
            'interest_translations' => [
                'trip_interests.interest_id', 
                'interest_translations.interest_id'
            ]
        ]
    ];

The performance will become very shitty. Any suggestions?

[micc83]

Go with search engine indexing (Elasticsearch etc... ) or buy a faster Sql server... This package is great but doing full-text search without indexes is terribly expensive.

[Gummibeer]

It's based in the nature of a SQL-Server it isn't made for search - it's for related data storage. If you want to go for elasticsearch you can use https://github.com/elasticquent/Elasticquent or you use a service like algolia with https://github.com/algolia/algoliasearch-laravel - and I bet that there are plenty more options. But don't do a "real" search in a sql database.