MySQL :: Optimize Inner Join With WHERE BETWEEN dates

New Topic

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:

Navigate: Previous Message• Next Message

Options: Reply• Quote

Subject

Views

Written By

Posted

Optimize Inner Join With WHERE BETWEEN dates

4084

Anthony Galano

May 21, 2013 07:32PM

Re: Optimize Inner Join With WHERE BETWEEN dates

1684

Rick James

May 22, 2013 06:41PM

Re: Optimize Inner Join With WHERE BETWEEN dates

1629

Anthony Galano

May 24, 2013 12:40PM

Re: Optimize Inner Join With WHERE BETWEEN dates

1488

Rick James

May 25, 2013 11:43AM

Re: Optimize Inner Join With WHERE BETWEEN dates

1569

Anthony Galano

May 30, 2013 05:30PM

Re: Optimize Inner Join With WHERE BETWEEN dates

1481

Rick James

June 02, 2013 01:15PM

Re: Optimize Inner Join With WHERE BETWEEN dates

1645

Anthony Galano

June 03, 2013 05:02PM

Re: Optimize Inner Join With WHERE BETWEEN dates

2850

Rick James

June 04, 2013 11:49PM

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.