homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Selecting information from one db, prices from another
This code works, but is it the best way to do it?

5+ Year Member

Msg#: 4545264 posted 11:34 am on Feb 13, 2013 (gmt 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'





WebmasterWorld Senior Member 5+ Year Member

Msg#: 4545264 posted 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.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved