INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Almost Identical Selects; Vastly Different Results

Almost Identical Selects; Vastly Different Results

Almost Identical Selects; Vastly Different Results

(OP)
Hello to all,

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

CODE

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 |
+----+-------------+-------+-------+----------------------+----------+---------+------+--------+ 

CODE

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

RE: Almost Identical Selects; Vastly Different Results

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close