On 12/18/2013 01:07 PM, Kevin Falcone wrote:
Your query here says that MySQL is looking at a pretty tiny dataset
and running the same query on multiple databases here shows a wildly
different explain and very quick results. This usually points to you
having some enormous tables (although the explain doesn't indicate
that) or a badly tuned mysql.
Can you download and run http://mysqltuner.pl on your database.
I will have to go through this script and consult with a colleague
before running that as our database contains more than just our ticket
system.
It would also be interesting to see
SELECT TABLE_ROWS, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'rt4';
changing your database name where needed.
mysql> SELECT TABLE_ROWS, TABLE_NAME
-> FROM information_schema.tables
-> WHERE TABLE_SCHEMA = 'rt4_2_1';
+------------+-------------------------+
| TABLE_ROWS | TABLE_NAME |
+------------+-------------------------+
| 579 | ACL |
| 0 | Articles |
| 1436268 | Attachments |
| 5708 | Attributes |
| 353565 | CachedGroupMembers |
| 0 | Classes |
| 36 | CustomFieldValues |
| 4 | CustomFields |
| 107281 | GroupMembers |
| 157020 | Groups |
| 1579 | Links |
| 0 | ObjectClasses |
| 14357 | ObjectCustomFieldValues |
| 4 | ObjectCustomFields |
| 45 | ObjectScrips |
| 0 | ObjectTopics |
| 162308 | Principals |
| 16 | Queues |
| 21 | ScripActions |
| 22 | ScripConditions |
| 45 | Scrips |
| 35 | Templates |
| 35232 | Tickets |
| 0 | Topics |
| 578000 | Transactions |
| 10340 | Users |
| 4518 | sessions |
+------------+-------------------------+
27 rows in set, 1 warning (0.43 sec)
Thanks!
//adam
