soporta - optimizar consultas mysql explain

La misma consulta MySQL devuelve resultados diferentes en el mismo servidor

Question

Estoy ejecutando la siguiente consulta. A veces devuelve alrededor de 100 registros, como se esperaba, otras veces devuelve solo un registro con NULL para todos los valores de los campos unidos. Además, explicar arroja un resultado diferente casi siempre.

Aquí está la consulta:

EXPLAIN
SELECT
`fieldreps`.`user_id` AS `fieldreps.user_id`,
`fieldreps`.`availability_id` AS `fieldreps.availability_id`,
`fieldreps`.`applicant_type` AS `fieldreps.applicant_type`,
`fieldreps`.`license_number` AS `fieldreps.license_number`,
`fieldreps`.`license_exp` AS `fieldreps.license_exp`,
`fieldreps`.`license_state` AS `fieldreps.license_state`,
`fieldreps`.`car` AS `fieldreps.car`,
`fieldreps`.`authorized_worker` AS `fieldreps.authorized_worker`,
`fieldreps`.`restrictions` AS `fieldreps.restrictions`,
`fieldreps`.`night` AS `fieldreps.night`,
`fieldreps`.`day` AS `fieldreps.day`,
`fieldreps`.`longer` AS `fieldreps.longer`,
`fieldreps`.`commitment` AS `fieldreps.commitment`,
`fieldreps`.`travel_metro` AS `fieldreps.travel_metro`,
`fieldreps`.`travel_states` AS `fieldreps.travel_states`,
`fieldreps`.`big_employee` AS `fieldreps.big_employee`,
`fieldreps`.`employed` AS `fieldreps.employed`,
`fieldreps`.`retail` AS `fieldreps.retail`,
`fieldreps`.`status` AS `fieldreps.status`,
`fieldreps`.`start` AS `fieldreps.start`,
`fieldreps`.`sales` AS `fieldreps.sales`,
`fieldreps`.`study` AS `fieldreps.study`,
`fieldreps`.`relevant` AS `fieldreps.relevant`,
`fieldreps`.`experience` AS `fieldreps.experience`,
`fieldreps`.`claims` AS `fieldreps.claims`,
`fieldreps`.`education` AS `fieldreps.education`,
`fieldreps`.`degree_details` AS `fieldreps.degree_details`,
`fieldreps`.`degree_institution` AS `fieldreps.degree_institution`,
`fieldreps`.`other_training` AS `fieldreps.other_training`,
`fieldreps`.`jobs` AS `fieldreps.jobs`,
`fieldreps`.`current_training` AS `fieldreps.current_training`,
`fieldreps`.`interested` AS `fieldreps.interested`,
`fieldreps`.`achievements` AS `fieldreps.achievements`,
`fieldreps`.`passions` AS `fieldreps.passions`,
`fieldreps`.`ambitions` AS `fieldreps.ambitions`,
`fieldreps`.`max_travel_time` AS `fieldreps.max_travel_time`,
`fieldreps`.`creation_time` AS `fieldreps.creation_time`,
`fieldreps`.`resume` AS `fieldreps.resume`,
`users`.`user_id` AS `users.user_id`,
`users`.`email` AS `users.email`,
`users`.`name` AS `users.name`,
`users`.`password` AS `users.password`,
`users`.`given_name` AS `users.given_name`,
`users`.`title` AS `users.title`,
`users`.`nationality` AS `users.nationality`,
`tasks`.`task_id` AS `tasks.task_id`,
`tasks`.`recurrence_id` AS `tasks.recurrence_id`,
`tasks`.`parent_id` AS `tasks.parent_id`,
`tasks`.`user_id` AS `tasks.user_id`,
`tasks`.`campaign_id` AS `tasks.campaign_id`,
`tasks`.`location_id` AS `tasks.location_id`,
`tasks`.`title` AS `tasks.title`,
`tasks`.`description` AS `tasks.description`,
`tasks`.`earliest_start` AS `tasks.earliest_start`,
`tasks`.`deadline` AS `tasks.deadline`,
`tasks`.`km_estimate` AS `tasks.km_estimate`,
`tasks`.`estimate` AS `tasks.estimate`,
`datapoints`.`datapoint_id` AS `datapoints.datapoint_id`,
`datapoints`.`task_id` AS `datapoints.task_id`,
`datapoints`.`datatype_id` AS `datapoints.datatype_id`,
`datapoints`.`value` AS `datapoints.value`,
`datapoints`.`product_id` AS `datapoints.product_id`,
`datapoints`.`recorded` AS `datapoints.recorded`,
`datatypes`.`datatype_id` AS `datatypes.datatype_id`,
`datatypes`.`datatype_template_id` AS `datatypes.datatype_template_id`,
`datatypes`.`client_id` AS `datatypes.client_id`,
`datatypes`.`name` AS `datatypes.name`,
`datatypes`.`description` AS `datatypes.description`,
`datatypes`.`options` AS `datatypes.options`,
`locations`.`location_id` AS `locations.location_id`,
`locations`.`retailer_group_id` AS `locations.retailer_group_id`,
`locations`.`latitude` AS `locations.latitude`,
`locations`.`longitude` AS `locations.longitude`,
`locations`.`name` AS `locations.name`,
`locations`.`address` AS `locations.address`,
`locations`.`city` AS `locations.city`,
`locations`.`state` AS `locations.state`,
`locations`.`postal` AS `locations.postal`,
`locations`.`phone` AS `locations.phone`,
`locations`.`suburb` AS `locations.suburb`,
`locations`.`country` AS `locations.country`,
`locations`.`region` AS `locations.region`,
`locations`.`coverage` AS `locations.coverage`,
`locations`.`store_number` AS `locations.store_number`,
`locations`.`store_type` AS `locations.store_type`
FROM `fieldreps` AS `fieldreps`
LEFT JOIN `users` ON (`fieldreps`.`user_id` = `users`.`user_id`)
LEFT JOIN `tasks` ON (`fieldreps`.`user_id` = `tasks`.`user_id`)
LEFT JOIN `datapoints` ON (`tasks`.`task_id` = `datapoints`.`task_id`)
LEFT JOIN `datatypes` ON (`datapoints`.`datatype_id` = `datatypes`.`datatype_id`)
LEFT JOIN `locations` ON (`tasks`.`location_id` = `locations`.`location_id`)
WHERE `fieldreps`.`user_id` LIKE ''5f5dc29c-832e-5646-9dc2-46d774bfb1ec''

