Performance Problem, Not Basic, I'm knowledgeable

MySQL & MariaDB Other MySQL® Questions
1

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 Extra1 SIMPLE registration_task ref idx_reg_task_p idx_reg_task_p 1 const 464926 Using where; Using filesort1 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 are probably longer than allowable in the forum…
They are available at pastebin: [URL]http://pastebin.com/m284e3e79[/URL]

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.

2

I could be way off here but is there anyway you can switch the order of the WHERE clause to:

registration_task.deleted=0 AND (registration.FIELD001 LIKE ‘schreckengost%’)

3

Well, I would take that one further. From the explain, it doesn’t appear you have the column ‘FIELD001’ indexed at all, and thus its looking through 464926 rows… at best to return 20. that seems to be a waste.

Since you are don’t seem to be doing like ‘%…%’, i believe an index on this column would be helpful.

4

FIELD001 is second in an index called ‘mbr_name’.

I have since indexed FIELD001 by itself, as well as swapped the WHERE around, as suggested by malonso.

EXPLAIN says:

id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra1 |SIMPLE |registration_task |ref |idx_reg_task_p,parent_id |idx_reg_task_p |1 |const |157693 |Using where; Using filesort1 |SIMPLE |registration |eq_ref |PRIMARY,mbr_name,FIELD001 |PRIMARY |8 |sugarcrm401.registration_task.parent_id |1 |Using where1 |SIMPLE |users |eq_ref |PRIMARY |PRIMARY |36 |sugarcrm401.registration_task.assigned_user_id |1 |

I think, and please suggest if I’m on/off the target:
The problem lies in the fact that I’m initially selecting FROM the registration_task table, but the only condition in place to round that down is registration_task.deleted=0 (for that particular table), of which there are so many records.

So then MySQL must load those records and then filter down from there, correct? So the big slowdown is that scanning (even with an index) through all of those results, and then the SORT, depending on what column they chose to sort by
(Using filesort).

Parts of Sort By are normally done on registration table, which is joined by an ID to users, which is joined by an ID itself… as shown in the EXPLAIN. So that to me looks like a fairly effecient join… I just have no way of getting down the number of records that registration_task looks at, it seems.

Do I still look stuck?

5

What columns are being indexed by the index ‘idx_reg_task_p’?

If you have an index on the deleted column, would it be possible to add FIELD001 to the index?

Do you have any indexes with FIELD001 as the first column in the index?

Mysql will will attempt to use indexes based on the order of the columns. It may not need every column that is in the index, but the order is important. If FIELD001 is the second column of an index where the first column is not ‘deleted’ then it would not be used.

6

My guess is that deleted is a 1 or a 0. You are going to run into an issue if that is more than MySQL’s breaking point to not use the index. (I think I heard 30% at some point.) So, if you have 100 records and 35 (or more) of them are 0, and you do a select for 0, the system is just going to do a full table scan. That could certainly slow down the select. I’ll keep looking to see if I can figure out anything else from the information.