SHOW CREATE TABLE
| field_data_field_name | CREATE TABLE `field_data_field_name` (
`entity_type` varchar(128) NOT NULL DEFAULT '' COMMENT 'The entity type this data is attached to',
`bundle` varchar(128) NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
`deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
`entity_id` int(10) unsigned NOT NULL COMMENT 'The entity id this data is attached to',
`revision_id` int(10) unsigned DEFAULT NULL COMMENT 'The entity revision id this data is attached to, or NULL if the entity type is not versioned',
`language` varchar(32) NOT NULL DEFAULT '' COMMENT 'The language for this data item.',
`delta` int(10) unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
`field_name_fid` int(10) unsigned DEFAULT NULL COMMENT 'The file_managed.fid being referenced in this field.',
`field_name_display` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT 'Flag to control whether this file should be displayed when viewing content.',
`field_name_description` text COMMENT 'A description of the file.',
PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
KEY `entity_type` (`entity_type`),
KEY `bundle` (`bundle`),
KEY `deleted` (`deleted`),
KEY `entity_id` (`entity_id`),
KEY `revision_id` (`revision_id`),
KEY `language` (`language`),
KEY `field_name_fid` (`field_name_fid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 19 (field_name)' |
EXPLAIN
+----+-------------+----------------------------------+--------+----------------------------------+----------------------+---------+---------------------------------------------------------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------------+--------+----------------------------------+----------------------+---------+---------------------------------------------------------+------+-------------------------------------------+
| 1 | PRIMARY | <derived5> | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using temporary |
| 1 | PRIMARY | field_data_field_media_type | ref | revision_id,field_media_type_tid | field_media_type_tid | 5 | media_type.tid | 187 | Using where |
| 1 | PRIMARY | node | ref | vid,node_status_type,node_type | vid | 5 | drupal.field_data_field_media_type.revision_id | 1 | Using where |
| 1 | PRIMARY | field_data_field_region | ref | revision_id | revision_id | 5 | drupal.node.vid | 1 | |
| 1 | PRIMARY | field_data_field_language | ref | revision_id | revision_id | 5 | drupal.field_data_field_media_type.revision_id | 1 | |
| 1 | PRIMARY | <derived6> | ALL | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | field_data_field_resolution | ref | revision_id | revision_id | 5 | drupal.field_data_field_media_type.revision_id | 1 | |
| 1 | PRIMARY | <derived7> | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | field_data_field_quality | ref | revision_id | revision_id | 5 | drupal.node.vid | 1 | |
| 1 | PRIMARY | field_data_field_reliability | ref | revision_id | revision_id | 5 | drupal.field_data_field_media_type.revision_id | 1 | |
| 1 | PRIMARY | field_data_field_installs | ref | revision_id | revision_id | 5 | drupal.field_data_field_media_type.revision_id | 1 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 7 | |
| 1 | PRIMARY | field_data_field_media_url | ref | revision_id | revision_id | 5 | drupal.field_data_field_media_type.revision_id | 1 | |
| 1 | PRIMARY | field_data_field_required_plugin | ref | revision_id | revision_id | 5 | drupal.field_data_field_media_type.revision_id | 1 | |
| 1 | PRIMARY | field_data_field_source | ref | revision_id | revision_id | 5 | drupal.field_data_field_media_type.revision_id | 1 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 98 | |
| 7 | DERIVED | taxonomy_vocabulary | index | PRIMARY | list | 771 | NULL | 21 | Using where; Using index; Using temporary |
| 7 | DERIVED | taxonomy_term_data | ref | taxonomy_tree,vid_name | taxonomy_tree | 4 | drupal.taxonomy_vocabulary.vid | 4 | Using index |
| 7 | DERIVED | field_data_field_resolution | ref | field_resolution_tid | field_resolution_tid | 5 | drupal.taxonomy_term_data.tid | 193 | Using index |
| 6 | DERIVED | field_data_field_language | index | field_language_tid | field_language_tid | 5 | NULL | 662 | Using where; Using index; Using temporary |
| 6 | DERIVED | taxonomy_term_data | eq_ref | PRIMARY,taxonomy_tree,vid_name | PRIMARY | 4 | drupal.field_data_field_language.field_language_tid | 1 | |
| 6 | DERIVED | taxonomy_vocabulary | eq_ref | PRIMARY | PRIMARY | 4 | drupal.taxonomy_term_data.vid | 1 | Using where |
| 5 | DERIVED | node | ref | vid,node_type | node_type | 14 | | 555 | Using where; Using temporary |
| 5 | DERIVED | field_data_field_media_type | ref | revision_id,field_media_type_tid | revision_id | 5 | drupal.node.vid | 1 | Using where |
| 5 | DERIVED | taxonomy_term_data | eq_ref | PRIMARY | PRIMARY | 4 | drupal.field_data_field_media_type.field_media_type_tid | 1 | |
| 4 | DERIVED | field_data_field_plugins | ALL | bundle,field_plugins_tid | NULL | NULL | NULL | 44 | Using where |
| 4 | DERIVED | taxonomy_term_data | eq_ref | PRIMARY | PRIMARY | 4 | drupal.field_data_field_plugins.field_plugins_tid | 1 | |
| 4 | DERIVED | file_usage | index | NULL | fid_count | 8 | NULL | 126 | Using index |
| 4 | DERIVED | file_managed | eq_ref | PRIMARY | PRIMARY | 4 | drupal.file_usage.fid | 1 | |
| 4 | DERIVED | node | ref | vid | vid | 5 | drupal.field_data_field_plugins.revision_id | 1 | Using index |
| 3 | DERIVED | field_data_field_source | ALL | bundle,field_source_tid | NULL | NULL | NULL | 502 | Using where; Using temporary |
| 3 | DERIVED | taxonomy_term_data | eq_ref | PRIMARY | PRIMARY | 4 | drupal.field_data_field_source.field_source_tid | 1 | |
| 2 | DERIVED | taxonomy_vocabulary | index | PRIMARY | list | 771 | NULL | 21 | Using where; Using index; Using temporary |
| 2 | DERIVED | taxonomy_term_data | ref | taxonomy_tree,vid_name | taxonomy_tree | 4 | drupal.taxonomy_vocabulary.vid | 4 | Using index |
| 2 | DERIVED | field_data_field_resolution | ref | field_resolution_tid | field_resolution_tid | 5 | drupal.taxonomy_term_data.tid | 193 | Using index |
+----+-------------+----------------------------------+--------+----------------------------------+----------------------+---------+---------------------------------------------------------+------+-------------------------------------------+
36 rows in set (0.02 sec)
"API Response Time" is the time it takes the RESTful PHP API to run. Using PHP microtime_float. I get the time the API is called and then when it finishes and use it to calculate the run time.
I am looking at other ways of allowing web site visitors to edit the data. I picked Drupal cause it is easy to setup the visual web site. But it is hard with the way that the fields are set in seperate tables.
I have looked at cakephp and some mysql web front ends. Any advice would be greatly appreciated. Had to split post in multiple posts due to length.