Question

How to remove unused images from database (blob)? To save space in database and keep up performance.

Is there any mysql query that can do this?

Answer 1

For all having the same demand, you can use my new plugin:

New Plugin: List All Uploads v0.2 / Image Upload Manager for Q2A
http://www.question2answer.org/qa/18768/new-plugin-list-all-uploads-v0-2-image-upload-manager-for-q2a

It list all images and gives you the option to delete images that are not used in posts or as avatars. To remove the image from the db, just click on the link below the image: "not found in posts → delete image?"

Answer 2

When an avatar is updated, the previous one is automatically deleted.

So as far as I know, the only way in which there can be stored but unused blobs is if content is uploaded within the WYSIWYG editor for embedding, and then no longer referenced within the HTML itself.

So this would be a big job of scanning all the HTML in all the posts, as well as collecting all the avatar blob IDs from users (as well as the default setting), and then seeing if there are any blobs in the database which are not referenecd.

Comments

and not only the avatars, I mean all images in the questions & posts.

as you said "So this would be a big job of scanning" - and doing it by hand... nope :)