ansaurus

Question

Answer 1

+2 A:

Well the biggest gain would be if you could normalize your data away from attribute-value approach and avoid transforming them. Can you do that?

Can you also do SHOW INDEXES IN Details?

EDIT: It seems the newtorder index suggestion worked for you. Could you just satisfy my curiosity and check two things:

Here is a rewrite of the query that turns the aggregate into joins, could you check how does it compare to the original one?

SELECT
  RULE
  ,TITLE
  ,SUM(IF(t.PASSED='Y',1,0)) AS PASS
  ,SUM(IF(t.PASSED='N',1,0)) AS FAIL
FROM
(
SELECT
  a.line_order,
  a.value AS TITLE,
  b.value AS RULE,
  c.value AS PASSED
FROM
  Details a INNER JOIN 
  Details b ON a.line_order = b.line_order AND a.run_id = runId AND a.element_name = 'PASSED' INNER JOIN 
  Details c ON b.line_order = c.line_order AND b.run_id = runId AND b.element_name = 'RULE' AND c.element_name = 'TITLE' 
) t
GROUP BY RULE, TITLE;

A few notes regarding the join

I assumed that for a TITLE there will be at least one RULE and PASSED value, if not turn the INNER JOINS to LEFT JOINS
Also I have moved the where conditions inside the join condition, this can sometimes be a hint to mysql to use indexes where it normally would not, but I am not sure about that and if you have inclination you can experiment with join conditions moving them from the ON into WHERE

Finally, could you also check what happens to the speed of your query if you add index on ('run_id', 'line_order','element_name', 'value')? I am not sure it will improve things (it will increase the size of the index, but will cut access to the individual rows), so it would be interesting to see the numbers (check the plan to see if it is really used).

Finally, another note on the original query - it might be possible to at least do aggregation in one step. Would you like to investigate that further?

Unreason 2010-04-20 21:54:40

@Unreason: Unfortunately I can't. There are over 900 different 'elements' that could be reported on, none are required, and they can be in any order.

manyxcxi 2010-04-20 22:10:50

@Unreason: Based on the data processed, there are sometimes no TITLE and no RULE, so it simply just gives me null,null,[n passes],[n fails] as output, which is a perfectly acceptable use case. I will try your query and report back to you how it works out. Thank you very much for your help as well!

manyxcxi 2010-04-21 16:05:59

@manyxcxi, ok then when turning it into LEFT JOINS you should then take out some of the conditions out of joins (because it will return bizarre results as it is if turned to LEFT JOIN; if you need help let me know, still simply for the speed comparison I think you can run the INNER JOIN version to evaluate if getting it to work even makes sense)

Unreason 2010-04-21 17:14:39

Answer 2

+3 A:

Wow. Here is a couple of hints:

The first problem here is using TEXT fields. When you use TEXT or BLOB, all temporary tables will be created on disk instead of memory. Try using varchar(N) instead, keep N as small as possible. It is strange that MySQL allows GROUPing BY on TEXT fields at all.
I would consider creation of a composite index on ('run_id', 'line_order','element_name'). Just to try, such an index may significantly affect performance in other parts of your system.

newtover 2010-04-20 23:07:02

@newtover: I'll try creating and index and reducing TEXT to varchar(N) if I can find a suitable N. The problem is that the value field can hold messages that are pretty long, a few characters, or a single int- it's sort of a catch-all. I know that using TEXT is wasteful, but it may be my only option IF I stick with this schema.

manyxcxi 2010-04-20 23:13:49

@newtover: SELECT(MAX(LENGTH(`value`))) FROM DetailsGives me 482, so I think varchar is not going to be in the cards for me...

manyxcxi 2010-04-20 23:29:45

@newtover: The index managed to cut my query time from 14 seconds to .3045 seconds! I played around with adding value into the key as well, but the same query ran in about 7. Thank you for point out my glaring mistake.

manyxcxi 2010-04-20 23:42:55

@manyxcxi: I am glad I managed to help. Just wanted to say that even varchar (1000) is much better than TEXT in your case.

newtover 2010-04-21 00:00:18

@newtover: I was under the impression (cannot remember where I read it) that if you were going to have a varchar over 255 in length it was better to use TEXT. I am admittedly not a MySQL DBA, I am usually just the guy developing against it. But if, in your opinion using 1000 is still better than TEXT, I'll certainly give that a whirl and try to figure out what a rational max length would be.

manyxcxi 2010-04-21 01:23:08

@manyxcxi: varchar can be up to 2^16 bytes long. It stores the length as the first byte if it is less than 256 and two bytes otherwise. When the character set of the field is utf-8, the length in bytes is character length multiplied by 3 (thus, your `element_name` column is actually up to 768 bytes long). TEXT fields store a reference to a block on disk (which result in item 1 of my answer). The rule of thumb should be to use VARCHAR instead of TEXT when the field is used in conditions (or extract the minimum to a separate field: prefix, hash value, etc.).

newtover 2010-04-21 07:52:53

ansaurus

tags:

views:

answers:

Any way to optimize this MySQL query?

related questions