MySQL Forums
Forum List  »  Optimizer & Parser

Re: prepared statement query takes 3s for a few hundred lines - query issue
Posted by: Michael W.
Date: December 01, 2015 07:10AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: prepared statement query takes 3s for a few hundred lines - query issue
948
December 01, 2015 07:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.