Estoy ejecutando la consulta en phpMyAdmin. La base de datos está alojada en xeround.com (¿Podría ser un error en su configuración?)

Estoy perplejo. No estoy seguro de a dónde ir desde aquí.

¡Gracias por adelantado!

ACTUALIZAR:

Aquí está el volcado de phpMyAdmin:

-- phpMyAdmin SQL Dump
-- version 3.4.3.2
-- http://www.phpmyadmin.net
--
-- Generation Time: Jan 07, 2012 at 08:20 PM
-- Server version: 5.1.42
-- PHP Version: 5.3.1
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `big_development`
--
-- --------------------------------------------------------
--
-- Table structure for table `datapoints`
--
CREATE TABLE IF NOT EXISTS `datapoints` (
`datapoint_id` varchar(36) COLLATE utf8_bin NOT NULL,
`task_id` varchar(36) COLLATE utf8_bin NOT NULL,
`datatype_id` varchar(36) COLLATE utf8_bin NOT NULL,
`value` text COLLATE utf8_bin,
`product_id` varchar(36) COLLATE utf8_bin DEFAULT NULL,
`recorded` text COLLATE utf8_bin,
PRIMARY KEY (`datapoint_id`),
KEY `task_id` (`task_id`),
KEY `datatype_id` (`datatype_id`),
KEY `product_id` (`product_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `datatypes`
--
CREATE TABLE IF NOT EXISTS `datatypes` (
`datatype_id` varchar(36) COLLATE utf8_bin NOT NULL,
`datatype_template_id` varchar(36) COLLATE utf8_bin NOT NULL,
`client_id` varchar(36) COLLATE utf8_bin NOT NULL,
`name` text COLLATE utf8_bin,
`description` text COLLATE utf8_bin,
`options` text COLLATE utf8_bin,
PRIMARY KEY (`datatype_id`),
KEY `datatype_template_id` (`datatype_template_id`),
KEY `client_id` (`client_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `fieldreps`
--
CREATE TABLE IF NOT EXISTS `fieldreps` (
`user_id` varchar(36) COLLATE utf8_bin NOT NULL,
`availability_id` varchar(36) COLLATE utf8_bin NOT NULL,
`applicant_type` text COLLATE utf8_bin NOT NULL,
`license_number` text COLLATE utf8_bin NOT NULL,
`license_exp` text COLLATE utf8_bin,
`license_state` text COLLATE utf8_bin,
`car` text COLLATE utf8_bin NOT NULL,
`authorized_worker` text COLLATE utf8_bin NOT NULL,
`restrictions` text COLLATE utf8_bin NOT NULL,
`night` text COLLATE utf8_bin NOT NULL,
`day` text COLLATE utf8_bin NOT NULL,
`longer` text COLLATE utf8_bin NOT NULL,
`commitment` text COLLATE utf8_bin NOT NULL,
`travel_metro` text COLLATE utf8_bin NOT NULL,
`travel_states` text COLLATE utf8_bin NOT NULL,
`big_employee` text COLLATE utf8_bin NOT NULL,
`employed` text COLLATE utf8_bin NOT NULL,
`retail` text COLLATE utf8_bin NOT NULL,
`status` text COLLATE utf8_bin NOT NULL,
`start` text COLLATE utf8_bin,
`sales` text COLLATE utf8_bin NOT NULL,
`study` text COLLATE utf8_bin NOT NULL,
`relevant` text COLLATE utf8_bin NOT NULL,
`experience` text COLLATE utf8_bin NOT NULL,
`claims` text COLLATE utf8_bin NOT NULL,
`education` text COLLATE utf8_bin NOT NULL,
`degree_details` text COLLATE utf8_bin NOT NULL,
`degree_institution` text COLLATE utf8_bin NOT NULL,
`other_training` text COLLATE utf8_bin NOT NULL,
`jobs` text COLLATE utf8_bin NOT NULL,
`current_training` text COLLATE utf8_bin NOT NULL,
`interested` text COLLATE utf8_bin NOT NULL,
`achievements` text COLLATE utf8_bin NOT NULL,
`passions` text COLLATE utf8_bin NOT NULL,
`ambitions` text COLLATE utf8_bin NOT NULL,
`max_travel_time` text COLLATE utf8_bin NOT NULL,
`creation_time` text COLLATE utf8_bin,
`resume` text COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`user_id`),
KEY `availability_id` (`availability_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `locations`
--
CREATE TABLE IF NOT EXISTS `locations` (
`location_id` varchar(36) COLLATE utf8_bin NOT NULL,
`retailer_group_id` varchar(36) COLLATE utf8_bin NOT NULL,
`latitude` text COLLATE utf8_bin,
`longitude` text COLLATE utf8_bin,
`name` text COLLATE utf8_bin,
`address` text COLLATE utf8_bin,
`city` text COLLATE utf8_bin,
`state` text COLLATE utf8_bin,
`postal` text COLLATE utf8_bin,
`phone` text COLLATE utf8_bin,
`suburb` text COLLATE utf8_bin,
`country` text COLLATE utf8_bin,
`region` text COLLATE utf8_bin,
`coverage` tinyint(1) DEFAULT NULL,
`store_number` text COLLATE utf8_bin,
`store_type` text COLLATE utf8_bin,
PRIMARY KEY (`location_id`),
KEY `retailer_group_id` (`retailer_group_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `tasks`
--
CREATE TABLE IF NOT EXISTS `tasks` (
`task_id` varchar(36) COLLATE utf8_bin NOT NULL,
`recurrence_id` varchar(36) COLLATE utf8_bin NOT NULL,
`parent_id` varchar(36) COLLATE utf8_bin DEFAULT NULL,
`user_id` varchar(36) COLLATE utf8_bin NOT NULL,
`campaign_id` varchar(36) COLLATE utf8_bin NOT NULL,
`location_id` varchar(36) COLLATE utf8_bin NOT NULL,
`title` text COLLATE utf8_bin,
`description` text COLLATE utf8_bin,
`earliest_start` text COLLATE utf8_bin,
`deadline` text COLLATE utf8_bin,
`km_estimate` text COLLATE utf8_bin,
`estimate` int(11) DEFAULT NULL,
PRIMARY KEY (`task_id`),
KEY `campaign_id` (`campaign_id`),
KEY `location_id` (`location_id`),
KEY `user_id` (`user_id`),
KEY `parent_id` (`parent_id`),
KEY `recurrence_id` (`recurrence_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`user_id` varchar(36) COLLATE utf8_bin NOT NULL,
`email` varchar(255) COLLATE utf8_bin NOT NULL,
`name` text COLLATE utf8_bin,
`password` varchar(255) COLLATE utf8_bin NOT NULL,
`given_name` text COLLATE utf8_bin,
`title` text COLLATE utf8_bin NOT NULL,
`nationality` text COLLATE utf8_bin,
PRIMARY KEY (`user_id`),
KEY `user_id` (`user_id`)
) ENGINE=Xeround DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

EDITAR:

Si elimino la unión en todas las tablas pero el usuario, la consulta funciona siempre. Sin embargo, tan pronto como agregue el join en las tareas, se rompe. Por ejemplo, esta consulta la divide de la misma manera que la primera consulta que publiqué:

SELECT
`fieldreps`.`user_id` AS `fieldreps.user_id`,
`fieldreps`.`availability_id` AS `fieldreps.availability_id`,
`fieldreps`.`applicant_type` AS `fieldreps.applicant_type`,
`fieldreps`.`license_number` AS `fieldreps.license_number`,
`fieldreps`.`license_exp` AS `fieldreps.license_exp`,
`fieldreps`.`license_state` AS `fieldreps.license_state`,
`fieldreps`.`car` AS `fieldreps.car`,
`fieldreps`.`authorized_worker` AS `fieldreps.authorized_worker`,
`fieldreps`.`restrictions` AS `fieldreps.restrictions`,
`fieldreps`.`night` AS `fieldreps.night`,
`fieldreps`.`day` AS `fieldreps.day`,
`fieldreps`.`longer` AS `fieldreps.longer`,
`fieldreps`.`commitment` AS `fieldreps.commitment`,
`fieldreps`.`travel_metro` AS `fieldreps.travel_metro`,
`fieldreps`.`travel_states` AS `fieldreps.travel_states`,
`fieldreps`.`big_employee` AS `fieldreps.big_employee`,
`fieldreps`.`employed` AS `fieldreps.employed`,
`fieldreps`.`retail` AS `fieldreps.retail`,
`fieldreps`.`status` AS `fieldreps.status`,
`fieldreps`.`start` AS `fieldreps.start`,
`fieldreps`.`sales` AS `fieldreps.sales`,
`fieldreps`.`study` AS `fieldreps.study`,
`fieldreps`.`relevant` AS `fieldreps.relevant`,
`fieldreps`.`experience` AS `fieldreps.experience`,
`fieldreps`.`claims` AS `fieldreps.claims`,
`fieldreps`.`education` AS `fieldreps.education`,
`fieldreps`.`degree_details` AS `fieldreps.degree_details`,
`fieldreps`.`degree_institution` AS `fieldreps.degree_institution`,
`fieldreps`.`other_training` AS `fieldreps.other_training`,
`fieldreps`.`jobs` AS `fieldreps.jobs`,
`fieldreps`.`current_training` AS `fieldreps.current_training`,
`fieldreps`.`interested` AS `fieldreps.interested`,
`fieldreps`.`achievements` AS `fieldreps.achievements`,
`fieldreps`.`passions` AS `fieldreps.passions`,
`fieldreps`.`ambitions` AS `fieldreps.ambitions`,
`fieldreps`.`max_travel_time` AS `fieldreps.max_travel_time`,
`fieldreps`.`creation_time` AS `fieldreps.creation_time`,
`fieldreps`.`resume` AS `fieldreps.resume`,
`users`.`user_id` AS `users.user_id`,
`users`.`email` AS `users.email`,
`users`.`name` AS `users.name`,
`users`.`password` AS `users.password`,
`users`.`given_name` AS `users.given_name`,
`users`.`title` AS `users.title`,
`users`.`nationality` AS `users.nationality`,
`tasks`.`task_id` AS `tasks.task_id`,
`tasks`.`recurrence_id` AS `tasks.recurrence_id`,
`tasks`.`parent_id` AS `tasks.parent_id`,
`tasks`.`user_id` AS `tasks.user_id`,
`tasks`.`campaign_id` AS `tasks.campaign_id`,
`tasks`.`location_id` AS `tasks.location_id`,
`tasks`.`title` AS `tasks.title`,
`tasks`.`description` AS `tasks.description`,
`tasks`.`earliest_start` AS `tasks.earliest_start`,
`tasks`.`deadline` AS `tasks.deadline`,
`tasks`.`km_estimate` AS `tasks.km_estimate`,
`tasks`.`estimate` AS `tasks.estimate`
FROM `fieldreps` AS `fieldreps`
LEFT JOIN `users` ON (`fieldreps`.`user_id` = `users`.`user_id`)
LEFT JOIN `tasks` ON (`fieldreps`.`user_id` = `tasks`.`user_id`)
WHERE `fieldreps`.`user_id` LIKE ''5f5dc29c-832e-5646-9dc2-46d774bfb1ec''

Answer 1

La conexión y la recopilación de la base de datos difieren.