Selecting information from one db, prices from another

Forum Moderators: open

Message Too Old, No Replies

Selecting information from one db, prices from another

This code works, but is it the best way to do it?

Elric99

11:34 am on Feb 13, 2013 (gmt 0)

Hello

My situation is I have one database with product information, another with prices. I need to get info from the first and prices from the second. This code seems to work, but I'd like to know if it's a good idea to add in the 'AND's on the JOIN in this way:

SELECT i.productId, i.id, p.wasPrice, p.thisPrice FROM product_info i
LEFT JOIN product_prices p
ON i.productId = p.productId AND i.dealDate = p.dealDate AND i.wasPrice = p.wasPrice
WHERE i.dealDate = '2013-08-16'
AND i.productId = 'NB5726'
GROUP BY i.id

Thanks!

Tom

Matthew1980

2:13 pm on Mar 3, 2013 (gmt 0)

Hi there Elric99,

Well I've a similar sort of thing that I use for a works project that allows our customers to do pretty much just that, and from what I can tell, you're doing it the same way as I am. It doesn't seem to affect the query speed at all having the And where it is, and even using a query builder it does much the same thing.

<slight tangent>
The question is - - when you run this, is there any discernable 'lag' when you're waiting for the database to complete the output back to you? Also this can depend on whether you are using INNO or MyIsam in your databse or not.

I've ony recently discovered that there is a difference in data retrieval speed when doing this. Essentially, (I do hope I've not got this wrong ;)) MyISAM will only lock the row in the table when querying the DB, INNODB will lock the table, therefore, if a query is queing, there will be a 'lag' whilst the query is waiting to be executed.

</slight tangent>

Moderators: Please correct me if i'm wrong there! I could have that backwards!

Succint answer: Looks Ok to me.

Cheers,
MRb