Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Rant:</strong> OMG Ponies' answer gives what you asked for - a rewrite with a join. But I would not be too excited about it, your performance killer is the inside join on email address which, I assume, is not particular selective and then your database needs to sift through all of those rows looking for the MAX of order date.</p> <p>This in reality for MySQL will mean doing a filesort (can you post EXPLAIN SELECT ....?).</p> <p>Now, if mysql had access to an index that would include <code>emailaddress</code>, <code>product</code> and <code>orderdate</code> it might, especially on MyISAM be much more efficient in determining MAX(orderdate) (and no, having an index on each of the columns is not the same as having a composite index on all of the columns). If I was trying to optimize that query, I would bet on that.</p> <p>Other than this rant here's my version of <code>not the latest from a category</code> (I don't expect it to be better, but it is different and you should test the performance; it just might be faster due to lack of subqueries)</p> <p><strong>My attempt</strong> (not tested)</p> <pre><code>SELECT DISTINCT notlatest.id, notlatest.emailaddress, notlatest.product, notlatest.orderdate FROM OrderHistoryTable AS notlatest LEFT JOIN OrderHistoryTable AS EMP latest ON notlatest.emailaddress = latest.emailaddress AND notlatest.orderdate &lt; latest.orderdate AND WHERE notlatest.product IN ('ProductA','ProductB','ProductC','ProductD') AND latest.product IN ('ProductA','ProductB','ProductC','ProductD') AND latest.id IS NOT NULL </code></pre> <p><strong>Comments:</strong><br> - If there is only one record in the category it will not be displayed<br> - Again indexes should speed the above very much</p> <p>Actually this is (might be) a good example of how normalizing data would <strong>improve</strong> performance - your product implies product category, but product category is not stored anywhere and the IN test will not be maintainable in the long run. </p> <p>Furthermore by creating a product category you would be able to <strong>index</strong> directly on it. </p> <p>If the Product was indexed on the category then the performance of joins on the category should be better then test on the Product indexed by value (and not category). (Actually then MyISAM's composite index on <code>emailaddress</code>, <code>category</code>, <code>orderdate</code> should already contain max, min and count per category and that should be cheap).</p>
    singulars
    1. This table or related slice is empty.
    1. POPoorly performing Mysql subquery -- can I turn it into a Join?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USUnreason
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POPoorly performing Mysql subquery -- can I turn it into a Join?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COActually on second though it need not be LEFT JOIN but it can be INNER JOIN without IS NOT NULL condition on latest.id (which should be even better for mysql).
      singulars
      1. PO
      1. USUnreason
    2. COJust tried your suggestion and got a 2 x performance increase over my previous best solution... 90 seconds originally is now down to 15 seconds. However, I had also added a covering index on email/product/orderdate which helped a bit, but hot hugely.
      singulars
      1. PO
      1. USBrian Adkins
    3. COAlso, I agree 100% about normalization... but it's not in the cards for now. Maybe a future project for me.
      singulars
      1. PO
      1. USBrian Adkins
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload