MYSQL :: OPTIMIZE TABLE

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.

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

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?

Reducing File Size (optimize Table)
I have had a DB where I added a huge amount of test data. Now, after having it processed, the DB contains only the date it will normally need. The point is that I can free up more than 1 GB, which will never be used again...

I was told that "Optimize table my_table" should do the trick. I know that it orders tables etc... and should be able to free up unused space.
Well, if it does, then it does not work for me.

How can I reclaim my 1 GB of disk space?

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?

Optimize Mysql
how to optimize my mysql server. I'm using DELL 2850 with 3Gb cpu and 2Gb RAM, Redhat Enterprise 3 and MySQL 4.1. Every 30-50 minutes mysql will be busy & very slow. Code:

Optimize Problem
I have a large'ish mySQL table, very text heavy (although no fields
are larger than varchar 255). I do a bulk update once a day, which
probably removes a 1/4 of the records, replacing them with fresh
records. I find my table swiftly becomes massively inefficent, with
the reported overhead soon exceeding the actual size of the table.

So my questions are:

(a) why does this happen?
and

(b) is there some programmatic way to perform the OPTIMIZE from my
java updating process?

Deletion And Optimize
I have a summary table which gets updated every half hour. Each half hour I delete the previous record for that day and the new records are summed up at the end of each half hour and gets inserted. This table has no primary/unique keys but only indexes.

After the insertion I do a optimize. This generally takes some time to optimize. Is it good idea to optimize the table after each deletion. I would like like improve the performance of my servers and also the query retrival time.

Optimize Query
What indexes are required to speed up the following query.

SELECT COUNT(*) AS cnt FROM company WHERE state = 'Maharashtra'
AND city = 'MUMBAI' AND companyName REGEXP '^D' GROUP BY parentid;

Optimize Search
If you want to retrieve data in a 10000 rows-table w/ simple WHERE clause, eg: select this where col1=val1 and col2=val2, this'll take forever (I would think, I never worked a database with such capacity.), 'cause all rows are accessed sub-sequentialy.
Are there ways to optimize this search? I read in some articles one way to do it is by indexing, but I don't quite understand how this indexing works,

Optimize Query
I'd like to optimize this query:Code:

SELECT *
FROM `links`
WHERE active = "1" AND mainweight != 0 ORDER BY Rand()*(1/mainweight) LIMIT 5

I have a database of links wich has 3 000 rows. I'd like to select weighted random links from it (mainweight is the weight of the link). On my serwer this query is executing 0.8 sec. When i have 50-100 users on my site this is a problem.

OPTIMIZE Database
I've recently noticed that my database has some overhead, and i would like to go about solving this issue, a short search on this forum noted something about running OPTIMIZE on the database or something like that, is there anyone that may know what exact command i need to run to OPTIMIZE the database (via PHPMyAdmin)

Optimize Problem
I have a large'ish mySQL table, very text heavy (although no fields
are larger than varchar 255). I do a bulk update once a day, which
probably removes a 1/4 of the records, replacing them with fresh
records. I find my table swiftly becomes massively inefficent, with
the reported overhead soon exceeding the actual size of the table.
So my questions are:
(a) why does this happen?
and
(b) is there some programmatic way to perform the OPTIMIZE from my
java updating process?

Optimize - NULL V.s. 0 Or ''
To optimize a fairly large table with variable length fields, is it generally better to use a default value of NULL or an empty string?

ex: for char types, use NULL in stead of ''
or for numeric types, use NULL in stead of 0

if there was a notable advantage/disadvantage to a lot of NULL values in a large table, and if so whether it is due to disk storage requirements or processing time/overhead?

Optimize Question
if the following is the best way to construct a table for searching x, y, and z coordinates
(especially for ranged search retrievals <, >, <=, >=, etc)
Code:

create table map (
x smallint unsigned not null,
y smallint unsigned not null,
z tinyint unsigned not null,
t smallint unsigned not null,
primary key(x, y, z)
)

The t column is simply the data at the xyz coordinate

