Импорт из MySQL в PostgreSQL

mysql> explain SELECT videos.* FROM videos ORDER BY created_on DESC LIMIT 0, 30;
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | videos | ALL  | NULL          | NULL | NULL    | NULL | 1782405 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.03 sec)

mysql> explain SELECT videos.* FROM videos ORDER BY id DESC LIMIT 0, 30;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | videos | index | NULL          | PRIMARY | 8       | NULL |   30 |       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql>

CREATE TABLE `videos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `site` varchar(255) NOT NULL,
  `site_id` bigint(20) unsigned NOT NULL,
  `site_url` varchar(255) NOT NULL,
  `title` text NOT NULL,
  `thumbnails` text NOT NULL,
  `duration_txt` varchar(32) NOT NULL,
  `duration` bigint(20) unsigned NOT NULL,
  `embed` text NOT NULL,
  `created_on` datetime NOT NULL,
  `status` enum('active','deleted') NOT NULL DEFAULT 'active',
  `views` bigint(20) unsigned NOT NULL DEFAULT '0',
  `rating` bigint(20) unsigned NOT NULL DEFAULT '0',
  `voted` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `site` (`site`),
  KEY `site_id` (`site_id`),
  KEY `site_url` (`site_url`),
  KEY `status` (`status`),
  KEY `id_created_on` (`id`,`created_on`),
  KEY `created_on_id` (`created_on`,`id`),
  KEY `in1` (`status`,`created_on`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1797527 DEFAULT CHARSET=utf8;

mysql> explain SELECT videos.* FROM videos WHERE status = 'active' ORDER BY created_on DESC, id DESC LIMIT 1782420, 30;
+----+-------------+--------+------+---------------+------+---------+-------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+-------+---------+-------------+
|  1 | SIMPLE      | videos | ref  | status,in1    | in1  | 1       | const | 1782455 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+---------+-------------+

The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET can be inefficient.

Note

Fixed-length row format is only available for tables without BLOB or TEXT columns. Creating a table with these columns with an explicit ROW_FORMAT clause will not raise an error or warning; the format specification will be ignored.