[SOLVED] Selecting every Nth record

[cooldude832]

Barand this is another challenge for you and I have an idea on it I'll share below

 

I'm using the same database as posted in http://www.phpfreaks.com/forums/index.php/topic,208130.0.html  adding in my urls_checks table which stores the cURL response data for each url during the cron job runs

 

(UrlCheckID PK, BatchID, UrlID, Response codes....)

 

I want to select every X row from the Urls_Checks table table where UrlID= $_GET['UrlID']

 

I want to take the data in this table and create graphs and statistical analysis using every Xth row (row 1,5,10,15,20,25,etc.) based on what creates the same number of data points on the graph (meaning if I have 10 thousand records for 1 url and I want 100 data points I want every 100th data point.)

 

I rather not have to create a temporary table if that is all possible, and differently not want to take all 10 thousand rows into php manage them just to work with 1/100th of that.

 

My Idea uses a count(*) selection to get the total number of rows in the query, and then using a Join with a pseudo table creation get the highest UrlCheckID num and lowest and from there I have a range but don't know what to do from there

[cooldude832]

well I played around in phpmyadmin and came up with

SET @n=5, @i=5;

SELECT IF(MOD(@i,@n)=0,urls_checks.CheckID ,NULL) as CheckID,
@i := @i+1
From `urls_checks`
Where UrlID = '10'

 

Which returns

CheckID 	@i := @i + 1
1 	6
NULL 	7
NULL 	8
NULL 	9
NULL 	10
7 	11
NULL 	12
NULL 	13
NULL 	14
NULL 	15
29 	16
NULL 	17
NULL 	18
NULL 	19
NULL 	20
134 	21
NULL 	22
NULL 	23
NULL 	24
NULL 	25
259 	26
NULL 	27
NULL 	28
NULL 	29
NULL 	30
384 	31
NULL 	32
NULL 	33
NULL 	34

 

Now is there a fast way I can drop all the rows that are "NULL"

[cooldude832]

New Idea I came up with

SET @n=5, @i=0;


SELECT urls_checks.UrlID, urls_checks.CheckID, urls_checks.Date
From `urls_checks`
Where UrlID = '10' AND
urls_checks.CheckID IN(

Select	IF(MOD(@i,@n)=0,urls_checks.CheckID ,NULL), @i := @i+1 as ""
) 

 

But modifying @i in that method causes a error of

#1241 - Operand should contain 1 column(s)

as I expected is there a way I can add to 1 without addin ga column?

 

[cooldude832]

Another effort but now I get either

 

Column Unknown or Table unknown using

SET @n=5, @i=0;

SELECT 
urls_checks.UrlID,
urls_checks.CheckID,
urls_checks.Date,
GROUP_CONCAT(X.CheckID) as Good_Checks,
GROUP_CONCAT(urls_checks.CheckID) as All_Checks
From `urls_checks`
JOIN(
Select IF(MOD(@i,@n)=0,CheckID ,NULL) as CheckID, @i := @i+1  from `urls_checks`
)	as X USING (CheckID)
Where urls_checks.UrlID = '10'  and urls_checks.CheckID IN(Select CheckID from `X`)
GROUP BY urls_checks.UrlID
LIMIT 100";

or by replacing the IN clause part with my GOOD_Checks

I've also notice th ecolum ALL_Checks isn't correct (it is what ever Good_Checks is)  however Good_Checks is what I want.

[Barand]

Couldn't you use something simpler like

 

SELECT * FROM `urls_checks`

WHERE CheckID MOD 5 = 1

[cooldude832]

No because CheckID is the primray Key of the table that includes records for every single URL not just that single URL.

But u gave me another idea, but it uses a temp table.

(I select every row with that UrlID and put it in a temp table with a a new primary key that has the indexing structure you assumed.)

 

SELECT CheckID
FROM `urls_checks`
WHERE UrlID = '10'

returns

CheckID
1
2
3
4
6
7
8
9
17
23
29
59

[cooldude832]

okay I gave up and went to a Tmp Table structure but I need to refine it (the query takes 3 seconds on avg)

 

refine if you can

SET @n =5,
@i =0;# MySQL returned an empty result set (i.e. zero rows).
CREATE TEMPORARY TABLE `url_data` SELECT IF( MOD( @i , @n ) =0, CheckID, 'a' ) AS CheckID, @i := @i +1
FROM `urls_checks`
WHERE UrlID = '10'
ORDER BY urls_checks.Date ASC
LIMIT 100 ;# Affected rows: 100
DELETE FROM `url_data` WHERE CheckID = 'a';# Affected rows: 80
SELECT urls_checks.UrlID, urls_checks.CheckID, urls_checks.Date
FROM `urls_checks`
WHERE CheckID
IN (

SELECT CheckID
FROM `url_data`
);

 

 

The part that lags it up is the IN(Select CheckID...) portion of the last select query.

[cooldude832]

Solved it using a JOIN let me know how I can improve it cause I still think its a sucky way to do it

SET @n =5,
@i =0;
CREATE TEMPORARY TABLE `url_data` SELECT IF( MOD( @i , @n ) =0, CheckID, 'a' ) AS CheckID, @i := @i +1
FROM `urls_checks`
WHERE UrlID = '10'
ORDER BY urls_checks.Date ASC
LIMIT 100 ;
DELETE FROM `url_data` WHERE CheckID = 'a';
SELECT urls_checks.UrlID, urls_checks.CheckID, urls_checks.Date
FROM `url_data`
JOIN urls_checks
USING ( CheckID ) ;
DROP TABLE `url_data` ;

 

 

Edit:

 

Problem I'm having now is when I apply an Order BY to the query (ORDER BY url_data.CheckID) it treats the value as a Varchar instead of an integer.

 

Anyone have a good example of building tmp tables off a select so that I can make some better deffinitions.

[fenway]

What was wrong with Barand's idea... except use a user variable as row counter, and check MOD 5 of that?

[cooldude832]

doesn't that fail if the IDs are mixed between different urls

 

His query be

SELECT *
FROM `urls_checks`
WHERE UrlID = '10'
AND CheckID Mod5 =1
LIMIT 0 , 30 

returned 0 rows.

[Barand]

probably returned an error too

[cooldude832]

I'm gonna go with what I wrote for now cause ti works fairly well and has a quick response time.

 

I can probably tidy it up but its alright for now.