Very Desperate - Performance Problem, NOT BASIC

[anon_login_001]

To begin: I've been a developer for quite some time, and although I wouldn't consider myself a "MySQL Expert" on a resume, I am *very* confident in my abilities to do decent MySQL performance tuning.

 

In this case, however, either I'm over-stressed and looking over something, or perhaps I'm truly out of my league and just don't realize it.

 

A company I'm working with has an installation of SugarCRM, which in the past has had some "custom" tabs/features/modules" added to it. Those custom additions are the source of queries that tend to run between 8 and 30 seconds a peice.

 

The queries, unfortunately, are built dynamically - so I'm having a hard time tracking down the best way to handle this situation, although they are mostly consistent.

 

If I'm not mistaken, the version of MySQL is a stable 4.1, on SuSE linux.

 

-----

 

I'll upload the whole my.cnf if needed, but don't have it at the moment. Though I will mention that:

 

The Query Cache is enabled, and running as effectively as possible. Since the slowest queries are dynamic, with changing search terms, the Query Cache doesn't keep them around anyway, since they're mostly One-offs.

 

The key-buffers are being used consistently, especially during these SELECT queries.

 

The query times are not being inflated by LOCKs... these are all SELECT Queries, and I've watched their status live several times, and there have been no LOCKs.

 

Also: YES, I am aware that there are some OBVIOUSLY poor structural issues. These are in place in a system that has been running for a very long time, and the point of my question is to see if there is anyone with a knowledge beyond my own that can help me make this work better *within my current contraints*. Knowledgeable answers that say "It cannot be done" are perfectly acceptable!

I'll be glad to supply any other information.

 

Aside from that, here's one particular problem query example:

 

SELECT users.user_name assigned_user_name, 
registration.FIELD001 parent_name, 
registration_task.status status,
registration_task.date_modified date_modified,
registration_task.date_due date_due, 
registration.FIELD240 assigned_wf,
if(LENGTH(registration_task.description)>0,1,0) has_description,
registration_task.* 
FROM registration_task 
LEFT JOIN users ON registration_task.assigned_user_id=users.id 
LEFT JOIN registration ON registration_task.parent_id=registration.id 
where (registration.FIELD001 LIKE 'schreckengost%') 
AND  registration_task.deleted=0  
ORDER BY date_due asc LIMIT 0,20;

 

It's corresponding info from the Slow Query Log:

# Query_time: 31  Lock_time: 0  Rows_sent: 20  Rows_examined: 1904929

 

an EXPLAIN Reveals:

id  select_type   table               type     possible_keys    key              key_len     ref                                              rows         Extra
1   SIMPLE        registration_task   ref      idx_reg_task_p   idx_reg_task_p   1           const                                            464926       Using where; Using filesort
1   SIMPLE        users               eq_ref   PRIMARY          PRIMARY          36          sugarcrm401.registration_task.assigned_user_id   1             
1   SIMPLE        registration        eq_ref   PRIMARY          PRIMARY          8           sugarcrm401.registration_task.parent_id          1            Using where

 

 

The table structures/create tables statements caused this post to be longer than allowable.

They are available at pastebin: http://pastebin.com/m284e3e79

 

 

If not obvious, I should add that what I'd like to see are Faster Query Times.

 

The machine is a Quadcore server with 6 Gigs of RAM. I don't have exact specs, but that should say something, at the least.

[fenway]

What if you drop the order by clause?