Almost Identical Selects; Vastly Different Results

[EdSharp]

Hello to all,

I am completely baffled!
[ul]
[li]Table has 7.8 million rows with 1.2 million for the time period being queried.[/li]
[li]There is a Normal Index on Stock, ProdGp and DateTran.[/li]
[li]Stock is a medium int, ProdGp is a small int, DateTran is Date.[/li]
[li]Computer is W3K Server, MySql 5.14, 4 gb RAM, SATA Drive.[/li]
[/ul]

select at_stock, sum(at_amt), sum(at_tax) from ar3at where 
  at_datetran between 20111001 and 20120930 AND 
  at_stock between 0 and 99
    group by at_stock;

14 rows in set (4.42 sec)

+----+-------------+-------+-------+----------------------+----------+---------+------+--------+
| id | select_type | table | type  | possible_keys        | key      | key_len | ref  | rows   |
+----+-------------+-------+-------+----------------------+----------+---------+------+--------+
|  1 | SIMPLE      | ar3at | range | AT_Stock,AT_DateTran | AT_Stock | 4       | NULL | 187974 |
+----+-------------+-------+-------+----------------------+----------+---------+------+--------+

select at_prodgp, sum(at_amt), sum(at_cost) from ar3at where 
  at_datetran between 20111001 and 20120930 AND 
  at_prodgp between 600 AND 799 
    group by at_prodgp;

73 rows in set (30.55 sec)

+----+-------------+-------+-------+-----------------------+-----------+---------+------+---------+
| id | select_type | table | type  | possible_keys         | key       | key_len | ref  | rows    |
+----+-------------+-------+-------+-----------------------+-----------+---------+------+---------+
|  1 | SIMPLE      | ar3at | index | AT_DateTran,AT_ProdGp | AT_ProdGp | 3       | NULL | 7821985 |
+----+-------------+-------+-------+-----------------------+-----------+---------+------+---------+

I don't understand why the EXPLAIN shows a type of `range` for the first query and `index' for the second, 187,974 rows vs 7,821,985 and I sure don't understand the elapsed time!

This code is an extract and it runs on MySql console. In my program, I am actually creating a working table containing this select (insert into t1 select...) and I get an error message telling me that I have a "timeout error".

Can anyone provide any assistance?

Thank you in advance for your time and consideration.


Regards

Ed Sharp

 

[DonQuichote]

I think the EXPLAIN results explain everything. In Each case, there are two fields to filter upon, and one of the possible indexes to help is chosen. That means that the other filter has to be done in a temporary table or file (are you sure the EXPLAIN command does not tell you that?). Given that the second query yields orders of magnitude more results, it is by no means strange that it takes longer, especially if a temp table with full row-by-row checking is needed. MySQL selects the most useful (in its opinion) index, but that judgement can sometimes be wrong. So if you feel that it picks the wrong index of the two, you can add a "FORCE INDEX" clause to select the other one. It does not hurt to try it and see what happens.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)