MySQL Forums
Forum List  »  PHP

Greatly differing query times
Posted by: Michael Pryfogle
Date: August 03, 2005 10:49AM

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

Options: ReplyQuote


Subject
Written By
Posted
Greatly differing query times
August 03, 2005 10:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.