0

I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is the one I want to filter by). I'm trying a subquery which may be a mistake, but I can't really think of another way to do it because there are many statuses for a given file in myfiles.

MySQL 5.0.96, column schema on both tables involved is fewer than 15.

< 500 records in myfiles, 68k records in statuses:

SELECT myfiles.*, 
(SELECT status FROM statuses WHERE statuses.itemtype=2 AND statuses.id=myfiles.id ORDER BY historyid DESC LIMIT 1) AS actualStatus
FROM myfiles
HAVING actualStatus=0

Here's a fiddle with the actual table schemas involved. Thank you!

  • 1
    Edit your question with the create table DDL for each table in the query – Philᵀᴹ Oct 24 '14 at 0:04
  • @Phil i updated with a fiddle link to the actual schema. thanks! – Jason Oct 24 '14 at 0:07
  • I'd try adding an index on (itemtype, id, historyid, status) – ypercubeᵀᴹ Oct 24 '14 at 0:10
  • @ypercube what do you mean? how do i do that? (i'm newish) – Jason Oct 24 '14 at 0:11
  • Unrelated but: why are you using having when you are not using group by? – a_horse_with_no_name Oct 24 '14 at 13:35
1

I'd actually think about adding a status column to the table tblstockingorders and having a trigger on that table that injects the status into the tblstatushistory.

Granted you'd have to do a 1-time update to all the rows in tblstockingorders (something similar to your query above) and set their last status, but this would give you best overall performance as these two tables grow.

| improve this answer | |
1

The EXPLAIN statement can be very useful when trying to track down the cause of a slow query. There's some great explanations of how to use the EXPLAIN statement and how to interpret its results here (with more real-word examples at /case{2..4}) and the official documentation here.

Hope this helps!

| improve this answer | |
0

Sounds like you want only the orders with a certain status/type...

    SELECT tblstockingorders.*, tblstatushistory.status AS actualStatus
    FROM tblstockingorders
    INNER JOIN tblstatushistory 
        ON tblstatushistory.id=tblstockingorders.stockingorderid 
    WHERE tblstatushistory.itemtype=2
     AND  tblstatushistory.status=0

editted version

    SELECT tblstockingorders.*, actualStatus.actualStatus
    FROM tblstockingorders
    INNER JOIN 
    (
        SELECT ID, MAX(Status) AS actualStatus
        FROM tblstatushistory 
        GROUP BY ID
    ) actualStatus
        ON actualStatus.id=tblstockingorders.stockingorderid 
    WHERE tblstatushistory.itemtype=2
        AND  tblstatushistory.status=0
| improve this answer | |
  • this would work if each order had a single status, but i keep a history of statuses as individual entries, and i just want the latest one. so every order has a status history of 0 at some point (0 is the initial state), but some have moved on to other statuses. doing this would pull every single order. – Jason Oct 24 '14 at 20:00
  • 1
    Assuming(possibly poor assumption on my part) your statuses increase in value you could use a query in in the join instead. – Steve Mangiameli Oct 24 '14 at 20:08
  • they don't increase in value, but their ID's increment. brilliant suggestion! i will give it a shot :) – Jason Oct 24 '14 at 22:02
  • there is a modified timestamp. is there any way to pull the latest history timestamp for a given record? – Jason Oct 24 '14 at 22:23
0

Just going to let you guys know what I did, thanks for responding. I ended up sticking with my original query but added keys to my table, which are apparently a thing I didn't know about. When I did

ALTER TABLE tblstatushistory
ADD KEY (id),
ADD KEY (itemtype)

It dropped the time waaaay down. Thanks for pointing me towards resources that taught me about EXPLAIN (or at least explained EXPLAIN) and about understanding indexes.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.