Agreed. This will be taken care of once Ansel is ported to use the 
Tagger code.

I think that this can probably be resolved for now, though I'll leave 
it up to Michael. A lot of different options are now available from 
the Rampage Tagger code in the incubator, including stat tables that 
take care of caching counts for tags and tags + users (and this adds 
user_id to the current Ansel structure, allowing folksonomy-type stuff 
which Ansel doesn't currently have).



On HAVING vs. the JOIN - HAVING might be quicker in some cases but 
using a JOIN also lets us exclude tags from the result set, and it 
also avoids some filesorting most of the time. So the rampage tagger 
goes with the JOIN solution.

In the meantime, I changed the search queries to use GROUP BY / HAVING 
to start comparing it with the queries that we had...any feed back on 
performance is welcome. I'm going to port these changes over to my 
production server data and see what it gets us.

Changes have been made in CVS for this ticket:

http://cvs.horde.org/diff.php/ansel/lib/Tags.php?r1=1.87&r2=1.88&ty=u

1) This patch does nothing to update the count when a tag is removed. 
You only updating the tags that are passed to Ansel_Tags::writeTags() 
this function *always* overwrites the resources's tags, so your not 
capturing when the tag is removed.



2) Was there any special reason you removed the charset conversion is 
some of the queries?



3) the variable $id is not always set, that is why I was using the 
$reults['tag_id'] value - any special reason you didn't want to use 
that?

And here it is solved. When we fill the ansel_tags table we count both 
images and galleries table. To be more simple, I reduced the amount of 
changes just to introduce the tag_count column and avoid the double 
UNION SELECT. Other performance patches will come after this will be 
commited.

followup on the having() idea:

http://blogs.sun.com/dups/entry/having_a_follow_up

An extremely simplistic idea for better tag-related queries:



http://blogs.sun.com/dups/entry/having_a_grand_old_time



Going to try to refactor Ansel's tag code to see what this gets us, 
what other issues it might bring up etc....

Removing as a showstopper, tags work, this would be a nice performance 
enhancement though, once the gallery vs image tag count issue is 
resolved.

Sorry, that should be "the actual number of times that a tag is used in..."

Huh?  Your saving the actual number of tags in ansel_tags.tag_count 
and then your using the value of that field directly in 
Ansel_Tags::readTags() and Ansel_Tags::listTagInfo().  With your 
changes, once any tags are added/removed from an image or gallery, the 
tag counts will never be correct because the value in the 
ansel_tags.tag_count field is only taking into account the number of 
times that tag is used in _either_ the images table *or* the galleries 
table, when it needs to be both.

Actually we don't need to know the actual number of images/galleries 
tag count. Is used only as a flag if the gallery/image has tags or not 
to save later queries. So this can be a boolean value.

It looks like these changes will cause the tag_count field to be 
overwritten with the count of tags in *either* the ansel_images_tags 
or ansel_galleries_tags table...when it should be both.

I agree.

To be honest, I feel that an entire page containing only a tag cloud 
is a bit, well, boring and wasteful considering that the "Browse" page 
can be configured with a cloud of whatever size the user wants.   I 
suppose I can be convinced differently if there is a compelling reason 
that I am missing or the Cloud page contained some other widgets/info 
etc...

becouse getTags() does not return one dimentional array anymore but 
even the tag count

I'm still looking over the changes here, but why do the 
iteration/concatenation in gallery.php instead of using implode()?   
Is there a big performance difference?

and why not, a tag cloud page...

The UNION ALL SELECT query is very waste full SQL command. Expecially 
with the kind of table that tags are implemented with. Is taking 
seconds for my installations with a lot of images. As the server must 
read all table data to perform the counting and proper selection of 
data. So we must help the server and perform the count on the rare 
update operations to have fast common selections.



The patch:

reorganize the Ansel_Tags object store the store counts of used tags 
in the tags table to not count on the fly

mark galleries and images if they have tags or not so we can avoid not 
needed queries with all galleries and images that does not have tags.

Add limit functionality to the tags cloud block