Optimize Rand() On A Big Database
I am using RAND() and I know it is killing my MySQL performance. My database is currently at 200K rows and growing quickly. Here is a general mysql SELECT that is looped about ten times depending on the page.

PHP

if($drange == "thisweek") {
$subquery = "SELECT articles.title, articles.link,
articles.date, site.site_name
FROM articles,feeds,site
WHERE articles.article_id != $articleid AND
(week(articles.date)=week(now()) AND
year(articles.date)=year(now())) AND
articles.feed_id=feeds.feed_id AND
feeds.sub_id=$subid AND feeds.site_id = site.site_id
ORDER BY RAND() LIMIT 3";
}

I am trying to optimize this to NOT use RAND(). For this query, it is looking at all the latest additions over the past week. I have thought about running this query only selecting the IDs of the rows, then randomly selecting 3 numbers out of the selection and running the query again just to select the rows. But would that be faster?

Optimize SUM() Function With JOIN
SELECT DISTINCTROW marketing.ID, marketing.jobID, marketing.courseID, marketing.type, marketing.date, marketing.mktArea, marketing.firmsType, marketing.indvType, marketing.codes, marketing.specs, marketing.topbox, marketing.finalExport, marketing.notes AS FirstOfnotes, Sum( bookings.amtPaid ) AS SumOfamtPaid
FROM marketing
INNER JOIN bookings ON marketing.ID = bookings.marketingID
GROUP BY marketing.ID, marketing.jobID, marketing.courseID, marketing.type, marketing.date, marketing.mktArea, marketing.firmsType, marketing.indvType, marketing.codes, marketing.specs, marketing.topbox, marketing.finalExport;
EXPLAIN shows:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE bookings ALL NULL NULL NULL NULL 67289 Using temporary; Using filesort
1 SIMPLE marketing eq_ref PRIMARY PRIMARY 4 plt.bookings.marketingID 1
The index is working fine on marketing.ID (the primary key) however i cant find an index which works on the bookings table.
Ive tried adding a key on bookings.marketingID however it shows it up as a possible key but doesnt use it.
Ive also tried creating a composite key of bookings.amtPaid and bookings.marketingID, it uses this key however the number of rows it has to look through stays the same.

I cant see what else I can do to optimize this query.
It takes a while to open as for every marketing.ID it has to work out the SUM of the booking.amtPaid.

Precedence In WHERE Clause - Trying To Optimize
How can I optimize a query like:
SELECT COUNT(*)
FROM ((table1 o
INNER JOIN table2 j ON o.ID = j.ID)
INNER JOIN table3 f ON j.f_ID = f.f_ID)
INNER JOIN table4 c ON c.f_ID = f.f_ID
WHERE j.end = &#55612;&#57200;-00-00'
AND c.dateC > j.startDate
AND c.sub = 'Y'
AND f.l_ID = '$lk_ID'
AND c.show = 'Y'
AND c.dateC BETWEEN '$lastyear' AND '$today'

Do I look at the which parameter in the WHERE clause will have the fewest records and put that first in the WHERE clause? Then the next fewest,...

MySQL Is Slow, Trying To Optimize
It just seems that my system is slow, adding records, etc. I tried to optimize it, saw that there was a my-medium.ini file, read that is was for medium sized system. I replaced it with my.ini (yes I did make a backup, thankfully) and restarted mysql.

Pretty much it hangs. I tried to connect with QueryBrower, did a Select Count(*) and just froze. I admit that I didn't wait for ever, yes I know I Select Count(*) takes a long time but I gave up after waiting 3x as long as I normal did. Also the logs screen froze in administrator.

Optimize Select Statement
I have a big simple table with 3 fields and 700,000 records.

Table: mytable
Field1 Field2 Field3
name1 initial1 1
name2 initial2 0
name3 initial3 0
name4 initial4 0
... ... ...

i like to be able to do a fast search where it can return the first record that has 0 in field3. If i go through the whole table, it takes too long.

Select * from mytable where field3 = 0; <---- it takes too long to do this

i would like to get the record # 2 because this is the first record that field3=0

