need help to optimize delete query

[sheraz]

Hi, i am new to mysql. i am working on a project in which i have to delete users's record from 17 tables. the problem is that when i delete a record, it takes too much time. I am only providing a sample query.The query i write looks like this

 

DELETE a, u, pa, pr

from admin a

LEFT JOIN users u ON u.user_id=a.admin_id

LEFT JOIN poll_answers pa ON pa.user_id=a.admin_id

LEFT JOIN product_reviews pr ON pr.user_id = a.admin_id

WHERE a.admin_id=345

 

i have used this technique to delete from tables.i.e used 17 LEFT JOINS to delete records.

It is working fine but consuming too much time. can somebody help me that how i can optimize query when deleting data from 17 tables.

[ManiacDan]

You can create ON DELETE CASCADE foreign keys in these tables, so when you delete the user record MySQL handles deleting the records from the 16 other tables.

 

Generally problems like this are solved with proper indexing. 

 

-Dan

[fenway]

Trigger cascading is fraught will difficulties

[ManiacDan]

Trigger cascading is fraught will difficulties

Very true!  I never really had problems with it "back in the day" but I always did it on very simple systems like a common attribute-value system or whatever.  17 tables all inter-linking with cascading deletes might be a big problem.

 

Indexing your tables properly is probably the best bet.  Try to SELECT all this data.  Does it come back in a reasonable amount of time?  What about an UPDATE?  Maybe it would be best to add a "deleted" column to these tables so you can "soft delete" the records instead of locking the tables to perform an actual delete.

 

-Dan

[fenway]

Also, cascaded actions won't trigger other triggers, so it's only a cascade in the most basic sense.

[ManiacDan]

Also, triggers won't trigger other triggers, so it's only a cascade in the most basic sense.

Wait...I didn't mean trigger cascade I meant the actual ON DELETE CASCADE command which you place on a foreign key for referential integrity.  It's still fraught with danger but it's not at all a trigger.

 

If I have a table called ITEMS and another called ATTRIBUTES I can make attributes.item_id be a foreign key to items.id ON DELETE CASCADE.  If the item is deleted, the attributes are deleted automatically.

 

-Dan

[fenway]

I simply meant to say that any record deleted by "ON DELETE CASCADE" won't trigger any DELETE triggers on the cascaded tables.

[ManiacDan]

Ok, I didn't know that part.  Interesting.

 

I generally use ORM models anyway so all of this is done in code so it's explicit.  Still interesting though.

 

-Dan

[fenway]

Emphasis added.

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

Note

 

Currently, cascaded foreign key actions do not activate triggers.

 

Personally, I see absolutely nothing wrong with leaving orphans rows lying around -- makes fixing accidental user errors so much easier.

 

Through, granted, with InnoDB, you can get screwed with UID re-use -- I always hated that.