Re: prepared statement query takes 3s for a few hundred lines - query issue

[Michael W.]

Thanks for picking up my issue

Sorry for being unable to format it as code:

SHOW TABLE STATUS

Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| airports | InnoDB | 10 | Compact | 1367 | 107 | 147456 | 0 | 49152 | 10485760 | 1350 | 2015-11-02 14:36:14 | NULL | NULL | utf8_unicode_ci | NULL | | |
| airspace_grid | InnoDB | 10 | Compact | 66868 | 39 | 2637824 | 0 | 3178496 | 10485760 | 112634 | 2015-11-02 14:36:41 | NULL | NULL | utf8_general_ci | NULL | | |
| airspace_points | InnoDB | 10 | Compact | 66703 | 70 | 4734976 | 0 | 1589248 | 10485760 | 112634 | 2015-11-02 14:36:52 | NULL | NULL | utf8_unicode_ci | NULL | | |
| airspaces | InnoDB | 10 | Compact | 448 | 146 | 65536 | 0 | 0 | 10485760 | NULL | 2015-11-02 14:37:01 | NULL | NULL | utf8_unicode_ci | NULL | | |
| migrations | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 0 | 10485760 | NULL | 2015-11-02 12:48:09 | NULL | NULL | utf8_unicode_ci | NULL | | |
| password_resets | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 10485760 | NULL | 2015-11-02 12:48:09 | NULL | NULL | utf8_unicode_ci | NULL | | |
| users | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 10485760 | 1 | 2015-11-02 12:48:09 | NULL | NULL | utf8_unicode_ci


+-------------------+
| Tables_in_flyzone |
+-------------------+
| airports |
| airspace_grid |
| airspace_points |
| airspaces |
| migrations |
| password_resets |
| users |
+-------------------+

| airports | CREATE TABLE `airports` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`icao` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
`type` enum('AD_CLOSED','AD_MIL','AF_CIVIL','AF_MIL_CIVIL','AF_WATER','APT','GLIDING','HELI_CIVIL','HELI_MIL','INTL_APT','LIGHT_AIRCRAFT') COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double(17,14) NOT NULL,
`longitude` double(16,14) NOT NULL,
`elevation` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`elevation_unit` enum('M') COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`grid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `grid` (`grid`)
) ENGINE=InnoDB AUTO_INCREMENT=1350 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

| airspace_grid | CREATE TABLE `airspace_grid` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`airspace_id` int(10) unsigned NOT NULL,
`grid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `airspace_id` (`airspace_id`),
KEY `grid` (`grid`),
CONSTRAINT `airspace_grid_ibfk_1` FOREIGN KEY (`airspace_id`) REFERENCES `airspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=112634 DEFAULT CHARSET=utf8 |

| airspace_points | CREATE TABLE `airspace_points` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`airspace_id` int(10) unsigned NOT NULL,
`type` enum('point','arc','circle') COLLATE utf8_unicode_ci NOT NULL,
`latitude` double(17,14) NOT NULL,
`longitude` double(16,14) NOT NULL,
`bearing_start` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`bearing_end` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`radius` int(10) unsigned DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `airspace_points_airspace_id_foreign` (`airspace_id`),
CONSTRAINT `airspace_points_airspace_id_foreign` FOREIGN KEY (`airspace_id`) REFERENCES `airspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=112634 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

| airspaces | CREATE TABLE `airspaces` (
`id` int(10) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`alt_bottom` int(11) NOT NULL,
`alt_bottom_ref` enum('GND','MSL','STD') COLLATE utf8_unicode_ci NOT NULL,
`alt_bottom_unit` enum('F','FL') COLLATE utf8_unicode_ci NOT NULL,
`alt_top` int(11) NOT NULL,
`alt_top_ref` enum('GND','MSL','STD') COLLATE utf8_unicode_ci NOT NULL,
`alt_top_unit` enum('F','FL') COLLATE utf8_unicode_ci NOT NULL,
`category` enum('A','B','C','CTR','D','DANGER','E','F','G','GLIDING','OTH','RESTRICTED','TMA','TMZ','WAVE','PROHIBITED','FIR','UIR','RMZ') COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
`geometry` enum('POLYGON','CIRCLE') COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |


EXPLAIN:
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | airports | ALL | NULL | NULL | NULL | NULL | 1333 | |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

+----+-------------+---------------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+-------+-------+
| 1 | SIMPLE | airspace_grid | ALL | NULL | NULL | NULL | NULL | 66868 | |
+----+-------------+---------------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)

+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------+
| 1 | SIMPLE | airspace_points | ALL | NULL | NULL | NULL | NULL | 66703 | |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)


+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | airspaces | ALL | NULL | NULL | NULL | NULL | 361 | |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Now the entire database should be explained. I didn't quote password and user tables.

Best,

Michael