Usage Of OPTIMIZE Command
I had a table of more than 800'000 rows where I deleted approx 200'000 rows. The documentation says that I shouldn't normally have to use the OPTIMIZE TABLE command, but performance on this table has not improved. Is it a good idea to use it?

Optimize Or Repair Db Tables
I bought a dating script online and been running it for 1 year, has about 1500 profiles, but the site is too slow now. Not about the host because I have godaddy host.
When I ask the script owner, they told me that I need to optimize the db to make tables indexed.
I already tried to go to phpmyadmin, click on all tables, then, select OPTIMIZE table from drop-down list,.... but it does not help. The site is still running too slow to load. I am using high speed intenet.

Optimize A Select For Birthday
I need to select all the clients in my database that celebrate their birthday in the first/last half of the month. The table has an index on the birthday field. I used the next statement:

select buc from clientes where Month(FechaNacimiento)=5 and DAY(FechaNacimiento)<16

to obtain ~13,000 clients, but it takes a while to do it. The explain output says that it will use the index, but estimates the 10 million record scan.
Is there another way to express the WHERE clause to help MySQL optimize the search?

Optimize My Snort MYSQL DB
We are using SNORT IDS which connect to a MYSQL DB, the SNORT IDS has been running for long long time and it got a lot of data in MYSQL DB. Nowadays, the snort web interface has become extremely slow and my friend said it is bcoz there are too much data in MYSQL and MYSQL was not optimized for long time.
May I know how do I do some maintenance or optimize the MYSQL for it to run faster?
I used optimize table, and repair table command for all tables already but the web interface is still slow

Optimize UPDATE Query With Subquery
Users(users_id,...,pictures_average_grade) - 5,000 rows
Pictures(..,average_grade,users_fk) - 50,000 rows

One User can have many pictures. Each picture have average_grade which is being continually changed. Once a day i would like to compute for each user average grade of his pictures and update column. users.pictures_average_grade. I wrote the following query:
UPDATE users AS u SET u.pictures_average_grade= (SELECT AVG(average_grade) FROM pictures WHERE users_fk=u.users_id).

The problem is that the query is being processing very long (after 15 minutes i resing and stop the query).

How To Optimize Mass Data Queries
I am trying to optimize the speed of a query that calculates bias/mean absolute error of forecasts against observations. The forecasts are issued hourly, and make hourly forecasts to the 48th hour. Furthermore, there are about 20 different forecast methods. On top of that, there are 12 different sites. What this adds up to is a really large database. The forecast table has 400K rows, while the observation table as 25K. In order to calculate the above statistics for a given time period, one must pull a lot of data based on the date range. The worst case scenario is that 60,000 rows must be pulled from the forecast table (all stations, methods, and 30 days of fcsts). The problem doesn't seem to be the speed of the query (<.1 sec) as the query is only on the Primary Key (time,station,method), but rather the throughput of the data. I am using PHP to handle the data and present it to the end user. I would estimate that about 15Mb has to go through the pipe before it's processed (ie. converted to an array, and compared to the observations - note, an observation would correspond to say, f010 of a forecast 10 hours earlier, so a parallel join is impossible). The machine I am using is forced to swap in order to execute the script, but it is an old machine (500Mhz processor, 128Mb Ram) that's used for development.

Are there any ways of possibly compressing the inbound data, especially while it is being processed? Right now, I create the big 4-D array before I do the statistics (which are grouped by method), would it help to split up the data feed by method? PHP seems to be most sluggish during the query mysql_query(...), rather than the mysql_fetch_array() looping.

How To Optimize MySQL 4.0.24-log On 10 Mbps Pnet
i hv a database driven portal which derives all the information & data from a database server which is on a different server and web server & database server are connected on 10mbps PNET.(Private Network)

i need to know by what all means and methodologies can i optimize mysql and make my site faster

this site is totally in LAMP environment:

php=4.3.2
mysql = 4.0.24
Apache=2.0....
RHEL3

ANALYZE, OPTIMIZE, CHECK, REPAIR
when to use the ANALYZE, OPTIMIZE, CHECK, REPAIR commands. From what I can tell, ANALYZE and OPTIMIZE are for making the table optimal to increase performance. CHECK and REPAIR are used to find & fix corruptions. ANAYLZE takes a lot less time to run than CHECK.

