[php/mysql] Dupletten entfernen nach Kriterium

[lockdoc]

Hallo Allerseits,

ich habe folgende Tabelle

id
fk_keyword_id
keyword_count
url_tld
...
is_bl_double   # soll aufzeigen, dass dieser record als doublette gefunden wurde
...

In dieser Tabelle sind ein paar hundert Tausend urls drinne.
Zu jeder Url gibt es ein keyword und einen counter wie oft dieses keyword gefunden worden ist. Jetzt ist es nun auch so dass es ein und die selbe url unter verschiedenen keywords gibt.
Ich möchte nun die doppelten urls löschen, ABER
Es soll die url drin bleiben, die den höchsten keywordcount hat.

Mein Allererster Ansatz sieht so aus:

	$sql	= new MySQL();
		
	$data	= $sql->fetch('SELECT id, url_tld, keyword_count FROM '.TBL_DATA);
	$size	= sizeof($data);
	
	for ($i=0; $i<$size; $i++)
	{
		$url	= $data[$i]['url_tld'];
		$tmp	= $sql->fetch('SELECT id, keyword_count FROM '.TBL_DATA.' WHERE url_tld=\''.$url.'\' AND is_bl_double=0 ORDER BY keyword_count DESC');
		
		if ( sizeof($tmp) > 1 )
		{
			// $j=0 is the entry with the highest keyword count
			for ($j=1; $j<sizeof($tmp); $j++)
			{
				$sql->updateRow(TBL_DATA, array('is_bl_double' => 1), $tmp[$j]['id']);
			}
		}
	}

Allerdings ist das ziemlich unperformant und dauert ein paar Stunden.Da ich das regelmäßig machen muss hatte ich das umgeschrieben.

	$sql	= new MySQL();
	
	$total	= $sql->count(TBL_DATA);
	$limit	= 100;
	$runs	= ceil($total/$limit);
	
	for ($i=0; $i<$runs; $i++)
	{
		$data	= $sql->fetch('SELECT
									id,
									url_tld,
									keyword_count
								FROM
									'.TBL_DATA.'
								WHERE
									is_bl_double=0
								LIMIT '.
									($limit*$i).', '.$limit);
									
		for ($j=0; $j<sizeof($data); $j++)
		{
			$url = $data[$i]['url_tld'];
			$tmp = $sql->fetch('SELECT
									id,
									keyword_count
								FROM
									'.TBL_DATA.'
								WHERE
									url_tld=\''.$url.'\'
								AND
									is_bl_double=0
								ORDER BY
									keyword_count DESC');
									
			if ( sizeof($tmp)>1 )
				// $k=0 is the entry with the highest keyword count
				for ($k=1; $k<sizeof($tmp); $k++)
					$sql->updateRow(TBL_DATA, array('is_bl_double' => 1), $tmp[$k]['id']);
		}
	}

Diese Version liegt jetzt bei ca. 22sek pro Hundert rows zu bereinigen. Zudem wird auch die Anzahl der zu bereinigenden Rows immer kleiner.
Aber irgendwas läuft noch nicht hundert pro rund. Komme auch erst zum nachprüfen wenn der morgen mal durchgelaufen ist.


Ich hab mich jetzt aber gefragt ob ich das nicht noch besser machen könnte:

Eigentlich wollte ich nach urls selektieren via GROUP BY aber hier weiß ich nicht wie ich mysql sagen soll, dass er GROUP BY url_tld machen soll und dann einfach auslesen und in eine andere tabelle reinpappen, aber er soll dieses GROUP BY nur die mit dem höchsten keyword_count machen.
Any Idea? Oder nen Ansatz?

 

[nagel]

prepared statements währen schneller

http://www.php.net/manual/de/pdo.prepared-statements.php

 

[indy]

Noch schneller wäre es, das in einer Abfrage zu machen.

So long...

Der Indy

 

[lockdoc]

@indy: Ja das wollte ich ja tun, nur leider bin ich da ein wenig überfragt, wie ich das mache, dass auch der keyword_count mit einbezogen wird.

'SELECT * FROM DATA GROUP BY url_tld'

Damit kriege ich ja dann jeweils nur 1 Eintrag pro url, aber hier wird nicht der keyword_count genutzt.

 

[nagel]

select * from table a WHERE keyword_count = (Select MAX(keyword_count) from table b where url_tld = url_tld);

 

[lockdoc]

Ich habe das jetzt so eingebaut

SELECT
	*
FROM
	data AS a
WHERE
	keyword_count = ( SELECT MAX(keyword_count) FROM data AS b WHERE a.id = b.id AND a.url_tld = b.url_tld);

Allerdings gibt er mir alle rows zurück

 

[nagel]

_