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
