temp table or query for API
Posted by: Stephen Strickland
Date: May 31, 2013 06:16AM

I have a mysql database that has an API ran in PHP that accesses the database via API. Currently I have a query in the API that combines multiple tables in the database and returns a response in a JSON format.

Would it be faster or better to setup a temp table or stored procedure from the query in the API and then have the API just filter the table? This RESTful API is being accessed through multiple clients around the world. Currently it is being accessed by API around 40-60 times a day, but there are changes that are happening that could increase this to 100's of times an hour.

The response from the API can be seen at the following.

www.serviidb.com/api/video
www.serviidb.com/api/media
www.serviidb.com/api/plugin

The current documentation can be seen at
www.serviidb.com/sites/default/files/Serviidb API_0.pdf

Here is an example of the query that the PHP query.


SELECT distinct node.title as "name", regions.region as region, field_data_field_media_url.field_media_url_value as url, media_type.media_type as mediaType, repositoryType.repositoryType as resourceType, plugins.plugin, media_resource_language.language, node.nid, resolution.resolution, (field_data_field_quality.field_quality_rating/20) as quality, (field_data_field_reliability.field_reliability_rating/20) as reliability, field_data_field_installs.field_installs_value as installCount FROM node left join field_data_field_region on field_data_field_region.revision_id=node.vid left join regions on regions.tid=field_data_field_region.field_region_tid left join field_data_field_media_url on field_data_field_media_url.revision_id = node.vid left join field_data_field_required_plugin on field_data_field_required_plugin.revision_id=node.vid left join field_data_field_source on field_data_field_source.revision_id = node.vid left join repositoryType on repositoryType.tid=field_data_field_source.field_source_tid left join plugins on plugins.tid=field_data_field_required_plugin.field_required_plugin_tid left join field_data_field_media_type on field_data_field_media_type.revision_id = node.vid left join media_type on media_type.tid = field_data_field_media_type.field_media_type_tid left join field_data_field_language on field_data_field_language.revision_id=node.vid left join media_resource_language on media_resource_language.tid= field_data_field_language.field_language_tid left join field_data_field_resolution on field_data_field_resolution.revision_id=node.vid left join resolution on resolution.tid=field_data_field_resolution.field_resolution_tid left join field_data_field_quality on field_data_field_quality.revision_id=node.vid left join field_data_field_reliability on field_data_field_reliability.revision_id=node.vid left join field_data_field_installs on field_data_field_installs.revision_id=node.vid WHERE node.status<>0 and node.type="media" and media_type.media_type ="video"

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.