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

insufficient information

please show the table layout

please show the query that didn't work

please show the results of this query --

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

please show the table layout

please show the results of this query --

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

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

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

that's goint to be way more complicated that a simple SELECT COUNT(*) query

yes..

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

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.