do I need to use fulltext search for one column?

Hello,

I have a table with these two important columns:
"folder" (int) and domain_name (varchar(255).

I accept there will be 1mio records some time and I need to search fast inside the column domain_name for a match like:

Code:

SELECT folder FROM table WHERE domain_name = 'www.webdigity.com';

is this a problem? or is it better to use a fulltext search?

[Join us @ facebook]

I am not sure for the full text, but if you want to make this query faster you can break the domain to 3 fields (subdomain, domain, tld) and make them index

I am not sure for the full text, but if you want to make this query faster you can break the domain to 3 fields (subdomain, domain, tld) and make them index

This is a really good idea thanks

[Join us @ facebook]

Welcome

dou you think that this way 1-2 mio. records are not a problem?

[Join us @ facebook]

I used this type in a database of 2 mil and it was ok.

Of course for big tables using mysql 5 (instead of 4) is allways better

I see another problem, there are this kind of entries possible:
subdomein.domein.nl
www.domein.nl
domein.nl

above its possible to split them while using regex

but what with this kind of domain names:
maniche18.blogs.sapo.pt

I saw another URL's with more dots like
www.domein.wh.at.ev.er

maybe its better to remove the www only?

I used this type in a database of 2 mil and it was ok.

Of course for big tables using mysql 5 (instead of 4) is allways better

yes that's possible if this host works fine (you know which host I'm talking about)

[Join us @ facebook]

If you want to make it better, you should hold a table with all the valid tlds, and associate it with the domain.

This way you will keep the subdomain(s), the domain, and the id of the tld.
This is propably the faster, but needs some work...

If you want to make it better, you should hold a table with all the valid tlds, and associate it with the domain.

This way you will keep the subdomain(s), the domain, and the id of the tld.
This is propably the faster, but needs some work...

yes, maybe this is the best solution... what about index the domain name without www?

[Join us @ facebook]

That would be better, but with no significant reduce in overhead.

I am not sure for the full text, but if you want to make this query faster you can break the domain to 3 fields (subdomain, domain, tld) and make them index

Hey Nick,

I checked this but how do you parse an unkown domain name format like
domain.com
or domain.co.uk
or sub.domain.co.uk

to resolve the three parts?
checking the last part against 280 tld's?

[Join us @ facebook]

First you get the tld by checkink the last characters of the domain with the tlds table,

Then you remove the tld part and split the domain with the . character, like this:

Code:

<?php
$dom = explode ( '.', $domainLeft );
$topDom = array_pop ( $dom );
$restDom = implode ( '.', $dom );

First you get the tld by checkink the last characters of the domain with the tlds table,

somthing like:

SELECT tld FROM table WHERE 'www.domein.co.uk' LIKE %tld

? maybe its too late but I don't get it... splitting all other parts is not the problem...

[Join us @ facebook]

I guess that will work, but I am not really in a mood to think too

Check it out and let me know if it works (or not) so we can solve it tommorow

I guess that will work, but I am not really in a mood to think too

Check it out and let me know if it works (or not) so we can solve it tommorow

Ok I will go to bed now (to get fresh ideas the next day)

[Join us @ facebook]

Good night man, see ya tomorow (now this thread looks more like a chat zone, lol )

I played a little bit more with this domain split.

I think there are more poblems:

example: www.domain.co.uk

there are two mathches in my tld table: co.uk and uk

another problem is the sub domain because this is optional, how does the script knows that?

(by the way the select statemant doesn't work)

[Join us @ facebook]

I will try to make this with pseudo - php code :

Code:

<?php

$domain = 'whatever.com';

$sql->query ( "SELECT tld FROM tlds ORDER BY LENGTH DESC" );//Meaning that it will give first the co.uk and then the uk
while ( $sql->next() )
{
  if ( substr ( $domain, -1, strlen( $sql['tld'] ) = $sql['tld'] )
  {
     //You found the tld
  $domainLeft = substr ( $domain, 0, ( strlen($sql['tld'] - strlen($domain) + 1 )); // It will left the whatever, without the extension and the .
  if ( strpos ( $domainLeft, '.' ) === false ) //It has subdomain
  {
      $subDomain = '';
      $finalDomain = $domainLeft;
  }else{
     $subDomain = explode ( '.', $domainLeft );
     $finalDomain = array_pop ( $subDomain );
     $subDomain = implode ( '.', $subDomain );
  }
  }
}

I am not sure if it is working, but the logic is correct (or I have to drink another coffee to think it better )

Hey Nick,

Thank you that brings back the development time for this feature to only a few hours

after some modification, this is the result:

Code:

<?php
$domain = "mail.finalwebsites.co.uk";

$res = mysql_query("SELECT tld FROM domain_info ORDER BY LENGTH(tld) DESC"); //Meaning that it will give first the co.uk and then the uk
while ($arr = mysql_fetch_assoc($res)) {
$tmp_tld = substr($domain, -strlen($arr['tld']));
if ($tmp_tld == $arr['tld']) { // You found the tld
$tld = $arr['tld'];
$domainLeft = substr($domain, 0, -(strlen($tld) + 1)); // It will left the whatever, without the extension and the .
if (strpos($domainLeft, ".") === false) { // It hasn't a subdomain
$subDomain = "";
$finalDomain = $domainLeft;
} else {
$domain_parts = explode(".", $domainLeft);
$finalDomain = array_pop($domain_parts); // select the domain and remove it from the array
$subDomain = implode(".", $domain_parts); // a subdomain can more then one parts seperated with dot's
}
echo "The tld is: ".$tld."<br>";
echo "the domain name is :".$finalDomain."<br>";
echo "the subdomain is: ";
echo (!empty($subDomain)) ? $subDomain : "n/a";
echo "<br>";
break;
}
}

by the way this function is a rare one (searched already the internet for a while). I like to post this script to the tutorial section, what about the tld table? Maybe we should send this data on request? (to members only)

[Guest]

[62.865]

Msnbot, Googlebot, Yahoo crawler