Mysql query execution time is varying in different environments

How are you measuring it?  And what do you mean by 'environment'?  If it is two different computers then it could simply be that one is slower than the other.  Or has a slower network connection.  Or is simply busier.

different environments means one is local environment and another one is production environment.
these two databases running in two different servers.
How to identify network is slow or busy?
so there is no problem with query correct?
because same query I am executing
local environment ----->0.02 secs it is taking
other environment ----->2seconds

First, if you are connecting thru 'localhost', that is a special connection that does not actually go thru all of the network software and as a result, it is usually quicker.  A remote connection to the production server goes thru the network software on both machines including the firewalls which takes more time than the 'localhost' connection.

There is no obvious problem with the query because of the difference in the environments.  If you go to the production machine, you would probably see a similar time thru 'localhost' on that machine.  It might be somewhat slower because it might be busier.  But you would have to run the query on the production server itself to compare with the 'localhost' results.

I am not connecting with local host.
I am connecting thru server ip address.

Run "EXPLAIN" for the query on both servers.  Is there any difference in their execution plan?

explain select testtotask0_.id as col_0_0_, task1_.task_status as col_1_0_, taskaction2_.success as col_2_0_, taskaction2_.action_status as col_3_0_, taskaction2_.scheduled_time as col_4_0_  from test_to_task testtotask0_  left outer join task task1_ on testtotask0_.task_id =task1_.task_id  inner join task_action taskaction2_ on testtotask0_.task_id=taskaction2_.task_id where testtotask0_.test_id=6 order by testtotask0_.id desc limit 1;
+----+-------------+--------------+--------+-----------------+---------+---------+----------------------------+--------+---------------------------------+
| id | select_type | table        | type   | possible_keys   | key     | key_len | ref                        | rows   | Extra                           |
+----+-------------+--------------+--------+-----------------+---------+---------+----------------------------+--------+---------------------------------+
|  1 | SIMPLE      | taskaction2_ | ALL    | NULL            | NULL    | NULL    | NULL                       | 350714 | Using temporary; Using filesort |
|  1 | SIMPLE      | testtotask0_ | ref    | test_id,task_id | task_id | 8       | stages.taskaction2_.task_id |      1 | Using where                     |
|  1 | SIMPLE      | task1_       | eq_ref | PRIMARY         | PRIMARY | 8       | stages.testtotask0_.task_id |      1 |                                 |
+----+-------------+--------------+--------+-----------------+---------+---------+----------------------------+--------+---------------------------------+
3 rows in set (0.00 sec)



Lab:

explain select testtotask0_.id as col_0_0_, task1_.task_status as col_1_0_, taskaction2_.success as col_2_0_, taskaction2_.action_status as col_3_0_, taskaction2_.scheduled_time as col_4_0_ from test_to_task testtotask0_ left outer join task task1_ on testtotask0_.task_id =task1_.task_id  inner join task_action taskaction2_ on testtotask0_.task_id=taskaction2_.task_id where testtotask0_.test_id=6 order by testtotask0_.id desc limit 1;
+----+-------------+--------------+--------+-----------------------+------------------+---------+---------------------------------+------+-----------------------------+
| id | select_type | table        | type   | possible_keys         | key              | key_len | ref                             | rows | Extra                       |
+----+-------------+--------------+--------+-----------------------+------------------+---------+---------------------------------+------+-----------------------------+
|  1 | SIMPLE      | testtotask0_ | ref    | test_id,task_id       | test_id          | 4       | const                           | 1725 | Using where; Using filesort |
|  1 | SIMPLE      | task1_       | eq_ref | PRIMARY,task_id_index | PRIMARY          | 8       | stagees.testtotask0_.task_id |    1 |                             |
|  1 | SIMPLE      | taskaction2_ | ref    | task_id_20101029      | task_id_20101029 | 8       | stages.testtotask0_.task_id |    1 |                             |
+----+-------------+--------------+--------+-----------------------+------------------+---------+---------------------------------+------+-----------------------------+
3 rows in set (0.01 sec)

3 rows in set (0.00 sec)
3 rows in set (0.01 sec) 

Select allOpen in new window

Looks like network - server response is the same.

I can see the difference in no of rows
one environment it is 1750,other one have 350000

in one environment I have added index on primary key manually by using
alter command add unique index.recently I have added this change.

