How to optimize this query with multiple substring and subquery

Question

Okay, I´m working on a website right now that shows information about parts of electronic devices. These parts sometimes get a revision. The part number stays the same, but they append an A, B, C etc to the part number, so the ´higher´ the letter, the newer it is. Also a date is added. So the table looks something like this:

------------------------------------------------------------
| Partcode       |   Description        | Partdate         |
------------------------------------------------------------
| 12345A         | Some description 1   | 2009-11-10       |
| 12345B         | Some description 2   | 2010-12-30       |
| 17896A         | Some description 3   | 2009-01-12       |
| 12345C         | Some description 4   | 2011-08-06       |
| 17896B         | Some description 5   | 2009-07-10       |
| 12345D         | Some description 6   | 2012-05-04       |
------------------------------------------------------------

What I need right now is the data from the newest revision of a part. So for this example I need:

12345D and 17896B

The query that some has build before me is something in the line of this:

SELECT substring(Partcode, 1, 5) AS Part,
       (
            SELECT pt.Partcode
            FROM Parttable pt
            WHERE substring(pt.PartCode, 1, 5) = Part
            ORDER BY pt.Partdate DESC
            LIMIT 0,1
       ),
       (
            SELECT pt.Description
            FROM Parttable pt
            WHERE substring(pt.PartCode, 1, 5) = Part
            ORDER BY pt.Partdate DESC
            LIMIT 0,1
       ),
       (
            SELECT pt.Partdate
            FROM Parttable pt
            WHERE substring(pt.PartCode, 1, 5) = Part
            ORDER BY pt.Partdate DESC
            LIMIT 0,1
       )
FROM Parttable
GROUP BY Part

As you will understand, this query is insanely slow and feels really inefficient. But I just can't get my head around how to optimize this query.

So I really hope someone can help.

Thanks in advance!

PS. I'm working on a MySQL database and before anyone asks, I can't change the database.

Normalizing part code in PartcodePrefix, PartcodeSuffix would be a good start. — Salman A, May 24 '12 at 07:09
@SalmanA As I said, can't change the database, otherwise that would have been the first thing I would have done. — Crinsane, May 24 '12 at 07:11

score 1 · Accepted Answer · answered May 24 '12 at 07:17

1

First : why not storing your version variable in a separate column? This way you wouldn't need to call substring to first extract it. If you really need the code and version to be concatenated, I thing it's a good practice to do it at the end.

Then in your place, I would first split the code and version, and simply use a max in an aggregate query, like:

SELECT code,max(version) FROM
  (SELECT substring(Partcode, 5, 1) as code,
         substring(Partcode, 1, 5) as version
  FROM Parttable
  )
AS part
GROUP BY code;

Note: I haven't tested this query so you may need to fix few parameters, like the substring indexes.

answered May 24 '12 at 07:17

Alexis

707
8
33

Thanks for the help. I changed to query to work with my data, and it kinda works. I get the correct partcode, so that's great, but I also need the description of that partcode, but I get the description of the first partcode it comes across (with the same code) – Crinsane May 24 '12 at 14:21
Then you should get what you want using an additional query level using INNER JOIN, see: http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql – Alexis May 24 '12 at 14:47
I fiddled a bit with it, and got it to works! execution time went from 20 seconds for the query, to 0.5 seconds! Great! Thanks! – Crinsane May 25 '12 at 09:05

How to optimize this query with multiple substring and subquery

1 Answers1