Multiple table queries vs one table query with multiple fields and more data sto

[acctman]

 

Which method is more efficient and optimized for storing and retrieving data on a potential large site

 

this version would store all the info in the member table which means only accessing 1 table

 

    m_country = US
    m_state = NY
    m_city = NEW YORK
    m_postal = 10011
    m_user = joe

    SELECT m_user, m_country, m_state, m_city, m_postal 
    FROM s_MEMBERS 
    WHERE $en_user = m_user

this one has one entry in the member table, then goes to the search_us table to get the info. Just looking at the code this option would appear best. But, multiple at least two queries will be needed because I'll also have to reference the MEMBER table. I like to avoid LEFT JOINs so i'd probably use a second query or pull what I can from already stored session data.

 

m_place = 103

SELECT us_id, us_zip, us_city, us_abv 
FROM search_US 
WHERE $en_place = us_id

[gizmola]

An optimized join is better than two separate queries.  As for caching data, I think you're better off with a data caching technology like memcache or apc rather than using session variables in most cases.  Session variables should have the data you need for the session.  Sure if you query data that you will be using throughout the session there is no problem sticking it in session variables, but depending on the session handler that is going to be in a database or files more often than not, and that data needs to be serialized between requests.  Session variables are not the place for lookup tables in my opinion.