The questions I have are:

1. What is the general rule of thumb on regular (daily) maintenance of a MySQL DB?

2. Will ANALYZE report table corruptions? If so, I don't even need to call CHECK, I can directly go
do REPAIR?

3. If the anwer to #2 is no, meaning I have to run CHECK TABLE, should I separate the optimization exercises from the find-corruption-and-repair routines?

Optimize MySQL Server Connection
I'm looking to make my site faster, and the first place I want to tackle is my MySQL server. I have no local or shell access to this server, so i'm not sure what I can do, essentially i have access via written code in PHP and phpMyAdmin.
Basically, the first thing i want to do is run a benchmark to see where the server is now, and what commands are slower then others, and the like... I found This. But it is if you run your own server.

How To Optimize Frequency Of Disc Write Operations
I need to optimize frequency of disc write operations for inserted or updated data on MyISAM tables. How can I tune MySQL for large records block disc flushing instead of record-by-record flushing by default?

Some DBMS parameters have effect on disc flushing

("flush", "flush_time", "delay_key_write"), but any of these parameters does not allow to reduce intensity of disc write operations. MySQL 5.0 (Win32) is used.

The Most Optimize Way To Store And Seach Chinese Character In MySQL 4.1
Currently i store the chinese character in unicode decimal format
(&#<number>;). In this way, I can display and search the chinese
character correctly. However, this will take more storage space. Is
this the most optimize way?

Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
?

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...

My goal is to be able to track code violations of the house PER owner.

For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

House 1009283
Address
Past history (link to the following)

House History
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy

For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

then I could look up all code violations by date and associate them with their rightful owner.

==================================================
Option 2:
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...

Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP

$gettray = mysql_query("SELECT trailers.title,
trailers.link,
trailers.movie,
movie.title AS mtitle
FROM trailers,movie
WHERE trailers.movie=movie.word
ORDER BY trailerid
DESC LIMIT 6",$connm);

It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table.

I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet.

If the row does not exist in 'movies', the program than uses the entire trailer title like so

PHP

if($ttray['mtitle']) {
$newttitle = explode("-",$ttray['title']);
$newttitle = array_reverse($newttitle);
$ttitle = $newttitle[0];
$ttitle = $ttray['mtitle'] ."- ". $ttitle;
} else {
$ttitle = $ttray['title'];
}

Thanks
Ryan

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

Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured.

A & B

Table A, has column: Product (product code) as primary key

Table B doesn't.

Apart from that they have the exact same fields.

There's also a column: supplier

I want to

Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples

So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes.

mysql version 4.0.27

Inserting Multiple Items (# Unknown) Into A Table Using Primary Id From Master Table
In my database I have a master table for products using a auto-num id column. One product may have several purchase options (items) which have to be entered into another table.

To enter a new product and items I use a form that gathers the information for the master product and one item. This information is then inserted into the database. For the items I use mysql_insert_id() to get the id into the items table.

My challenge is getting this same id for additional items. Once the user has input the initial product and first item they hit a submit button to call a form for adding any additional items, and this is repeated until all items for that product are entered.

The new items are inserted into the database okay, but always with an id of 0. The mysql_insert_id() no longer works. Is there a way to pass this id from the previous insertion to the new one by passing a variable? or echoing input type"hidden"?

Or even better yet, Im wondering if I can ask the user at the time of entering information for the product how many items that particular product has and then bring up that many form fields to enter multiple items? Can this be done using something like an incremented loop to call form fields?

Multi Table Query, Grab More Than Single Row From Secondary Table
I have a query like so:

PHP Code:

$selectt = mysql_query("SELECT trailers.trailer_id,
trailers.trailer_title,
trailers.description, trailers.file_url,
trailers.runtime, trailers.views,
trailers.rating, trailers.tt, trailers.keyw, trailers.date,
tags.snub,
films.film_title, films.studio, films.keyw, films.image
FROM films,tags,trailers
WHERE trailers.trailer_id=tags.trailer_id
AND trailers.film_id=films.film_id
ORDER BY trailers.trailer_id DESC LIMIT 8",$dbh);

I know this is pretty sloppy, I have multiple 'tags' per trailer ID, and I was hoping to grab all of them in a single query; maybe into an array? So that it shows the row, and then all the tags that belong to it.

Possible?

ALTER TABLE Deleted The Rows - Error: Table Is Full
Today when I tried to insert data in a table i received the error that 'table is full'. On SHOW TABLE STATUS, I noticed the size of table is grown to 4GB (rows 5359211).

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 5359211
Avg_row_length: 801
Data_length: 4294967288
Max_data_length: 4294967295
Index_length: 45783040
Data_free: 0
Auto_increment: 5406252
Create_time: 2007-04-20 18:26:38
Update_time: 2007-08-22 09:55:22
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Here is the table structure.

CREATE TABLE `messages` (
`id` int(11) NOT NULL auto_increment,
`subject` varchar(255) NOT NULL default '',
`message` text NOT NULL,
`attachment_path` varchar(255) default NULL,
`new` tinyint(4) NOT NULL default Ɔ',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

On searching I found the this link.
http://dev.mysql.com/doc/refman/4.1/en/full-table.html

According to manual i executed the following query

MySQL
ALTER TABLE messages MAX_ROWS=20000000000;
I skipped AVG_ROW_LENGTH from the query, I was confused what should be the value for this.

After executing the query, when i check the table status it displayed totally different picture. Now there were only 170109 rows left.

mysql> SHOW TABLE STATUS LIKE 'messages' G
*************************** 1. row ***************************
Name: messages
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 170109
Avg_row_length: 649
Data_length: 110563036
Max_data_length: 281474976710655
Index_length: 1961984
Data_free: 0
Auto_increment: 5409214
Create_time: 2007-08-23 10:07:08
Update_time: 2007-08-23 13:41:57
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=4294967295
Comment:
1 row in set (0.00 sec)
What could be the reason of this rows deletion?

I am on 32 bit system.
MySQL version: 4.1.18-standard-log
Operating System : CentOS 3.x
Memory: 4 GB DDR

3,000,000 Unique INT(11) Low-Hi Ranges In My TABLE, Am I Stuck With FULL TABLE SCAN?
I have a table with 3,000,000 different IP netranges.

each row has a unique range: ie: 1xxxxxxx-1xxxxxxx2
..

I try to find the primary_index when querying WHERE var BETWEEN lowIP and highIPrange.

Since there all of the netranges are unique, it seems like my INDEXES are useless, so the only way to optimize this would be to horizontally partition the data? The query is extremely slow ~1 second in MyISAM, and ~.50 second in MEMORY table format.

I'm assuming the only option is to horizontally position the data to improve performance, am I correct?

ERROR 1005: Can't Create Table './db/table.frm' (errno: 150)
I found the key to solve this problem in:

http://darkstar.ist.utl.pt/mysql/do...onstraints.html

You'll probably need an INDEX for that new foreign key you are declaring in
older versions this isn't neccesary but in latest ones it is a restriction.

You can have more info about the error description if you have root access
by typing
mysql> show innodb status;

LATEST FOREIGN KEY ERROR
------------------------
030807 1:27:10 Error in foreign key constraint of table database/table:
There is no index in the table database/table where the columns appear
as the first columns. Constraint:
foreign key (key) references database.table(key) on delete cascade)
type=innodb

also if you type:
shell> perror 150

you obtain:
150 = Foreign key constraint is incorrectly formed

I hope this to be enough...

Trying Abort One Select Accessing One Table Locked By Lock Table.
I'd like to configure one time to abort one transaction that are accessing one table locked by lock table write command ex:

Transaction A
mysql> start transaction;
mysql> lock table mytable write;

After I'll start new transaction B (another connection)

mysql> start transaction;
mysql> select * from mytable;

It's will become long time to wait...

My transaction B never "die".

I tried to configure innodb_lock_wait_timeout=20 in my.ini without success,