i must create a query and need your help

i have 134 databases under same connection(i use mysql administration tools:mysql workbench,navicat and SQLyog).

i must execute a query for finding and removing the rows including "string1,string2,...string8" from "wp-post" tables but without querying all of tables.

which is the best way to find and delete the rows with theses strings without querying more than 2650 tables in 134 databases.

i will be grateful if you can help me

Best Regards seyfigo

asked Sep 27 '11 at 05:16

seyfigo's gravatar image


2 Answers:

Dear Seygifo,

Is this a one-time problem you have, or will you need to perform this task on a regular basis?

Anyhow, there is no other way in SQL than querying all those tables. Considering the number of tables, you'll probably want to automate that with a scripting tool.

If you would need it regularly, you could consider using a view of unions. But honestly, that's a makeshift only. It will probably lead to a maintenance nightmare (if new databases are added) and performance will not at it's best. Even if an index is used on each table, it still has to be used hundredths or thousand times.

However, I'm still not sure if I understood your question correctly. Let me know in case I'm completely off track.

answered Sep 27 '11 at 09:26

Markus%20Winand's gravatar image

Markus Winand ♦♦

Dear Markus,

Thank you so much for your response, situation is updated !

by the way i will try to be most clear !

Actual Situation Now is :

We have exactly 55 hosting packages in each hosting package we have between 15-45 databases, %95 of theses databases are wordpress databases and we can access to theses databases using sqlyog or by ssh console.

now what we want to do exactly :

we must execute a cleaning operation and we decided to make it every night !

in this operation we need to query wp-post tables for searching and deleting post who include some filtered words and filtered urls

number of filtered words is: ~20 number of filtered urls is : ~12

As we can guess, to make it from outside of servers seems to be very difficult,

So actually i decided to find an solution who will work directly on server every night,

it can be a script executed by cron every night !

Can some one help me please

Thank's in advance ! seyfigo

answered Nov 14 '11 at 02:23

seyfigo's gravatar image