WithAllTags is slow.

[alexsnkr]

I'm using withAllTags() on a database with 10000 rows in a table and 53655 rows in tagging_tagged, paginating the results then passing it to the view for me to loop through. It takes around 10 seconds just to return the videos with the specified tags, is there a way to speed this up?

[rtconner]

You know, I don't think I added proper indexing to the migration. That's probably the cause.

Edit: On second look, indexes are not bad and probably not the issue. (though they could be improved with unique indexes)

[ghost]

@rtconner

I would bet a billion internet cookies that this slowness is due to abusive whereHas, they are practical, I agree, but they're far away from being fast.

Running multiples subqueries isn't great and as we are a lot to use RDBMS, subqueries with relationships are great for tables that hasn't an impressive amount of information to get, but with per exemples posts tables tagged in a community forum board with like more than 300.000 entries, it is not going to scale out 300.000 subqueries, and neither it will with eager loaded or lazy loaded relations.

[ghost]

@rtconner

I tricked a bit the scope withAllTags and withAnyTag by not using whereHas and using Tagged model instance like this :

public function scopeWithAllTags($query, $tagNames)
{
    $tagNames = TaggingUtil::makeTagArray($tagNames);

    $normalizer = config('tagging.normalizer');
    $normalizer = empty($normalizer) ? 'Conner\Tagging\TaggingUtil::slug' : $normalizer;
    foreach($tagNames as $tagSlug) {
        $className = get_class($query->getModel());

        $tags = Tagged::where('tag_slug', call_user_func($normalizer, $tagSlug))
            ->where('taggable_type', class_basename($className))
            ->lists('taggable_id');

        $query->whereIn($this->getTable().'.id', $tags);
    }

    return $query;
}

public function scopeWithAnyTag($query, $tagNames)
{
    $tagNames = TaggingUtil::makeTagArray($tagNames);
    $normalizer = config('tagging.normalizer');
    $normalizer = empty($normalizer) ? '\Conner\Tagging\TaggingUtil::slug' : $normalizer;

    $tagNames = array_map($normalizer, $tagNames);
    $className = get_class($query->getModel());

    $tags = Tagged::whereIn('tag_slug', $tagNames)
        ->where('taggable_type', class_basename($className))
        ->lists('taggable_id');

    return $query->whereIn($this->getTable().'.id', $tags);
}

I know that those are a bit hacky, but they do work better than the original and loads a lot faster than the original subqueries, by the way, they are completely safe to use with Polymorphic relationships as I currently check if the current model is the corresponding taggable_type

Give a test ;)

[ghost]

Any news @rtconner ?

[rtconner]

Yeah, I'll add this soon, sorry.. been busy.

[rtconner]

I've added these in version 2.0.

Version 2.0 is a major upgrade. You'll need to fix names on the classes / traits.

[ghost]

@rtconner :+1: