MYSQL :: Reducing File Size (optimize Table)

Size Of Indexes Versus Data File Size
I am adding indexes to a table with about 200,000 records. Every field indexed adds about 2MB to the index file (*.MYI).

The index file is around 8MB and the data file is around 10MB.

Is there some optimum ratio I need to know about?

(I have examined the queries and am indexing field used in WHERE, ORDER BY and GROUP BY clauses.)

Key File Size
We have loaded a very large dataset and received an error message stating that the key
file is full. How do you go about increasing the allowable size of the key file? I assume by the key file the error message is referring to the .MYI file. Is this correct?

Bin Log File Size
My bin log files get huge and the only time it creates a new one is when the server restarts. Is there a way to make it create a new bin file every week or when it reaches a certain size?

Check Table And Repair Versus Optimize Table
I am confused about the difference between running a CHECK table and REPAIR(if necessary) versus running an OPTIMIZE table. It's not very clear in the documentation, but seems that OPTIMIZE table does a check and repair in addition to the other functions performed by optimize.

I have inherited some legacy code that does a check and repair and optionally follows it with an optimize. That seems redundant to me and I would like to streamline the code to make it an "either-or" selection.

Database File Size
I running MySQL locally and have the following dir setup

C:wampmysqldatamike - This is where the tables for the database
mike are stored

C:wampmysqldata - This is where I think the database data is stored.

Basically the file in the data dir. (ibdata1) is 10,240kb in size.
When I update a table in the mike database the file size of ibdata1
doesn't increase and I don't know why?

Optimize Table
Optimize table in mysql is nice!! but seems to require table names.
Any way I can run it for my entire db? I like to do this in a batch
process from time to time.

FYI, I was having SERIOUS perf. problems on my box, until I optimized
the tables that had huge number of rows deleted. Don’t believe the
mysql documentation that this is not needed.

OPTIMIZE TABLE
Is it safe if the 'OPTIMIZE TALBE [table_name]' query is issued frequently(every 30 second)?

The MySQL manual,[13.5.2.5. OPTIMIZE TABLE Syntax], says,
"In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables."

202 File Size Limit Exceeded
I am setting up a huge database in mysql, and I get the above error in
Linux. I believe it is related to the size of one of my tables, which
is 4,294,966,772 bytes in size.

Estimate Size Of Index File
I have designed some tables and need to estimate how much space they will need as they grow. I can estimate how many rows each table will likely grow to, per day and per month, and can predict the size of the MYD file. But I can't seem to find any information on how to estimate the size of the index files (MYI)? I am using MyISAM.

Blob Cutting File Size
I am trying to upload a binary file into mySQL
However, when I upload the file, only 28 bytes get into the dB.
does anyone know what's happening here? The file just contains raw binary data. I tried uploading a zip file too.. and out of 7 KB only 421 bytes got uploaded..

Perl Code:PHP Code:

my $sth= $dbh->prepare(
"INSERT INTO
blobtest(fileType,fileName,fileSize,fileData)".
" VALUES ( ?,?,?,?) ");

Optimize Table With LOCAL Arg
I am running mysql 4.0.16. Optimize table does not work with LOCAL or
NO_WRITE_TO_BINLOG options, and gives me mysql error. Any ideas?

Stop Optimize Table
While trying to shorten my files, I tried optimize table - later I
found that I need:

mysqlcheck -u root -p[password[ --all-databases --analyze --optimize
(thanks to Markus Popp).

But it gives this error:

mysqlcheck: Got error: 1044: Access denied for user 'root'@'localhost'
to database 'information_schema' when executing 'OPTIMIZE TABLE ... '

How can I stop this?
The optimise command seem to be in the way (and since I started it, my
computer has got pretty slow)

Size Of Ibdata1 File Keep Increasing And Never Decrease
I have a strange problem with innodb tables. I have about 10 innodb
tables, a few of which can have tens of thousands of entries. These
huge tables are clean up regularly.

But the size of ibdata1 remained same even after the tables were
dropped. Funny thing is that as new entries are added into the newly
created tables, the size of ibdata1 increases. And eventually exhausts
the disk space.

I don't do any strange things with MySQL. Will indexing cause this
kind of problem?

Changing Innodb Log File Size - Errors?
I am using MySQL version 5.0 on Linux Fedora Core 4.

I recently got this error message:

060609 11:06:29 InnoDB: ERROR: the age of the last checkpoint is
9433557,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

which I interpreted as my log files at /var/lib/mysql/ib_logfile0 and
/var/lib/mysql/ib_logfile1 being too small (still at default at 5M). I
looked up some resources and they recommended setting the log file size
to about 25% of my buffer size, which I had set to 256MB.

So I added the line in /etc/my.cnf :
innodb_log_file_size=50M

But on restart i get the error:
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880
bytes
InnoDB: than specified in the .cnf file 0 52428800 bytes!

and my queries started failing too:
060609 12:05:39 [ERROR] /usr/sbin/mysqld: Incorrect information in
file: './db/available.frm'
060609 12:05:39 [ERROR] /usr/sbin/mysqld: Incorrect information in
file: './db/available.frm'
060609 12:05:39 [ERROR] /usr/sbin/mysqld: Incorrect information in
file: './db/site_info.frm'

I reverted my config back to a default log file size, and everything
went back to normal. Is there a proper procedure that I should follow
for changing the log file size?

How Do I Get Around PhpMyAdmin File Size Upload Limit?
I use phpMyAdmin to upload data into my database tables. However, there is a 2MB file size limit on the files to be uploaded. I have over 100 MB of database content to upload. How can I get around the file size limit and upload larger files into my database tables?

Optimize Table And Forced Sorting....
I've optimised a table but I'd like a table to be optimized and sorted according to the primary key in asc What should I do?

Importing Data Via Text File. Size Limit!
I am running mysql/php on my local machine (xp-pro). I am trying to import a lot of data into the database. I have about 50 thousand records. Here is a example of a recordset.

"13-01-103-007","32835 BRUGGEMAN","Warren Consolidated","95380","95380","94160","100.000","60.000","120.000","0.165","0","0","Apartment ","0","1.00","1307","C ","Brick ","1626","399","2","1626","1.00","0","1","1 Story","1980","A "

The fields are seperated by "," and enclosed by " "" ".
When I use phpmyadmin and limit the insert to about 30 records every thing works fine. But when I try to do the entire batch (50,000) it times out or I get a generic error "page can not be found" .

I noticed in phpmyadmin that the limit of file size is 2048kb. Could this be the problem? If so can I increase the size of the file? Mines is 14.9 MB. Or not have it timed out.

Exporting Database With Putty & PHPMyAdmin File Size Question?
I have finally worked out how to back up the database using Putty. I just need a couple of questions answering as I am a bit baffled.

When I use Putty to export my database the database size comes out as 55MB
When I use PHPMyAdmin to export my database the database size comes out 89MB

Table Size
I know this is probably a dumb question and mostly because i'm too lazy to research it. But if anybody knows off the top of their head it will be muchly appreciated.

How big can a mysql table be while running on an NTFS partition such as Win 2000 server or XP?

I know the max table size in mysql is 4 terabytes but what is the maximum file size on NTFS.

Max Table Size
Basically whats the maximum table size in GB that MySQL allows? We are
developing an application that streams content - The streams are created
by a CMS from a raw file that gets uploaded by a user - These raw files
are stored in the db by the app. In Oracle i'm used to creating mutiple
datafiles that make up a tablespace and so no singe data file ever
really gets that big. Is the same approach applicable in MySQL and if so
how big can you go?

Table Size
How many fields can be in a table before it begins to slow search requests and the server speed?
What is a good number of fields per table

Max Table Size
Can someone please tale my what is the max table size and the max value of an INTEGER AUTO INC, in MySql DB.
And if a table as reached to the max whet is the best thing to do, open a new with the same name and rename the old one

Table Size In Mysql
how many records can i put in a mysql table. i need a table with more than
50,000,000 records (just 4 short fields, it's basically a log with
id,action,date,result).

> Mysql Table Size?
i read the mysql manual and it's explained the maximum table size in many
operating system, but i can't find the maximum size for MS window (win32),
what's the maximum table size for win32?

My office want to migrate the database server to mysql, and it's contains a
millions record. Can mysql hold up tables with each have millions record?

Find The Size Of The Table
Is there any function OR any way to find a size of the table?

Let us assume the table contains the 3 rows and 4 columns, if i add the one more column does this increases the table size. How to find the size of the table?

Table Size Limitation
Is there a limitation in the size of table in Windows MySQL Server?

Physical Table Size
I have created a database using some innobase type tables. I had to fill my database with quit a lot of data, to test its efficiency. I have also made calculations of how big my database will be. After calculating the data size (well, you know... INT = 4B, DATE = 3B, VARCHAR(x) = k+1 and so on), I came to a conclusion, that my database will be about 62MB big. (This calculations are quite accurate.)

But my MySQL Administrator tool says, that my database is about 90,2 MB big. The indexes use 11,8MB. Well: 90,2-11,8-62 = 16,4 [MB] of space. That's quite a lot... (18% of the database size). What is MySQL using this space for? Is the innobase type really so spaceconsuming? Where can I read more about it?

Table Size Limit
what is meant by Table Size Limit ?

If we encounter such error related to this issue, how to work around it ?

Table Size Limit
I am using mysql 4.1 with windows 2003. Do the table size has limit in the table? How can I know how many size in my server? If the table size becomes to limit, what can I do to increase the table size? Or can we split the single database file.

Restricting Table Size
Say I want to make sure that a table can not have more than 100 records
- how could I do this from MYSQl or PHP.

Mysql Table Size
i read the mysql manual and it's explained the maximum table size in many
operating system, but i can't find the maximum size for MS window (win32),
what's the maximum table size for win32?
My office want to migrate the database server to mysql, and it's contains a
millions record. Can mysql hold up tables with each have millions record?

Speed, Table Size, And Index
I am trying to understand if table size impacts query speed on an query thats looking up an index value.

Here is an example query:

Select * From Holdings
Where Holdings.user_id=3

Assuming there are 50 records in this table for user_id 3, will there be a difference in query speed if the whole table is 10,000 rows versus 10 million, or will the only difference be in storage size of the user_id index?

Also, if there is a difference, does it only have to do with whether or not the whole table can be cached in memory, or is there more of a fundamental difference in the way the query would operate?

How To Estimate Innodb Table Size?
Innodb maintains extra info for ACID requirements so the data size is much larger than what we get from theoratical analysis: #records* (#bytes per record it should be). So my question is: how to estimate the table size?

How To Measure HEAP Table Size
Can anyone suggest a method of measuring the size of a HEAP/MEMORY-type table? This would be useful to set an appropriate figure for the max_heap_table_size server variable.

I can create the table on disk rather than in memory and check the file size that way, but is there a more direct method?

Finding Table And Database Size
A couple questions about finding the disk space used by a =
table/database:

1) First how can I get the size a table is taking up on disk?
Is it the 'Data_length' field in: SHOW TABLE STATUS FROM db_name LIKE =
'wild';??
If so how can I do a query like the one above and just get Data_length =
returned?
Otherwise what query can I do to find the disk size of a table?
2) Is there a simple query to get the disk size of a database and all of =
it's tables? Or do I need to do the method I use in question one on each =
table in the db and add them up?