Can you post "SHOW CREATE TABLE" for each of these tables, in each environment?

The EXPLAIN results are showing a few differences:

1) taskaction2_ is not using an index in the first set.  That results in 350k rows being examined vs the 1-row const comparison in the second set.

2) task1_ is using a different ref...?

3) testtotask0_ is selecting a different key between the two sets.  

What differences are there between the data sets and structures?

I will post it soon

Please find show create table details:

Lab:
 CREATE TABLE `test_to_task` (
  `id` int(10) NOT NULL auto_increment,
  `task_id` bigint(19) NOT NULL,
  `test_id` int(9) NOT NULL,
  `incident_id` bigint(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id_index` (`id`),
  KEY `test_id` (`test_id`),
  KEY `task_id` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=426972 DEFAULT CHARSET=latin1 |


Az:
CREATE TABLE `test_to_task` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(19) NOT NULL,
  `test_id` int(9) NOT NULL,
  `incident_id` bigint(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test_id` (`test_id`),
  KEY `task_id` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=462791 DEFAULT CHARSET=latin1 |

Lab:
CREATE TABLE `task` (
  `task_id` bigint(19) NOT NULL auto_increment,
  `message` text NOT NULL,
  `task_status` varchar(30) NOT NULL,
  `src_to_customer_id` bigint(20) NOT NULL,
  `pushed_on` datetime NOT NULL,
  `script_output` text NOT NULL,
  PRIMARY KEY  (`task_id`),
  UNIQUE KEY `task_id_index` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=436007 DEFAULT CHARSET=latin1 |

Az:
CREATE TABLE `task` (
  `task_id` bigint(19) NOT NULL AUTO_INCREMENT,
  `message` text NOT NULL,
  `task_status` varchar(30) NOT NULL,
  `src_to_customer_id` bigint(20) NOT NULL,
  `pushed_on` datetime NOT NULL,
  `script_output` text NOT NULL,
  PRIMARY KEY (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=472848 DEFAULT CHARSET=latin1 |




Lab:
CREATE TABLE `task_action` (
  `task_action_id` bigint(19) NOT NULL auto_increment,
  `task_id` bigint(19) NOT NULL,
  `action_id` int(10) NOT NULL,
  `action_status` varchar(30) NOT NULL,
  `CI_ID` bigint(19) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `asynchronous` int(1) NOT NULL,
  `est_time_of_completion` int(9) NOT NULL,
  `script_fire_time` datetime default NULL,
  `script_content` blob,
  `script_arguments` text,
  `script_output` mediumtext NOT NULL,
  `secondary_CI_ID` bigint(19) NOT NULL,
  `success` int(1) default NULL,
  `vault_ref_id` varchar(100) default NULL,
  `script_output_arguments` text,
  PRIMARY KEY  (`task_action_id`),
  UNIQUE KEY `taskaction_id_index` (`task_action_id`),
  KEY `task_id_20101029` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=439153 DEFAULT CHARSET=latin1 |


Az:

CREATE TABLE `task_action` (
  `task_action_id` bigint(19) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(19) NOT NULL,
  `action_id` int(10) NOT NULL,
  `action_status` varchar(30) NOT NULL,
  `CI_ID` bigint(19) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `asynchronous` int(1) NOT NULL,
  `est_time_of_completion` int(9) NOT NULL,
  `script_fire_time` datetime DEFAULT NULL,
  `script_content` blob,
  `script_arguments` text,
  `script_output` mediumtext NOT NULL,
  `secondary_CI_ID` bigint(19) NOT NULL,
  `success` int(1) DEFAULT NULL,
  `vault_ref_id` varchar(100) DEFAULT NULL,
  `script_output_arguments` text,
  PRIMARY KEY (`task_action_id`),
  KEY `task_id_20101029` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=476495 DEFAULT CHARSET=latin1 |

The main problem I see is that one of your environments is not using the task_id_20101029 index.  Why that index is being ignored is probably the key to the performance difference.

Check https://dev.mysql.com/doc/refman/5.5/en/explain-extended.html for your next steps, and post the results here.  I'm focusing on the environment not using the index, but please run this through both.

Hi

In Lab environment recently i have added index for primary key .
same thing i am not applied to another environment.
i came to know that no need to add index for primary key. so thats why i am not applied index for primary key on another environment.

apart from this any place i have ignored task_id_20101029 index.
Please let me know i will add it or i will utilize it effectively.

please verify explain extended query.
i have attached text file.
extend.txt

K,I will check and let you know.
Thanks

Hi SteveBink,

Yes u r correct index is ignoring in task_action table.
how do i reassign the index to the same column.
is it possible reindex it?
I am not sure how to add index again on the same column.
i have tried to add index

alter table task_action add index task_id_index(task_id);

but still it is ignoring index,i can ableto see possible key showing as null and cordinality value as null.

Can u suggest me what to do.

On further examination, I think the root problem is the index on another table - testtotask0_.  In the 'az' environment, the index task_id is being used, while lab is selecting the index test_id.  Since your where clause is keyed to test_id, that second index makes much more sense.  

What is the cardinality of the test_id column in both tables?  See here for information on assessing cardinality.

test_id having cardinality value in both the tables.

Mean while i have made change in  query.
splitten into two parts.

only task_action table only causing the issue.
select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;

when i execute only this query it is taking more time.
Earlies as i said cardinality value is zero for task_id ,

Run that new SELECT query through EXPLAIN in both environments and post the results.  Also, please post the SHOW CREATE TABLE for both tables.

AZ:
====

mysql> select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;
+----------+---------------+---------------------+
| col_0_0_ | col_1_0_      | col_2_0_            |
+----------+---------------+---------------------+
|        0 | TASK-ACT-COMP | 2010-07-21 09:00:00 |
+----------+---------------+---------------------+
1 row in set (0.79 sec)

mysql> explain select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | taskaction0_ | ALL  | NULL          | NULL | NULL    | NULL | 350714 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

CREATE TABLE `task_action` (
  `task_action_id` bigint(19) NOT NULL AUTO_INCREMENT,
  `task_id` bigint(19) NOT NULL,
  `action_id` int(10) NOT NULL,
  `action_status` varchar(30) NOT NULL,
  `CI_ID` bigint(19) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `asynchronous` int(1) NOT NULL,
  `est_time_of_completion` int(9) NOT NULL,
  `script_fire_time` datetime DEFAULT NULL,
  `script_content` blob,
  `script_arguments` text,
  `script_output` mediumtext NOT NULL,
  `secondary_CI_ID` bigint(19) NOT NULL,
  `success` int(1) DEFAULT NULL,
  `vault_ref_id` varchar(100) DEFAULT NULL,
  `script_output_arguments` text,
  PRIMARY KEY (`task_action_id`),
  KEY `task_id_20101029` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=476495 DEFAULT CHARSET=latin1 |


LAB:
====
mysql> select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;
+----------+---------------+---------------------+
| col_0_0_ | col_1_0_      | col_2_0_            |
+----------+---------------+---------------------+
|        0 | TASK-ACT-COMP | 2010-07-21 09:00:00 |
+----------+---------------+---------------------+
1 row in set (0.00 sec)

mysql> explain select taskaction0_.success as col_0_0_, taskaction0_.action_status as col_1_0_, taskaction0_.scheduled_time as col_2_0_ from task_action taskaction0_ where taskaction0_.task_id=11832;
+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys    | key              | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------+
|  1 | SIMPLE      | taskaction0_ | ref  | task_id_20101029 | task_id_20101029 | 8       | const |    1 |       |
+----+-------------+--------------+------+------------------+------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

 CREATE TABLE `task_action` (
  `task_action_id` bigint(19) NOT NULL auto_increment,
  `task_id` bigint(19) NOT NULL,
  `action_id` int(10) NOT NULL,
  `action_status` varchar(30) NOT NULL,
  `CI_ID` bigint(19) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `asynchronous` int(1) NOT NULL,
  `est_time_of_completion` int(9) NOT NULL,
  `script_fire_time` datetime default NULL,
  `script_content` blob,
  `script_arguments` text,
  `script_output` mediumtext NOT NULL,
  `secondary_CI_ID` bigint(19) NOT NULL,
  `success` int(1) default NULL,
  `vault_ref_id` varchar(100) default NULL,
  `script_output_arguments` text,
  PRIMARY KEY  (`task_action_id`),
  UNIQUE KEY `taskaction_id_index` (`task_action_id`),
  KEY `task_id_20101029` (`task_id`)
) ENGINE=MyISAM AUTO_INCREMENT=439169 DEFAULT CHARSET=latin1 |