Page 1 of 1

Need help with page timeout issue on reading a large table

Posted: Mon Jul 23, 2007 3:49 am
by Kadanis
Hi

Basically, its a tracking page for clicks on microsites. There are 2 sql statements on the page to get the data sorted.

The first

Code: Select all

SELECT Count(tbltracking.LID)
FROM tbltracking 
WHERE CID = '1127' AND tbltracking.LID <> '0';
Gets a count of the records for the pagination to be constructed.

The second gets a selection of data based on which page and the number of items per page that are selected. For the example page 1 with 100 records is shown.

Code: Select all

SELECT tbltracking.CID, tbltracking.RID,tbltracking.LID, Count(tbltracking.LID), tbllinks.LinkName 
FROM tbltracking INNER JOIN tbllinks 
ON tbltracking.LID = tbllinks.LID 
WHERE CID = '1127' AND tbltracking.LID <> '0' GROUP BY tbltracking.RID LIMIT 0,100
If I run these queries directly in the database, via MySQL front, they successfully complete and return the required data within 1second or so.


If the data return is relative small, 100's or 1000's of records then the page works perfectly, but we have one client that has massive traffic so their tracking generates data in 10's of thousands if not more. In their case the page always timeouts without returning the data.

Can anyone suggest how I can optimise this in some way so that no matter the recordset size the data can be returned.

It's confusing the hell out of me, as I though the LIMIT command would mean only 100 records were present at any one time, therefore there should be no timeout...

Thanks in advance

Posted: Mon Jul 23, 2007 4:48 am
by volka
please try

Code: Select all

EXPLAIN SELECT Count(tbltracking.LID)
FROM tbltracking
WHERE CID = '1127' AND tbltracking.LID <> '0'
and

Code: Select all

EXPLAIN SELECT tbltracking.CID, tbltracking.RID,tbltracking.LID, Count(tbltracking.LID), tbllinks.LinkName
FROM tbltracking INNER JOIN tbllinks
ON tbltracking.LID = tbllinks.LID
WHERE CID = '1127' AND tbltracking.LID <> '0' GROUP BY tbltracking.RID LIMIT 0,100
and post the result.

Posted: Mon Jul 23, 2007 5:46 am
by Kadanis
for the first query i get


ID = 1
SELECT_TYPE = SIMPLE
TABLE = tbltracking
TYPE = index
POSSIBLE_KEYS = blank
KEY = ID_2
KEY_LEN = 35
REF = blank
ROWS = 661911
EXTRA = Using Where; Using Index

for the second query i get

ID = 1
SELECT_TYPE = SIMPLE
TABLE = tbltracking
TYPE = index
POSSIBLE_KEYS = blank
KEY = ID_2
KEY_LEN = 35
REF = blank
ROWS = 661913
EXTRA = Using Where; Using Index; Using temporary; Using filesort

and

ID = 1
SELECT_TYPE = SIMPLE
TABLE = tbllinks
TYPE = eq_ref
POSSIBLE_KEYS = PRIMARY, LID, LID_2
KEY = PRIMARY
KEY_LEN = 4
REF = db.tbltracking.LID
ROWS = 1
EXTRA = Using Where

Hope that helps, not used it before so not 100% on some of this.

Posted: Mon Jul 23, 2007 6:03 am
by volka
Ok, there's an index set to support the query. Sorry, I'm clueless then.

Posted: Mon Jul 23, 2007 6:54 am
by superdezign
Yes, the limit should aid your query to stop it from taking so long because it will automatically stop after 100 records, but I believe the COUNT() function is still running through a lot more than 100 records, still leaving you with the problem of reading too much of the table. How does the query fare when the COUNT() function is removed from it?

Posted: Mon Jul 23, 2007 7:04 am
by Kadanis
if i comment out the Count query and use a constant for the pagination code, then the page loads. its very slow but it loads.

Posted: Mon Jul 23, 2007 7:36 am
by superdezign
Kadanis wrote:if i comment out the Count query and use a constant for the pagination code, then the page loads. its very slow but it loads.
Yeah, what you're going to want to do is handle your pagination in a separate query so that one query isn't doing it all. However, since this is a slow process, you may way to save these results somewhere to ease the load so that you get the results once, and from then on just select from the saved data. Very useful for pagination, as you don't have to run the query over and over from page to page.