Function To Return The Size Of A Database And/or Table
I would like to know if there's any function to return the size of the database or table... I have a PHP script and I would like to display the actual size of my database.

The Total Number Of Locks Exceeds The Lock Table Size
I have a quite large table (about 70 mln rows, MySQL 5.0.18 on a linux box) and when i try to delete some records from it I started getting this message:
Quote:#HY000The total number of locks exceeds the lock table size

Create Virtual Table During Query Or Load Table To Memory From Text File
I have access to my application database via a secure link, so I cannot link a local database to it, only run queries on the remote server and download the result.

The database is missing some useful information, and I cannot add a table at this stage. I would like to achieve the result by creating a virtual table or the like. ...

ERROR 1206 (HY000) At Line 41: "The Total Number Of Locks Exceeds The Lock Table Size"
I'm loading a large file into a table, and I get this error:

"ERROR 1206 (HY000) at line 41: The total number of locks exceeds the lock table size"

I already searched, and the only advice given was to do an explict full-table lock on that table before loading to avoid the row-by-row locks filling up the lock table. Well, I tried that, and it's not helping either.

Here's my log:

What Does OPTIMIZE Do
I use phpMyAdmin and constantly see the OPTIMIZE TABLE option on the structure page with a number of bytes highlighted. You press the button and the bytes are gone! Great!!!

