# Performance Problem, Not Basic, I'm knowledgeable **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [July 22, 2008, 8:16am UTC](https://forums.percona.com/t/performance-problem-not-basic-im-knowledgeable/848 "2008-07-22T08:16:25Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![anon\_login\_001](https://avatars.discourse-cdn.com/v4/letter/a/ac91a4/32.png) [@anon\_login\_001](https://forums.percona.com/u/anon_login_001)\ **Post date:** [July 22, 2008, 8:16am UTC](https://forums.percona.com/t/performance-problem-not-basic-im-knowledgeable/848/1 "2008-07-22T08:16:25Z")
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: [http://pastebin.com/m284e3e79](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. ---
**Author:** ![malonso](https://avatars.discourse-cdn.com/v4/letter/m/eb8c5e/32.png) [@malonso](https://forums.percona.com/u/malonso)\ **Post date:** [July 22, 2008, 5:17pm UTC](https://forums.percona.com/t/performance-problem-not-basic-im-knowledgeable/848/2 "2008-07-22T17:17:06Z")
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%’) ---
**Author:** ![teajay2](https://avatars.discourse-cdn.com/v4/letter/t/3e96dc/32.png) [@teajay2](https://forums.percona.com/u/teajay2)\ **Post date:** [July 22, 2008, 7:57pm UTC](https://forums.percona.com/t/performance-problem-not-basic-im-knowledgeable/848/3 "2008-07-22T19:57:58Z")
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. ---
**Author:** ![anon\_login\_001](https://avatars.discourse-cdn.com/v4/letter/a/ac91a4/32.png) [@anon\_login\_001](https://forums.percona.com/u/anon_login_001)\ **Post date:** [July 23, 2008, 8:07am UTC](https://forums.percona.com/t/performance-problem-not-basic-im-knowledgeable/848/4 "2008-07-23T08:07:02Z")
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? ---
**Author:** ![teajay2](https://avatars.discourse-cdn.com/v4/letter/t/3e96dc/32.png) [@teajay2](https://forums.percona.com/u/teajay2)\ **Post date:** [July 23, 2008, 7:07pm UTC](https://forums.percona.com/t/performance-problem-not-basic-im-knowledgeable/848/5 "2008-07-23T19:07:15Z")
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. ---
**Author:** ![tom.krouper](https://avatars.discourse-cdn.com/v4/letter/t/ce73a5/32.png) [@tom.krouper](https://forums.percona.com/u/tom.krouper)\ **Post date:** [July 24, 2008, 11:14pm UTC](https://forums.percona.com/t/performance-problem-not-basic-im-knowledgeable/848/6 "2008-07-24T23:14:28Z")
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.