Create a mysql row with non-unique keys based on some other rows

Question

Example database:

| country | animal | size | x_id* | |---------+--------+--------+-------| | 777 | 1001 | small | 1 | | 777 | 2002 | medium | 2 | | 777 | 7007 | medium | 3 | | 777 | 7007 | large | 4 | | 42 | 1001 | small | 1 | | 42 | 2002 | medium | 2 | | 42 | 7007 | large | 4 | 

I need to generate x_id continuously based on entries in (animal, size), and if x_id for x_id combination exist, use it again.

I am currently using the following PHP script for this action, but on a large db table it is very slow.

 query("UPDATE myTable SET x_id = -1"); $i = $j; $c = array(); $q = query(" SELECT animal, size FROM myTable WHERE x_id = -1 GROUP BY animal, size"); while($r = fetch_array($q)) { $hk = $r['animal'] . '-' . $r['size']; if( !isset( $c[$hk] ) ) $c[$hk] = $i++; query(" UPDATE myTable SET x_id = {$c[$hk]} WHERE animal = '".$r['animal']."' AND size = '".$r['size']."' AND x_id = -1"); } 

Is there a way to convert a PHP script into one or two mysql commands?

edit:

 CREATE TABLE `myTable` ( `country` int(10) unsigned NOT NULL DEFAULT '1', -- country `animal` int(3) NOT NULL, `size` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `lang_id` tinyint(4) NOT NULL DEFAULT '1', `x_id` int(10) NOT NULL, KEY `country` (`country`), KEY `x_id` (`x_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

Answer 1

 UPDATE myTable m JOIN ( SELECT animal, size, @newid := @newid + 1 AS x_id FROM myTable a CROSS JOIN (SELECT @newid := 0) b WHERE x_id = -1 GROUP BY animal, size ) t ON m.animal = t.animal AND m.size = t.size SET m.x_id = t.x_id ; 

http://sqlfiddle.com/#!9/5525ba/1

The group in the subquery is not needed. This creates unnecessary overhead. If it's fast enough, leave it like that, otherwise we can use a separate + different subquery.

Answer 2

This is conceptual. Worm it in your world if it is useful.

Scheme

 create table AnimalSize ( id int auto_increment primary key, animal varchar(100) not null, size varchar(100) not null, unique key(animal,size) -- this is critical, no dupes ); create table CountryAnimalSize ( id int auto_increment primary key, country varchar(100) not null, animal varchar(100) not null, size varchar(100) not null, xid int not null -- USE THE id achieved thru use of AnimalSize table ); 

Some queries

 -- truncate table animalsize; -- clobber and reset auto_increment back to 1 insert ignore AnimalSize(animal,size) values ('snake','small'); -- id=1 select last_insert_id(); -- 1 insert ignore AnimalSize(animal,size) values ('snake','small'); -- no real insert but creates id GAP (ie blows slot 2) select last_insert_id(); -- 1 insert ignore AnimalSize(animal,size) values ('snake','small'); -- no real insert but creates id GAP (ie blows slot 3) select last_insert_id(); -- 1 insert ignore AnimalSize(animal,size) values ('frog','medium'); -- id=4 select last_insert_id(); -- 4 insert ignore AnimalSize(animal,size) values ('snake','small'); -- no real insert but creates id GAP (ie blows slot 3) select last_insert_id(); -- 4 

Note: insert ignore says it does this, and ignores the fact that it could die. In our case, this may fail due to a unique key (which is good). In general, do not use insert ignore unless you know what you are doing.

This is often talked about in conjunction with the insert on duplicate key update (IODKU) call. Or should I think about how, as in, How can I solve this current predicament. But that (IODKU) will be stretched in this case. However, keep both in your solution tool.

After the insert ignores fire, you know, anyway, that the line is there.

Forgetting the INNODB GAP aspect, the above assumes that if the string already exists before ignoring the insert, then

You cannot rely on last_insert_id() for id

So, after disabling ignoring the insert, go and enter the identifier, which, as you know, should be there. Use this in subsequent calls against CountryAnimalSize

continue this line of reasoning for the tables in the CountryAnimalSize table where the row may or may not be there.

There is no reason to formalize the solution here, because, as you say, these are not even your tables in question.

Also, return to INNODB GAP . Google it. Find out if you can live with the spaces created.

Most people have large fish to fry, while maintaining tight and alkaline clothes.

Other people (read: OCD) are so obsessed with the perceived burst problem that they explode on it for days.

So, these are general comments intended to help a wider audience than the answers to your question, which, as you say, is not even your outline.

Answer 3

Custom variables are inconvenient, but should do the trick tested on my machine.

 CREATE TABLE t ( animal VARCHAR(20), size VARCHAR(20), x_id INT); INSERT INTO T(animal,size) VALUES('crocodile','small'), ('elephant','medium'), ('giraffe','medium'), ('giraffe','large'), ('crocodile','small'), ('elephant','medium'), ('giraffe','large'); UPDATE t RIGHT JOIN (SELECT animal,size, MIN(CASE WHEN @var:=CONCAT(animal,size) THEN @id ELSE @id: =@id +1 END)id FROM t, (SELECT @var:=CONCAT(animal,size) FROM t)x , (SELECT @id:=0)y GROUP BY animal,size)q ON t.animal=q.animal AND t.size=q.size SET x_id=q.id 

results

 "animal" "size" "x_id" "crocodile" "small" "1" "elephant" "medium" "2" "giraffe" "medium" "3" "giraffe" "large" "4" "crocodile" "small" "1" "elephant" "medium" "2" "giraffe" "large" "4" 

You want these indexes to add for (much) faster access

 ALTER TABLE `yourtable` ADD INDEX `as_idx` (`animal`,`size`); ALTER TABLE `yourtable` ADD INDEX `id_idx` (`x_id`); 

Answer 4

You can use x_id like this:

 CONCAT(`animal`, '_', `size`) AS `x_id` 

And then compare it with x_id so you get something like:

 +---------+-----------+--------+------------------+ | country | animal | size | x_id* | +---------+-----------+--------+------------------+ | africa | crocodile | small | crocodile_small | | africa | elephant | medium | elephant_medium | | africa | giraffe | medium | giraffe_medium | | africa | giraffe | large | giraffe_large | | europe | crocodile | small | crocodile_small | | europe | elephant | medium | elephant_medium | | europe | giraffe | large | giraffe_large | +---------+-----------+--------+------------------+ 

Answer 5

As I can see, you are already using the MyISAM engine type, you can simply define both the country field and x_id as the PRIMARY KEY (shared), and you can set the AUTO_INCREMENT field for x_id . Now MySQL will do the rest for you! BINGO!

Here is the SQL Fiddle for you!

 CREATE TABLE `myTable` ( `country` int(10) unsigned NOT NULL DEFAULT '1', -- country `animal` int(4) NOT NULL, `size` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `lang_id` tinyint(4) NOT NULL DEFAULT '1', `x_id` int(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (country,x_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `myTable` (`country`, `animal`, `size`) VALUES (777, 1001, 'small'), (777, 2002, 'medium'), (777, 7007, 'medium'), (777, 7007, 'large'), (42, 1001, 'small'), (42, 2002, 'medium'), (42, 7007, 'large') 

The result will be like this:

 | country | animal | size |lang_id | x_id | |---------+--------+--------+--------+-------| | 777 | 1001 | small | 1 | 1 | | 777 | 2002 | medium | 1 | 2 | | 777 | 7007 | medium | 1 | 3 | | 777 | 7007 | large | 1 | 4 | | 42 | 1001 | small | 1 | 1 | | 42 | 2002 | medium | 1 | 2 | | 42 | 7007 | large | 1 | 4 | 

NOTE. This will only work for MyISAM and BDB tables, for other types of engines you will receive the error message "Incorrect table definition: there can be only one automatic column, and it must be defined as a key!" See this answer for more details: fooobar.com/questions/198691 / ....