Thanks to everyone who has contributed so far, it's much appreciated!
Now, here's where we stand...
jonknee's query took about 25-27 seconds and returned 154 records.
b logica's query took about 6 seconds and returned 24 records
My original query takes 18 seconds and returns ALL records and is
exactly this:
SELECT CONCAT(`Contact`.`ln`, `Contact`.`fn`) AS `full_name`,
`Contact`.`id`, `Contact`.`created`, `Contact`.`modified`,
`Contact`.`fn`, `Contact`.`ln`, `Contact`.`co`, `Contact`.`email1`,
`Contact`.`phone_home`, `Address`.`line1`, `Address`.`line2`,
`Address`.`city`, `Address`.`st`, `Address`.`zip` FROM `contacts` AS
`Contact` LEFT JOIN `addresses` AS `Address` ON `Contact`.`id` =
`Address`.`contact_id` WHERE `Contact`.`ln` <> '' AND `Contact`.`fn`
<> '' AND `Contact`.`ln` IS NOT NULL AND `Contact`.`fn` IS NOT NULL
ORDER BY `Contact`.`ln` ASC, `Contact`.`fn` ASC
Then I store the results into an array and run this query:
SELECT * FROM `import_contacts` AS `ImportContact` ORDER BY `ln` ASC,
`fn` ASC
I store those results into an array, too, then I loop through the
ImportContact array looking for a matching key (PHP's array_key_exists
function). I can do this because I format the two result set arrays
using a concatenated string as the key of the array. For instance, a
record with a first name of "Mark" and last name of "Smith" gets
stored as:
array("MarkSmith" => array("field1" => "value1", "field2" =>
"value2"));
This is working great, it's just timing out my PHP script because it
can take 3+ minutes to execute the whole thing across the 30k records
I have :-/
On Mar 27, 5:44 pm, "Dardo Sordi Bogado" <[EMAIL PROTECTED]> wrote:
> > SELECT * FROM contactImport WHERE first_name IN (SELECT first_name
> > FROM contact) AND last_name in (SELECT last_name FROM contact)
>
> That is a really bad query (for 30k records) and even don't solve the issue.
>
> > b logica's way would work too, so you could just check whatever is
> > faster. I don't have 30,000 records to test that out on :P.
>
> blogica's one seems to be right.
>
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
