Order By Join Column

[henryrhenryr]

I have a query in which I want to order the join table before joining it.

It's something like this:

SELECT product, price
FROM prods
  INNER JOIN subprods ON prodPk=prodFk ORDER BY price ASC
GROUP BY prodPk
 

Each product may have 1 or more subproducts with varying prices. I want to select a product and the lowest price from the list of sub-products.

Intuitively speaking, I would put the "order by" on the join. But clearly this doesn't work! I have also tried using a sub-query like this:

SELECT product, (SELECT price FROM subprods WHERE prodFk=prodPK ORDER BY price ASC LIMIT 0,1) AS price
FROM prods
 

This works but is taking about 8 seconds for the query to run (about 4000 rows returned). When I remove the sub-query, this drops to under 1 second.

Can anyone point me in the right direction?

Thanks

Henry

[coolsti]

Yes, you cannot do what you are trying to do using the order by before the group by. It won't work.

What you need to do is to use the query structure that I explain in my two posts on this thread:

You need something like the query technique that I explained in this thread:

http://bytes.com/forum/thread819577.html

See if that helps you, and post again if it isn't clear what to do.

[coolsti]

Ok, I am completely bored at work at the moment, so I will try to make this query for you.

I do not have your tables in my database, so I do not know if the following is completely free of any syntax bugs, but this is basically what you need:

SELECT T1.product, T2.price from prods T1 inner join subprods T2 ON T1.prodPk=T2.prodFk inner join (SELECT T3.prodPk, MIN(T4.price) as minprice from prods T3 inner join subprods T4 ON T3.prodPk=T4.prodFk group by T3.prodPk) T5 where T1.prodPk = T5.prodPk and T2.price = T5.minprice.

Not all the table aliases are necessary, but at least doing it this way makes it clear which table's column is being used.

Basically you make a two part query. The subquery creates a table which I give the alias T5 which is composed just of what I assume is the unique index of the prods table `prodPk` and the minimum price for each. This table is your minimum price filter, and is used in a join with the first part of the query which selects the columns that you wish to see in your output.

In the subquery, you create as many columns as necessary in order to be able to distinctly select from the rows of the first part of the query. In this case, you need the prodPk index and of course the minium price to select.

The above solution has one flaw, however. If your subtable subprods has two entries for the same product with exactly the same price, and this price happens also to be the minium price, then your result will have two rows that are the same, for this product. If this could happen, you would then need to add a "DISTINCT", and I think if you place it after the first SELECT this will work.

[henryrhenryr]

Hi

Thanks for this solution - yes it does indeed work! The scenario with the same price for the subprod does exist and a group by right at the end solved that - select distinct didn't help.

I have to report, though, that i went for a work-around in the end as my main aim is shaving time off the query:

My initial solution (with simple sub-query) - about 22 seconds - ahh!
Your solution (with join and sub-query) - about 3.6 seconds
My work-around solution - about 1.5 seconds

My work-around involved making a new field called "cheapest" and running a 'slow' query to update that field with 1 for all the cheapest subprods. Then the query is a simple join with "cheapest=1" - easy. Then I put a little script which updates this every week which is about as much as it changes.

Thanks for your help! Actually I didn't know you could use sub-queries in place of tables until I read this so it has been very useful!

Henry

[coolsti]

Henry, the idea to have another program running periodically to update your cheapest column is not a bad idea, but you are then dependent on this working and not stopping for some reason.

What you might consider: Try to perform the query I suggested, and add some extra column indexes here and there. In particular in things that are present in the where clauses. It is amazing how much faster a query can become by adding an index. I once speeded up a query from taking 10 seconds to taking about 0.1 second by adding one index.

If you can do this and find an index that can speed things up considerably, then you have all you need in the one query without the other program running periodically :)

[coolsti]

Henry, the idea to have another program running periodically to update your cheapest column is not a bad idea, but you are then dependent on this working and not stopping for some reason.

What you might consider: Try to perform the query I suggested, and add some extra column indexes here and there. In particular in things that are present in the where clauses. It is amazing how much faster a query can become by adding an index. I once speeded up a query from taking 10 seconds to taking about 0.1 second by adding one index.

If you can do this and find an index that can speed things up considerably, then you have all you need in the one query without the other program running periodically :)

Afterthought: looking at your query, you may wish to try first to put an index on the product ID in the subprods table. If that does not speed things up, you could try instead to put an index on the price column. Both these columns are used in where clauses (for price, you need to find the minimum). Actually, for price, since you are looking for the minimum in a group by product ID, it may speed things up to make an index over both these columns together, that is one index defined on both columns.

However, try one thing at a time, and don't add more indexes than you need, as there is of course overhead (hardisk space and insert/update time) involved in having an index.

You could try to carry out your query but tack on the "EXPLAIN" keyword to the beginning of it. This then does not carry out your query, but gives you a table of information of how many rows need to be examined per table, and also may give you hints on where an index will help.

Regarding your "behind the scenes" program that updates the extra minimum price column, another thing you could consider doing is updating this information each time there is an update to the database. That is, you change whatever scripts are being used to update or insert rows into the database to also update the minimum price flag. This will work if the amount of effort to do this update is small compared to how often the database is updated. For example if the database is updated with many hits per second then this is probably not a good idea.

[henryrhenryr]

Hi

Thanks again for the useful pointers.

The tables were pretty much indexed up already - been caught there before ;). I still find it something of a mystery over which ones MySQL uses and I might try to test some 'combination' indexes to see if that works better...

The update script is working quite well. Actually the data are only updated once a week so it makes quite a lot of sense to use this. Good idea about updating when the data are changed though, I should be able to include that functionality quite easily...

Thanks for the thorough help!

Henry

[coolsti]

Glad to help.

Try using the "EXPLAIN ....." syntax, where the keyword EXPLAIN is just added to the start of any query. If you do this, mysql will not carry out the query, but will give you a table showing what it needs to do to carry out the query. That is, it shows you how many rows in each table need to be searched, and what indexes are used.

This should then tell you what indexes are actually going to be used for a query, and which ones are not.

It also gives you some indication of where to add extra indexes. If you have several tables which indicate that ALL the rows need to be examined, then you should be able to speed up the query significantly by adding an appropriate index.