Greatly differing query times
I am currently running a Query trying to find users who use many activities and areas from an activity and area table. I am creating temp tables and referencing off of them to ease the query. When I run the query in PHP MyAdmin I past it as one long semicolon seperated query and it runs in about 1/10th of a second. In my PHP script it runs in about 35-40 seconds and I am trying to find out why.
This is whats posted on MyAdmin:
create temporary table temp_activity1 TYPE=heap select contact_id from activity where activity_id IN ('2', '3', '4', '5', '6', '7', '8', '9', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '58', '59', '60', '61'); CREATE INDEX activity_index ON temp_activity1 (contact_id); create temporary table temp_area1 TYPE=heap select contact_id from area where area_id IN ('1', '2', '3'); CREATE INDEX area_index ON temp_area1 (contact_id); select id from contacts , temp_activity1, temp_area1 where id IN (temp_activity1.contact_id) AND id IN (temp_area1.contact_id) order by id;
For my PHP script I sperate it out by the semicolon
CREATE TEMPORARY TABLE temp_activity1 TYPE=heap select contact_id from activity where activity_id IN ('2', '3', '4', '5', '6', '7', '8', '9', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '58', '59', '60', '61')
CREATE INDEX activity_index ON temp_activity1 (contact_id)
CREATE TEMPORARY TABLE temp_area1 TYPE=heap select contact_id from area where area_id IN ('1', '2', '3')
CREATE INDEX area_index ON temp_area1 (contact_id)
SELECT id from contacts , temp_activity1, temp_area1 where id IN (temp_activity1.contact_id) AND id IN (temp_area1.contact_id) order by id
All of the create statements take no real time, but the SELECT takes up the 35-40 second time, and I was wondering if there was a reason for this.
Thanks,
Michael