homepage Welcome to WebmasterWorld Guest from 23.22.217.122
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:4545266
 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




msg:4550650
 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

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