SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jan 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help on mysql

    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....

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please do a SHOW CREATE TABLE for the table

    also please show the slow query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is the show create

    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'

    as for the query..its just any random select statement...problem is i can't even browse the data...

    thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i don't see any indexes

    if you are running random select statements, you should at least have a few random indexes...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i only have one index which is the "name" column.Should i create another index for userid because it is no longer the primary key...i don't need to create index for primary key field right?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that's why i wanted to see your query

    whatever is in the WHERE clause should probably have an index

    a primary key does not need an extra index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jan 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks alot..i will go add an index for the userid field and see how it goes..didn't know that indexing have such a big impact on the performace..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •