Results 1 to 10 of 10

Thread: table too big

  1. #1
    Join Date
    Nov 2002
    Posts
    50

    Unanswered: table too big

    Hello everybody

    I have a table which i think got too big that when i use a select command to view records.. it gets stuck and don't show anything

    Can somebody help me out with this problem?.. how can i view the records and see whats in it that it got so big.

    Thanks in Advance
    Deep

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    insufficient information

    please show the table layout

    please show the query that didn't work

    please show the results of this query --
    Code:
    select count(*) from tablename
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Posts
    50
    Quote Originally Posted by r937
    insufficient information

    please show the table layout

    please show the query that didn't work

    please show the results of this query --
    Code:
    select count(*) from tablename
    Thanks for the quick reply

    sorry about the insufficient info

    here's what I do

    - i connect to mysql using mysql -u -p table_name

    - then i use "Select * from table_name;"

    - gets stuck at this point (because that table is too big)

    - used status command and i got this

    Connection id: ######
    Current database: Database_Name
    Current user: user@localhost
    Current pager: stdout
    Using outfile: ''
    Server version: 3.23.58
    Protocol version: 10
    Connection: Localhost via UNIX socket
    Client characterset: latin1
    Server characterset: latin1
    UNIX socket: /var/lib/mysql/mysql.sock

    Threads: 34 Questions: 370451 Slow queries: 92 Opens: 277 Flush tables: 1 Open tables: 64 Queries per second avg: 0.050

    I also notice that in my tmp folder.. i have this #sql6e8_2f52_0.MYD file.. which is over 64 gb .. and its not suppose to be that big.

    I hope that helps
    Deep

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show the table layout

    please show the results of this query --
    Code:
    select count(*) from tablename
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Posts
    50
    Hello

    If i use select count(*) from table_name ... it gets stuck. I can't access that table

    and heres i used describe table_name

    and this is wat i got

    +----------------+------------------------------------------------------------------------------+------+-----+----------------------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------+------------------------------------------------------------------------------+------+-----+----------------------+----------------+
    | SampleID | int(10) unsigned zerofill | | PRI | NULL | auto_increment |
    | Name | varchar(50) | | MUL | | |
    | TemplateType | enum('PCR(ss)','PCR(ds)','Phage','Plasmid(ss)','Pl asmid(ds)','Cosmid','BAC') | | | PCR(ss) | |
    | SeqRunID | int(5) unsigned zerofill | | MUL | 00000 | |
    | Lane | tinyint(2) unsigned | | | 0 | |
    | Min | smallint(6) | YES | | NULL | |
    | Max | smallint(6) | | | 0 | |
    | ProjectID | int(7) unsigned zerofill | | | 0000000 | |
    | Account | varchar(80) | | | | |
    | Cost | tinyint(3) unsigned | | | 0 | |
    | Charge | enum('yes','no') | | | yes | |
    | NoChargeReason | text | YES | | NULL | |
    | Problems | text | YES | | NULL | |
    | Comments | text | | | | |
    | SeSampleID | bigint(20) unsigned zerofill | | | 00000000000000000000 | |
    | admin | varchar(20) | | | | |
    | DateTimeOut | datetime | | | 0000-00-00 00:00:00 | |
    | DateTimeIn | datetime | | | 0000-00-00 00:00:00 | |
    | CD | int(10) unsigned | YES | | NULL | |
    +----------------+------------------------------------------------------------------------------+------+-----+---------------
    Deep

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, i have no idea

    did you try the REPAIR command?

    this exceeds my meagre admin skills

    perhaps someone else will step in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Posts
    50
    That's ok

    Can you tell me how can I delete Last 100 records (the newest records added) added to that table?

    the syntax please?

    Thanks
    Deep

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's goint to be way more complicated that a simple SELECT COUNT(*) query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Posts
    50
    yes..

    but I can't access that table by any select statement... I'm not even sure if it will let me delete nething.
    Deep

  10. #10
    Join Date
    May 2004
    Location
    NJ USA
    Posts
    21
    Did you try doing a select * from tablename LIMIT 20;

    I am assuming your select statement is taking a long time because your table is large. The server is actually trying to retrieve all the results and send them to the client.

    If you really need to delete the last 100 records. Run the SELECT MAX(SampleID ) FROM tablename. (This will certainly take a long time to run. So don't kill it, just let it run for a couple hours or days if need be.)

    You can probably use this result to delete the last 100 records since SampleID is an AUTO_INCREMENT field.
    J.D.
    Database Administrator

Posting Permissions

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