Jump to content

Handling php with large database


ascentlogic

Recommended Posts

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)?

Link to comment
https://forums.phpfreaks.com/topic/211819-handling-php-with-large-database/
Share on other sites

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

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.