Heya.
Firstly, appologies if this is the wrong maillist to send this too.
I'm using a great request tracking software package called RT (
www.fsck.com ) , however my
database seems to be a little larger than the ideal config for this system
as
its not scaling very well, i've tracked most of the problems down to one
query that takes
forever to run, as it seems to access every row in the tables its looking
at, I was
hoping for some help in optimising this query or in creating some better
indexes for it.
Oh, and I have tried to get a solution from the RT mailing lists first, but
no real luck
I've tried a few different indexes myself, but nothing seems to work. when I
explain this query
it shows that all the rows are beening looked at to resolve this query.
I would be very thankful for any help with this.
Here is the query
SELECT DISTINCT main.*
FROM Tickets main, Watchers Watchers_1 LEFT JOIN Users as Users_2 ON
Watchers_1.Owner = Users_2.id
WHERE ((main.EffectiveId = main.id))
AND ((Watchers_1.Scope = 'Ticket'))
AND ((Watchers_1.Type = 'Requestor'))
AND ((Watchers_1.Email = '[EMAIL PROTECTED]')OR(Users_2.EmailAddress =
'[EMAIL PROTECTED]'))
AND main.id = Watchers_1.Value ORDER BY main.id DESC LIMIT 50
the tables are as follows
mysql> describe Tickets
-> ;
+-----------------+--------------+------+-----+--------------+--------------
--+
| Field | Type | Null | Key | Default | Extra
|
+-----------------+--------------+------+-----+--------------+--------------
--+
| id | int(11) | | PRI | NULL |
auto_increment |
| EffectiveId | int(11) | YES | MUL | NULL |
|
| Queue | int(11) | YES | MUL | NULL |
|
| Type | varchar(16) | YES | | NULL |
|
| IssueStatement | int(11) | YES | | NULL |
|
| Resolution | int(11) | YES | | NULL |
|
| Owner | int(11) | YES | MUL | NULL |
|
| Subject | varchar(200) | YES | | [no subject] |
|
| InitialPriority | int(11) | YES | | NULL |
|
| FinalPriority | int(11) | YES | | NULL |
|
| Priority | int(11) | YES | | NULL |
|
| Status | varchar(10) | YES | | NULL |
|
| TimeWorked | int(11) | YES | | NULL |
|
| TimeLeft | int(11) | YES | | NULL |
|
| Told | datetime | YES | | NULL |
|
| Starts | datetime | YES | | NULL |
|
| Started | datetime | YES | | NULL |
|
| Due | datetime | YES | | NULL |
|
| Resolved | datetime | YES | | NULL |
|
| LastUpdatedBy | int(11) | YES | | NULL |
|
| LastUpdated | datetime | YES | | NULL |
|
| Creator | int(11) | YES | MUL | NULL |
|
| Created | datetime | YES | | NULL |
|
| Disabled | smallint(6) | | | 0 |
|
+-----------------+--------------+------+-----+--------------+--------------
--+
24 rows in set (0.00 sec)
mysql> describe Watchers
-> ;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| Type | varchar(16) | YES | MUL | NULL | |
| Scope | varchar(16) | YES | MUL | NULL | |
| Value | int(11) | YES | | NULL | |
| Email | varchar(255) | YES | MUL | NULL | |
| Quiet | int(11) | YES | | NULL | |
| Owner | int(11) | YES | MUL | NULL | |
| Creator | int(11) | YES | | NULL | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | YES | | NULL | |
| LastUpdated | datetime | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)
mysql> describe Users;
+-----------------------+--------------+------+-----+---------+-------------
---+
| Field | Type | Null | Key | Default | Extra
|
+-----------------------+--------------+------+-----+---------+-------------
---+
| id | int(11) | | PRI | NULL |
auto_increment |
| Name | varchar(120) | | UNI | |
|
| Password | varchar(40) | YES | | NULL |
|
| Comments | blob | YES | | NULL |
|
| Signature | blob | YES | | NULL |
|
| EmailAddress | varchar(120) | YES | MUL | NULL |
|
| FreeformContactInfo | blob | YES | | NULL |
|
| Organization | varchar(200) | YES | | NULL |
|
| Privileged | int(11) | YES | | NULL |
|
| RealName | varchar(120) | YES | | NULL |
|
| Nickname | varchar(16) | YES | | NULL |
|
| Lang | varchar(16) | YES | | NULL |
|
| EmailEncoding | varchar(16) | YES | | NULL |
|
| WebEncoding | varchar(16) | YES | | NULL |
|
| ExternalContactInfoId | varchar(100) | YES | | NULL |
|
| ContactInfoSystem | varchar(30) | YES | | NULL |
|
| ExternalAuthId | varchar(100) | YES | | NULL |
|
| AuthSystem | varchar(30) | YES | | NULL |
|
| Gecos | varchar(16) | YES | | NULL |
|
| HomePhone | varchar(30) | YES | | NULL |
|
| WorkPhone | varchar(30) | YES | | NULL |
|
| MobilePhone | varchar(30) | YES | | NULL |
|
| PagerPhone | varchar(30) | YES | | NULL |
|
| Address1 | varchar(200) | YES | | NULL |
|
| Address2 | varchar(200) | YES | | NULL |
|
| City | varchar(100) | YES | | NULL |
|
| State | varchar(100) | YES | | NULL |
|
| Zip | varchar(16) | YES | | NULL |
|
| Country | varchar(50) | YES | | NULL |
|
| Creator | int(11) | YES | | NULL |
|
| Created | datetime | YES | | NULL |
|
| LastUpdatedBy | int(11) | YES | | NULL |
|
| LastUpdated | datetime | YES | | NULL |
|
| Disabled | smallint(6) | | | 0 |
|
+-----------------------+--------------+------+-----+---------+-------------
---+
34 rows in set (0.00 sec)
----------------------------------------------
Matthew Watson
Development, Netspace Online Systems
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