But what exactly did that just delete? Should you optimize or just leave the databases as they are? Is there a chance of corrupting the data in a table when you run this?

Optimize This Sql
optimize this sql

SELECT DISTINCT(ibf_posts.author_id), ibf_topics.* FROM ibf_topics LEFT JOIN ibf_posts ON (ibf_topics.tid=ibf_posts.topic_id AND ibf_posts.author_id=25869) WHERE ibf_topics.forum_id=4 and ibf_topics.approved=1 and (ibf_topics.pinned=1 or ibf_topics.last_post > 0) ORDER BY pinned DESC, last_post DESC LIMIT 0,30 .

Optimize
the following query takes forever on 80,000 records. I was wondering if there was anyone that sees how I could speed this query up.
SELECT c.products_model AS model, SUBSTRING(warehouse_sku FROM 4) AS sku, a.products_id AS products_id, products_viewed
FROM products_description AS a, products AS b, products_to_warehouses AS c
WHERE a.products_id = b.products_id
AND b.products_model=c.products_model
AND warehouse_id = 2
ORDER BY products_viewed DESC;

Optimize And Tuning
i was reading a post from long ago, and suggest for better performance at the mysql server to check the queries adn then make an optimization. i have this db that's eating lots of resources, and usually reaches 90% for the cpu and almost everything is used by the mysql.

i supposse itÂ´s a matter of tuning vars, wich one would you suggest to tackle first, and what others next in an myisam engine

Shorten/optimize This?
I have 3 fields I want to search:

categories table's Title field.
items table's Title and Info fields.

The titles are < 25 chars, and Info < 50 characters.

So, I figured a LIKE '%SEARCH%' is best... is there something else I should do?

Right now I split up each word in the search, so a search for "multiple word search" gives me these two queries:

SELECT Id, Title FROM categories WHERE Title LIKE '%multiple%' OR Title LIKE '%word%' OR Title LIKE '%search%'

SELECT Id, Title, Info FROM items WHERE Title LIKE '%multiple%' OR Title LIKE '%word%' OR Title LIKE '%search%' OR Info LIKE '%multiple%' OR Info LIKE '%word%' OR Info LIKE '%search%'

How Can I Optimize This Query?
tables:
---------
branch:
PK = branch_ID
(has many)
galleries:
PK = gallery_ID
FK = branch_ID
(has many)
photos:
PK = pic_ID
FK = gallery_ID

query:

select x,y,z
from galleries join photos
on photos.gallery_ID = galleries.gallery_ID
where galleries.branch_ID = 1
order by photos.img_weekHits desc,photos.img_hits desc
limit 0,1;
explain says the following:
---------
table: galleries
type: simple
possible keys:3
ref: const
rows: 200
extra: Using where; Using temporary; Using filesort
---------
type: simple
table: photos
ref: galleries.gallery_ID
rows: 35
extra: Using where

I need to run this query for each branch of my website (currently there are 7). the query gets the most popular picture for the week from that branch.

All fields used in where, joins and order by are indexed.

The photos table has 40 000+ rows in it.
The galleries table has 500+ rows in it.

The query takes about 3-500ms on my laptop to run for my busiest branch, and less for less busy branches.

The page that runs the query is cached, so the query is only run every half hour or when new content is added or the cache is flushed.

Is there any better way i can run this query, or alternatively achieve the same result with putting less load on the server?

Can I Optimize This Process?
I have a script that reads data records from MySQL and creates a table of all the records and their subsequent data for viewing. Each page of the script will only show 10 records, but with over 10,000 records, there are many many pages of records... thus I have pagination links at the bottom.

Now for the pagination to work correctly, I have to count the total number of records in the specified query, but to display only the 10 records per page, I have to run the same query a second time and add the LIMIT clause with start and display limit parameters. So right now I am calling the same query essentially twice, only the second time I specify the LIMIT clause. These queries are rather huge with multiple joins and many many columns of data.

Is there any way I can optimize this process and get the total record count of the specified query for pagination purposes, but also be able to limit the start and display count for each page as aforementioned. Or is this something I just have to use 2 seperate queries for?