Handling php with large database

[ascentlogic]

Hi, i am php programmer , i need help from php expert to create php apllication for large database .

I have database table called "profiles" which contains millions(1.5 to 2 million) of profile of the business companies. This table has 10 fields and there is one field named as "bname" which is name of company , i made this column full-text index for full-text search .

 

Now , i have to use this table for profile searching (using full-text search), profiles within particular cities , profiles within particular categories etc. This table contains millions of records so it will take lots of time for searching and fetching the reocrd(s) from this table.

Can anybody help me that how can i manage this large table to improve the performance and fast searching with php ? Is there any other technique (algorithm) to manage large database (like facebook,twiiter,orkut)?

[Mchl]

2 million rows is not large at all, provided you configured your server well and structured your data properly.

There's not much more we can tell without seeing your table structure and sample query.

[ascentlogic]

Hi, i am php programmer , i need help from php expert to create php apllication for large database .

I have database table called "profiles" which contains billions(1.5 to 2 billion) of profile of the business companies. This table has 10 fields and there is one field named as "name" which is name of company , i made this column full-text index for full-text search .

 

Now , i have to use this table for profile searching (using full-text search), profiles within particular cities , profiles within particular categories etc. This table contains billions of records so it will take lots of time for searching and fetching the reocrd(s) from this table.

Can anybody help me that how can i manage this large table to  improve the performance and fast searching with php ? Is there any other technique (algorithm) to manage large database (like facebook,twiiter,orkut)

 

following is the table structure

 

CREATE TABLE IF NOT EXISTS `profiles` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `cityid` int(11) NOT NULL,

  `cid` int(11) NOT NULL,

  `name` varchar(200) NOT NULL,

  `phone` varchar(20) NOT NULL,

  `fax` varchar(20) NOT NULL,

  `address` varchar(1000) NOT NULL,

  `city` varchar(1000) NOT NULL,

  `state` varchar(100) NOT NULL,

  `zip` varchar(20) NOT NULL,

  `county` varchar(100) NOT NULL,

  `contact` varchar(100) NOT NULL,

  `gender` varchar(10) NOT NULL,

  `jobtitle` varchar(1000) NOT NULL,

  `website` varchar(100) NOT NULL,

  `email` varchar(100) NOT NULL,

  `emplayees` int(11) NOT NULL,

  `sales` int(100) NOT NULL,

  `type` varchar(1000) NOT NULL,

  `maincat` varchar(1000) NOT NULL,

  `url` varchar(500) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `city` (`city`),

  KEY `cityid` (`cityid`),

  KEY `cid` (`cid`),

  FULLTEXT KEY `name` (`name`)

)

 

Queries are :

1) select p.id,p.name,p.address,p.cityid,p.url,c.city,s.state,match(p.name) against('keywords') as score from profiles p,cities c,states s where 1=1 and p.cityid=c.id and c.stateid=s.stateid and match(p.name) against('keywords' in boolean mode) order by score desc

 

2) select p.id,p.name,p.address,p.cityid,p.url,c.city,s.state from profiles p,cities c,states s where 1=1 and p.cityid=c.id and c.stateid=s.stateid and cid=2 order by p.name asc

 

3) select p.id,p.name,p.address,p.cityid,p.url,c.city,s.state from profiles p,cities c,states s where 1=1 and p.cityid=c.id and c.stateid=s.stateid and s.stateid=33 and cityid=7810 order by p.name asc

 

[ascentlogic]

I made mistake , there is not millions of records , there is billions of records ,  i am not able to edit this post so i created new post with detials of table structures http://www.phpfreaks.com/forums/index.php/topic,308438.0.html

[Alex]

Please don't create multiple topics for the same issue. Just posting the additional information in another post within the same topic is completely fine. I've merged the two topics for you.