Database execution time problem

[seangkb]

Good day everyone!
I'm facing problem in database execution timeout.
my database has about 60000 records(table1) and 10000(table2) , and i need to execution sql queries that retrieve data(about 5000 records) from each table.
I had try to configure the php.ini to increase the max_execution_time to 120 but still not enough time to execute my script. so i would like to know instead of continue inceasing the max_execution_time, do i still have any solution to solve this problem?

thanks for help 😃

regards
seangkb

[nemonoman]

If you are using a reasonably well structured database in MySQL with this number of rows you should be executing most well written queries in way under 10 seconds.

I therefore suspect that your query is a little screwy.

Please post your query: I expect it can be optimized.

[seangkb]

the query is something like this:

Select A.AdID, A.Advertisement, AM.MediaID, AM.Cat,
M.Title, M.Artiste, M.SecID, M.SourceFile, M.ProductTypeID
From AnaMedia AM,AnaSet SM,Advertisement A ,Media M
Where AM.SetID=SM.SetID AND SM.AdID=A.AdID
AND AM.MediaID=M.MediaID AND SM.China=0
AND SM.Date Between '$LAST14DAYS' AND '$YESTERDAY'
Order By A.AdID, AM.Cat , AM.MediaID

after execute i put the relevant data inside series of array...Then run for loop for these three query:
($LAST7DAYS and $LAST14DAYS and $YESTERDAY are constant date from today)

select count(DownloadID) from Download
where MediaID=".$row_adv["MediaID"]."
AND AdID=".$row_adv["AdID"]." AND Date='$YESTERDAY'

select count(DownloadID) from Download
where MediaID=".$row_adv["MediaID"]."
AND AdID=".$row_adv["AdID"]." AND Date

Between'$LAST7DAYS' AND '$YESTERDAY'

select count(DownloadID) from Download
where MediaID=".$row_adv["MediaID"]."
AND AdID=".$row_adv["AdID"]." AND Date

Between'$LAST14DAYS' AND '$YESTERDAY'

then again i will store the relevant data into array...after this the array of download counter will be sort by arsort().

finally the layout of report is printed by looping the arrays.

PS: for ur information, if the date for $LAST14DAYS is only 10days before, then the script will run completely.

here is DB definition

Table: 'Advertisement'

CREATE TABLE Advertisement (
AdID int(4) NOT NULL default '0',
Advertisement varchar(255) default NULL,
PRIMARY KEY (AdID)
) TYPE=MyISAM;

Table: 'AnaMedia'

CREATE TABLE AnaMedia (
MediaID int(5) NOT NULL default '0',
Cat varchar(255) default NULL,
SetID int(4) NOT NULL default '0',
PRIMARY KEY (MediaID,SetID),
KEY CAT_IDX (Cat)
) TYPE=MyISAM;

Table: 'AnaSet'

CREATE TABLE AnaSet (
SetID int(4) unsigned NOT NULL auto_increment,
SetName varchar(255) default NULL,
AdID int(4) NOT NULL default '0',
Date date default NULL,
China tinyint(2) default '0',
PRIMARY KEY (SetID),
KEY AD_IDX (AdID)
) TYPE=MyISAM;

Table: 'Download'

CREATE TABLE Download (
DownloadID int(6) unsigned NOT NULL auto_increment,
Date date default NULL,
Time time default NULL,
MediaID int(5) default '0',
ProductTypeID int(4) default '0',
CategoryID int(4) default '0',
AdID int(4) default '0',
MobileNo varchar(16) default NULL,
Opcode varchar(6) default NULL,
TypeID tinyint(2) default '0',
ModelID tinyint(2) default '0',
Status tinyint(2) default '0',
ProvRet int(4) default NULL,
PRIMARY KEY (DownloadID),
KEY MEDIA_IDX (MediaID),
KEY AD_IDX (AdID),
KEY TYPE_CAT_IDX (ProductTypeID,CategoryID)
) TYPE=MyISAM;

Table: 'Media'

CREATE TABLE Media (
MediaID int(5) unsigned NOT NULL auto_increment,
Media blob,
Title varchar(255) default NULL,
Artiste varchar(255) default NULL,
ProductTypeID int(4) default NULL,
CategoryID int(4) default NULL,
SourceFile varchar(255) default NULL,
DateIn date default NULL,
Notes varchar(50) default NULL,
SecID int(5) default NULL,
TitleC varchar(60) default NULL,
ArtisteC varchar(40) default NULL,
RTTTL tinyint(1) unsigned default '0',
PRIMARY KEY (MediaID),
KEY MEDIAID_IDX (MediaID)

) TYPE=MyISAM;

Thanx for help

regards,
GKB

[nemonoman]

what does this query return?

Select count(*)
From AnaMedia AM,AnaSet SM,Advertisement A ,Media M
Where AM.SetID=SM.SetID AND SM.AdID=A.AdID
AND AM.MediaID=M.MediaID AND SM.China=0
AND SM.Date Between '$LAST14DAYS' AND '$YESTERDAY'

[nemonoman]

If you're expecting 5000 records to be returned, conceivably you could be running those last 3 queries 15000 total times, which time could add up...

You could probably get each of those statements to be managed with a single query...
Loop through your arrays and make a string like:

WHERE (adid=$adid[1] AND mediaid=$mediaid[1] and between yesterday and today)
OR (adid=$adid[2] AND mediaid=$mediaid[2] and between yesterday and today )
etc:

Then

Select DownloadID, adid, mediaid, count(DownloadID) FROM downlaod
WHERE (adid=$adid[1] AND mediaid=$mediaid[1] and between yesterday and today)
OR (adid=$adid[2] AND mediaid=$mediaid[2] and between yesterday and today )etc:
group by DownloadID, adid, mediaid

[seangkb]

14 days will give 5882 records...

i get what u mean, thanx a lot and i will try it out.

thanx you!!

regards
GKB