MySQL Forums
Forum List  »  Optimizer & Parser

Optimize Inner Join With WHERE BETWEEN dates
Posted by: Anthony Galano
Date: May 21, 2013 07:32PM

Is there a way to optimize the query more efficiently? Or do what I'm trying to do a better way? The goal is to find users that have records with overlapping ClockIn-ClockOut ranges. This is a corner stone of a new feature. I suppose the performance is not horrible at around 3 seconds but this just a small sample. The query could be run over hundreds of users when generating certain reporting.

Any suggestions are appreciated!

EXAMPLE:

SELECT a.UserID
FROM records AS a INNER JOIN records AS b
WHERE a.UserID = b.UserID
AND a.TimeClass = b.TimeClass
AND a.RecordID != b.RecordID
AND a.ClockIn <> b.ClockOut
AND a.ClockOut <> b.ClockIn
AND ( a.ClockIn BETWEEN b.ClockIn AND b.ClockOut
OR a.ClockOut BETWEEN b.ClockIn AND b.ClockOut
)
AND a.UserID IN (1,37491,74806,73680,13179,13220)
AND b.UserID IN (1,37491,74806,73680,13179,13220)


EXPLAIN:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: range
possible_keys: ix_accrualsum,idx_recordalerts,idx_payroll_elligible,idx_overlappcheck,idx_hourlyreport,idx_hourlyreport3
key: idx_overlappcheck
key_len: 4
ref: NULL
rows: 3766
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: ix_accrualsum,idx_recordalerts,idx_payroll_elligible,idx_overlappcheck,idx_hourlyreport,idx_hourlyreport3
key: idx_overlappcheck
key_len: 5
ref: _beta.a.UserID,_beta.a.TimeClass
rows: 82
Extra: Using where; Using index

INDEXES:
*************************** 1. row ***************************
Table: records
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: RecordID
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: records
Non_unique: 1
Key_name: ix_accrualsum
Seq_in_index: 1
Column_name: TimeClass
Collation: A
Cardinality: 17
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: records
Non_unique: 1
Key_name: ix_accrualsum
Seq_in_index: 2
Column_name: Approved
Collation: A
Cardinality: 1536
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: records
Non_unique: 1
Key_name: idx_xhours
Seq_in_index: 1
Column_name: Paid
Collation: A
Cardinality: 17
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: records
Non_unique: 1
Key_name: idx_xhours
Seq_in_index: 2
Column_name: TimeClass
Collation: A
Cardinality: 17
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: records
Non_unique: 1
Key_name: idx_xhours
Seq_in_index: 3
Column_name: UserID
Collation: A
Cardinality: 331436
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: records
Non_unique: 1
Key_name: idx_recordalerts
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 22267
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: records
Non_unique: 1
Key_name: idx_recordalerts
Seq_in_index: 2
Column_name: Approved
Collation: A
Cardinality: 37143
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 9. row ***************************
Table: records
Non_unique: 1
Key_name: idx_recordalerts
Seq_in_index: 3
Column_name: Paid
Collation: A
Cardinality: 44649
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 10. row ***************************
Table: records
Non_unique: 1
Key_name: idx_projectid
Seq_in_index: 1
Column_name: ProjectID
Collation: A
Cardinality: 2086
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 11. row ***************************
Table: records
Non_unique: 1
Key_name: idx_payroll_elligible
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 47089
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 12. row ***************************
Table: records
Non_unique: 1
Key_name: idx_payroll_elligible
Seq_in_index: 2
Column_name: Paid
Collation: A
Cardinality: 62900
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 13. row ***************************
Table: records
Non_unique: 1
Key_name: idx_payroll_elligible
Seq_in_index: 3
Column_name: TimeClass
Collation: A
Cardinality: 103823
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 14. row ***************************
Table: records
Non_unique: 1
Key_name: idx_payroll_elligible
Seq_in_index: 4
Column_name: ClockIn
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 15. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 92659
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 16. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 2
Column_name: TimeClass
Collation: A
Cardinality: 105089
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 17. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 3
Column_name: RecordID
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 18. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 4
Column_name: ClockIn
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 19. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 5
Column_name: ClockOut
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 20. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 269292
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 21. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 2
Column_name: AccountCodeID
Collation: A
Cardinality: 359056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 22. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 3
Column_name: TimeClass
Collation: A
Cardinality: 430867
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 23. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 4
Column_name: Signed
Collation: A
Cardinality: 453544
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 24. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 5
Column_name: Approved
Collation: A
Cardinality: 574489
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 25. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 6
Column_name: Paid
Collation: A
Cardinality: 718112
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 26. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 1
Column_name: AccountCodeID
Collation: A
Cardinality: 74287
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 27. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 2
Column_name: TimeClass
Collation: A
Cardinality: 94696
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 28. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 3
Column_name: Signed
Collation: A
Cardinality: 94696
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 29. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 4
Column_name: Approved
Collation: A
Cardinality: 116450
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 30. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 5
Column_name: Paid
Collation: A
Cardinality: 123104
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 31. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport3
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 84483
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 32. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport3
Seq_in_index: 2
Column_name: TimeClass
Collation: A
Cardinality: 126725
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 33. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport3
Seq_in_index: 3
Column_name: Paid
Collation: A
Cardinality: 165718
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimize Inner Join With WHERE BETWEEN dates
2911
May 21, 2013 07:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.