Welcome to WebmasterWorld Guest from

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?

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

Junior Member

10+ Year Member

joined:May 7, 2005
posts: 143
votes: 0


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'


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

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
votes: 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.