Jump to content

Advanced MySQL query


toxicious

Recommended Posts

I had a problem with my front-end that messed up my db a little, but it is somewhat easy to fix. Now I am wondering if I can fix it just by doing an mysql query, else I will need to create a php script which does multiple queries and some loops. This is basically what I want to do:

SELECT id FROM mirrors

then for each id

if SELECT COUNT(*) FROM files WHERE mirror_ids REGEXP '([[:<:]]|;){$id}([[:>:]]|;)' == 0 then DELETE FROM mirrors WHERE id = {$id}
In words (beware of bad description, code ftw): I want to delete all the mirrors, that aren't listed in the files table (un-indexed mirrors). Is it possible to create a nice MySQL query that does it?
Link to comment
Share on other sites

You may want to retrick the way your database is structured. If you do it properly, you can simply define foreign key constraints such that a file could only exist an an actual mirror, and you can easily clean up the mirrors every now and then.I suggest you create a "files" table without any mirror information in it (i.e. this table should contain information that identifies the file across the mirrors). Then create a "mirrors" table with no file information in it, and then create a third table (let's call it "file_mirrors") that has a "file_id" and "mirror_id" in it, with both of these columns being constrained to their respective tables. Also define "ON UPDATE CASCADE" and "ON DELETE CASCADE" actions on both of those, so that this 3rd table is automatically cleaned up after deleted/updated files and mirrors.When you have that, deleting mirrors that have no associated files is as simple as

DELETE FROM `mirrors` WHERE `id` NOT IN (SELECT DISTINCT `mirror_id` FROM `file_mirrors`)

Link to comment
Share on other sites

Great article you linked me too!I would love to improve the design my db, especially now when it has grown a lot.I guess I could just rework my current design into separate tables and then set the foreign keys since I already have ids on both the files and the mirrors :) I'm gonna create a php script taking care of all the mirrors, putting them into the file_mirrors table together with the correct addon id. (the file_mirrors table will have as many rows as mirrors, right?) Then I just need to set the keys. Though I need some help where to set them so it works like this: When I delete a mirror, the corresponding row in file_mirrors should be deleted.When I delete a file, all the corresponding rows in file_mirrors should be deleted, and all the mirrors belonging to it should also get deleted (inside mirrors). Is this right?:

ALTER TABLE filesADD CONSTRAINT files_conFOREIGN KEY (id) REFERENCES file_mirror(file_id)ON UPDATE CASCADEON DELETE CASCADE ALTER TABLE mirrorsADD CONSTRAINT mirrors_consFOREIGN KEY (id) REFERENCES file_mirror(mirror_id)ON UPDATE CASCADEON DELETE CASCADE ALTER TABLE file_mirrorsADD CONSTRAINT file_mirrors_consFOREIGN KEY (mirror_id) REFERENCES mirrors(id)ON UPDATE CASCADEON DELETE CASCADE ALTER TABLE file_mirrorsADD CONSTRAINT file_mirrors_cons2FOREIGN KEY (file_id) REFERENCES files(id)ON UPDATE CASCADEON DELETE CASCADE

EDIT: thought about one thing, how does adding work with the new structure?I assume I do everything manually?

Edited by toxicious
Link to comment
Share on other sites

You may want to create new tables from scratch, and then create a script that would transfer the data from your old tables into the new ones. It's the best way to ensure things are designed properly.

(the file_mirrors table will have as many rows as mirrors, right?)
No. It will have as many rows as all permutations between files and mirrors. If a file is at 2 mirrors, that table will have 2 rows (one for each mirror), and if one mirror has 3 files, there will be 3 rows in that table (one for each file).It will contain rows with stuff like "file 1 is at mirror 1, file 1 is at mirror 2, file 1 is at mirror 3, file 2 is at mirror 3...". You're never, at any point, saying "file 1 is at mirror 1, mirror 2 and mirror 3". This is only determined later when you make something like "SELECT `mirror_id` FROM `file_mirrors` WHERE `file_id` = 1".Here's a create statement for the new tables (extracted verbatim from MySQL Workbench; in a DB called "db"; make sure to edit that accordingly):
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';CREATE SCHEMA IF NOT EXISTS `db` ;SHOW WARNINGS;USE `db` ;-- ------------------------------------------------------- Table `db`.`files`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `db`.`files` (  `file_id` INT NOT NULL AUTO_INCREMENT ,  `name` VARCHAR(255) NOT NULL ,  PRIMARY KEY (`file_id`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `db`.`mirrors`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `db`.`mirrors` (  `mirror_id` INT NOT NULL AUTO_INCREMENT ,  `name` VARCHAR(255) NOT NULL ,  PRIMARY KEY (`mirror_id`) )ENGINE = InnoDB;SHOW WARNINGS;-- ------------------------------------------------------- Table `db`.`file_mirrors`-- -----------------------------------------------------CREATE  TABLE IF NOT EXISTS `db`.`file_mirrors` (  `file_id` INT NOT NULL ,  `mirror_id` INT NOT NULL ,  PRIMARY KEY (`file_id`, `mirror_id`) ,  CONSTRAINT `fk_file_mirrors_files`    FOREIGN KEY (`file_id` )    REFERENCES `db`.`files` (`file_id` )    ON DELETE CASCADE    ON UPDATE CASCADE,  CONSTRAINT `fk_file_mirrors_mirrors1`    FOREIGN KEY (`mirror_id` )    REFERENCES `db`.`mirrors` (`mirror_id` )    ON DELETE CASCADE    ON UPDATE CASCADE)ENGINE = InnoDB;SHOW WARNINGS;CREATE INDEX `fk_file_mirrors_files` ON `db`.`file_mirrors` (`file_id` ASC) ;SHOW WARNINGS;CREATE INDEX `fk_file_mirrors_mirrors1` ON `db`.`file_mirrors` (`mirror_id` ASC) ;SHOW WARNINGS;SET SQL_MODE=@OLD_SQL_MODE;SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

The ON UPDATE CASCADE and ON DELETE CASCADE ensure that if you update/delete a file, all entries in file_mirrors for that file will be updated/deleted (i.e. the file is deleted for all mirrors), and if you update/delete a mirror, all entries in file_mirrors for that mirror will be updated/deleted (i.e. no file will have that mirror associated).And about adding... yes, you add things manually, as before. It's just that now you add mirrors and files at different places, and the association of a file with a mirror is a separate third query (an insert at the file_mirrors table; one per a file's mirror).

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...