The database is created by Drupal. Drupal uses a new table for each field. I have to use the left joins to get all the fields back together in one array. I have actually already setup 3 temp tables to simplify the query alot.
"on field_data_field_xx.revision_id=node.vid" is what drupal uses like a primary key between the different tables. Each field has the revision_id and it linked back to the node.vid (content version id)
If you go to the below link you can see what the "node" is.
http://serviidb.com/content/amazing-race
Here is a cleaned up formatted version of the query.
SELECT DISTINCT
node.title AS "name",
regions.region AS region,
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,
(quality.field_quality_rating / 20) AS quality,
(reliability.field_reliability_rating / 20) AS reliability,
installs.field_installs_value AS installCount
FROM node
LEFT JOIN region ON region.revision_id = node.vid
LEFT JOIN regions ON regions.tid = region.field_region_tid
LEFT JOIN media_url ON media_url.revision_id = node.vid
LEFT JOIN required_plugin ON required_plugin.revision_id = node.vid
LEFT JOIN source ON source.revision_id = node.vid
LEFT JOIN repositoryType ON repositoryType.tid = source.field_source_tid
LEFT JOIN plugins ON plugins.tid = required_plugin.field_required_plugin_tid
LEFT JOIN media_type ON media_type.revision_id = node.vid
LEFT JOIN media_type ON media_type.tid = media_type.field_media_type_tid
LEFT JOIN language ON language.revision_id = node.vid
LEFT JOIN media_resource_language ON media_resource_language.tid = language.field_language_tid
LEFT JOIN resolution ON resolution.revision_id = node.vid
LEFT JOIN resolution ON resolution.tid = resolution.field_resolution_tid
LEFT JOIN quality ON quality.revision_id = node.vid
LEFT JOIN reliability ON reliability.revision_id = node.vid
LEFT JOIN installs ON installs.revision_id = node.vid
WHERE node.status <> 0
AND node.type = "media"
AND media_type.media_type = "video"
Here are a couple of graphs from Munin showing slow queries information.
http://serviidb.com/sites/default/files/mysql_slowqueries-day.png
http://serviidb.com/sites/default/files/mysql_slowqueries-week.png
Thanks for your help with this.
