Optimize tags table in MySql

[clems365]

Hi all

To make pligg quicker it's important to optimize the tags table, so I think one of the best way is delete the "unique" tag.

You can see with this instruction in your database haow many unique tag to you have

In Mysql run this:

SELECT COUNT(*) AS nb_doubles, tag_words
FROM   pligg_tags
GROUP  BY tag_words
HAVING nb_doubles < 2

My trouble is I don't know how to delete those tags....If somebody has an idea it will be great.

Thanks for your help

Best regards

Clément

[redwine]

The term "unique" does not apply in this case. I believe what you meant is "having a count of 1".

Assuming you have these tags:
3 love
3 technical
2 philosophy
2 arts
1 peace
1 war
1 history

In total, you have 13 tags and 7 unique tags.

Now, according to your suggestion, you want to delete every tag that appears only once. My question is how these tags are going to grow and appear in the cloud if you keep deleting the tags with a count of 1?

Please provide more details to see how we can help.

[clems365]

Hi

I have more 50.000 tags....So I would like every week for example make an optimize, so yes I would like to delete the unique tags beacause next a week thoses tags aren't relevant and just take a lot of space and ressouce.

thanks for your help

clement

[redwine]

Originally Posted by clems365

Hi

I have more 50.000 tags....So I would like every week for example make an optimize, so yes I would like to delete the unique tags beacause next a week thoses tags aren't relevant and just take a lot of space and ressouce.

thanks for your help

clement

It is a bit complex. First, to delete them (the tags with a count of 1), you have to run the query you posted and then from the result set, you only insert into a new temporary table all the records with a count > 1, then you have to delete all records from the initial table_tags and insert the records from the temp table into it.

I can see at least 2 problems here:

1. What about the tag_link_id field, and how are you going to allocate the tags to it?
2. The tags displayed within the story (summary and full) are taken from the Links table from the link_tags field. Any deleted tag from the tags table will still be displayed. What if a user clicks on it?

[xenfra]

Originally Posted by clems365

Hi all

SELECT COUNT(*) AS nb_doubles, tag_words
FROM   pligg_tags
GROUP  BY tag_words
HAVING nb_doubles < 2

Clemente, this is the only thing that you have done for tags? I've seen your site and seems that work's fine without the problems of the other replys.
Thank's
Francesco

[clems365]

Hi Francesco

Without the tags include my pligg site run perfectly, the trouble is the tags include, it's a big query for the database, so it's very important to optimize this table.

For my part I believe that important to delete the old unique tags, this will improve the speed of pligg

[xenfra]

I try.
then I would like to delete specific tag (that I can put in a txt file). I have asked in forum but nobody answer me.
You now if is possible to delete specific tags?

[clems365]

In PhpMyAdmin, selec the "pligg_tags", next on the top page, select "search", next on the bottom there is "tags_word" ... "like" here put your tag for exemple "le" and next click on execute and you will have all the "le" tags words

A leat select all and delete

That all

Regards

Clément