> Table cache is the name of what is happening, and your DB engine is > who is doing it. Also about setting indexes:
Thanks for the links. I read through them but will have to again to make sure the information sinks in. Overall, it sounds like the best optimization is to only include fields in the query which are indexed. This presents a fundamental problem for me since I am querying quite a few fields (notice all the address fields) and I know creating an index for all of these columns is probably not the best solution either. In short, I'm not sure how much the index is really helping in my particular case. > B Logica's query : > > Will return : > > 100 John Smith // this matches "1 John Smith" > 100 John Smith // this matches "2 John Smith" > > So, fix it this way: > > SELECT c.id, c.fn, c.ln FROM contacts AS c INNER JOIN import_contacts > AS ic ON c.fn = ic.fn AND c.ln = ic.ln GROUP BY (c.id); Thanks for the explanation of why I'm getting the duplicates in the result set, I think I just needed it visually spelled out. At any extent, I tried the DISTINCT and GROUP BY options but decided to remove them and settled on this query for now: SELECT ic.id, ic.fn, ic.ln, c.id, ic.phone_home, ic.line1, ic.line2, ic.city, ic.st, ic.zip, c.phone_home, a.line1, a.line2, a.city, a.st, a.zip FROM import_contacts AS ic INNER JOIN contacts AS c ON c.fn = ic.fn AND c.ln = ic.ln LEFT JOIN addresses AS a ON c.id = a.contact_id ORDER BY ic.ln, ic.fn I get 25 records this way (obviously some are retrieved twice) but I actually want that. This is because the result set is being displayed to the client as a 2-column layout (in HTML I mean, not database tables :) with the POTENTIAL duplicates from the import_contacts table on the left and each of their corresponding existing entries on the right. I believe the process of filtering out duplicates still needs to be a manual process because two John Smith's will likely exist and we have to determine if the John Smith awaiting import is updating his information, repeating it or actually a completely different John Smith altogether. BTW, the above query executes in about 32-47ms! It even retrieves all the data I actually need for my view (imagine that ;) Now I just need to change some HTML code and I think I'll be in good shape... --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
