Re: temp table or query for API
Posted by: Stephen Strickland
Date: June 01, 2013 01:32PM

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.

Options: ReplyQuote




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.