Hello,
I have a very serious problem.
I hope you can give me some advice, that would be very appreciated.
I have a mysql query like this
SELECT PartnerID,CampaignID,BrowserID,count(DISTINCT IP)
FROM tblData20020930
WHERE EventType='Impression' AND
PartnerID!=0
GROUP BY PartnerID,CampaignID,BrowserID
that takes forever to run. When I check the processlist
of mysql threads that query has status "Sending data".
It says like that for hours.
The tblData20020930 has about 2,500,000 rows in it.
It has keys(indexes) set on PartnerID,CampaignID, BrowserID,EventType.
mysql> desc tblData20020930;
+----------------+-----------------------------------+------+-----+---------
---+-------+
| Field | Type | Null | Key |
Default | Extra |
+----------------+-----------------------------------+------+-----+---------
---+-------+
| PartnerID | int(10) unsigned | | MUL | 0
| |
| AdNetworkID | int(10) unsigned | | MUL | 0
| |
| EtimeStamp | char(20) | | |
| |
| ReferringURL | char(200) | YES | | NULL
| |
| IP | char(15) | | |
| |
| RemoteHost | char(200) | | |
| |
| CampaignID | int(10) unsigned | | MUL | 0
| |
| CountryID | int(10) unsigned | | MUL | 0
| |
| OSID | int(10) unsigned | | MUL | 0
| |
| BrowserID | int(10) unsigned | | MUL | 0
| |
| TopLevelID | int(10) unsigned | | MUL | 0
| |
| EventType | enum('Click','Impression','Sale') | | MUL |
Impression | |
| UserType | enum('Partner','AdNetwork') | | MUL |
Partner | |
| ISPID | int(10) unsigned | | | 0
| |
| HODID | int(10) unsigned | | | 0
| |
| BannerLocation | char(5) | YES | | NULL
| |
+----------------+-----------------------------------+------+-----+---------
---+-------+
16 rows in set (0.00 sec)
I dont understand why is stays in "Sending data" mode for so long.
Nobody else is accessing that table, it is not locked, the database and
client are on the same machine. I tried using mysql_use_result()
instead of mysql_store_result() for getting the result set with no
improvements.
The situation is critical... I'd appreciate if you could
provide any type of help/advice.
Regards,
BK
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
