perx Posted March 16, 2008 Share Posted March 16, 2008 Hi, i have some problem regarding mysql that i need help. I have a member table that stores information of my user such as password,username,address,etc. Currently i have about 250k registered user(So thats 250k rows). The table has been running smoothly and querying to this table has been pretty fast. The table doesn't have an auto increment field before and i just assign the primary key to the username. Now, i need to add another auto increment field to it. But after i've done that, the database becomes significantly slower. Querying is super slow and sometimes when i try to browse the data from phpmyadmin, the database just hangs. As a result i have to restart the database. This problem only occurs after i added the auto increment field. I am very sure. Because after i remove it, everything goes back to normal... Can anyone help me here. I am really at a lost here....THANKS Quote Link to comment Share on other sites More sharing options...
Barand Posted March 16, 2008 Share Posted March 16, 2008 Have you changed any indexes? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 17, 2008 Share Posted March 17, 2008 Let's see EXPLAIN, table structure, etc... have you run analyze tables? Quote Link to comment Share on other sites More sharing options...
perx Posted March 17, 2008 Author Share Posted March 17, 2008 this is the show create [pre] CREATE TABLE `member` (\n `id` int(10) NOT NULL auto_increment,\n `userid` varchar(50) NOT NULL default '',\n `icnum` varchar(20) default NULL,\n `gender` char(1) NOT NULL default '',\n `birth` varchar(10) default NULL,\n `entrydate` varchar(5) NOT NULL default '',\n `name` varchar(40) NOT NULL default '',\n `emailservice` char(1) NOT NULL default 'Y',\n `regdate` varchar(10) NOT NULL default '',\n `regip` varchar(15) NOT NULL default '',\n `admin` char(1) default 'N',\n `lastlogin` varchar(10) default NULL,\n `lastloginip` varchar(15) NOT NULL default '',\n `reset` char(1) NOT NULL default 'N',\n `pwd` varchar(64) NOT NULL default '',\n PRIMARY KEY (`id`),\n KEY `name` (`name`)\n ) ENGINE=MyISAM AUTO_INCREMENT=261189 DEFAULT CHARSET=latin1 PACK_KEYS=1 CHECKSUM=1 COMMENT='member'[/pre] currently i only have an index for the name field...now that i've remove the primary key belonging to userid should i include an index for it? Quote Link to comment Share on other sites More sharing options...
perx Posted March 17, 2008 Author Share Posted March 17, 2008 i run analyze table..it says Msg Type - status Msg Text - OK Quote Link to comment Share on other sites More sharing options...
Barand Posted March 17, 2008 Share Posted March 17, 2008 currently i only have an index for the name field...now that i've remove the primary key belonging to userid should i include an index for it? Definitely yes. you could have kept it as the PK. The auto_inc field has to be a key but not necessarily the primary one. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 17, 2008 Share Posted March 17, 2008 I still don't see the query and it's EXPLAIN output. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.