SELECT A.*
FROM default_relations_users A
LEFT JOIN default_relations_users B
ON A.id_user_rq = B.id_user_ap
AND A.id_user_ap = B.id_user_rq
WHERE B.id_user_rq IS NULL;
I loaded you sample data (I added an additional index)
mysql> use Reynierpm
Database changed
mysql> DROP TABLE IF EXISTS `default_relations_users`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `default_relations_users` (
-> `id_user_rq` int(11) NOT NULL,
-> `id_user_ap` int(11) NOT NULL,
-> UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`),
-> UNIQUE KEY `rusers_ap_eq_idx` (`id_user_ap`,`id_user_rq`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
-> (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from default_relations_users;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
+------------+------------+
6 rows in set (0.00 sec)
mysql>
Here is the result of my answer
mysql> SELECT A.*
-> FROM default_relations_users A
-> LEFT JOIN default_relations_users B
-> ON A.id_user_rq = B.id_user_ap
-> AND A.id_user_ap = B.id_user_rq
-> WHERE B.id_user_rq IS NULL;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
| 1 | 3 |
| 1 | 4 |
| 2 | 3 |
+------------+------------+
3 rows in set (0.00 sec)
mysql>
Give it a Try !!!
mysql> SELECT A.*
-> FROM default_relations_users A
-> LEFT JOIN default_relations_users B
-> ON A.id_user_rq = B.id_user_ap
-> AND A.id_user_ap = B.id_user_rq
-> WHERE B.id_user_rq IS NULL
-> AND A.id_user_rq = 1;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
| 1 | 3 |
| 1 | 4 |
+------------+------------+
2 rows in set (0.01 sec)
mysql>
I suppose now tuples did not fit any free space on pages, so each row had to relocate. Assuming most if not all new rows only grown in size, around half rows needed new location, and so table is taking 50% more on disk. Why half of rows?.. Because space released by two old tuples is enough for one new tuple only. I think tuple tetris did not help here, because rows were moved not two closest together, but randomly, so new tuples could not effectively use space released...
If this assumption is correct, the space your table takes is half empty. Alas it is reserved for future operations. You need vacuum full table_name
to basically rebuild it to return space... Or just work with current size, day after day (or even year after year ) the size will drop a bit if you keep on updating, deleting...
Best Answer
The folling query will provide one column, where each column name of the respective table is displayed in a separate row. The order seems random.