MYSQL :: Disk Usage Increases After Dropping A Large Index?

Index Usage
I have two tables, one is Student, which has studentid, personid, grade, fees as its columns. The other one is Person, which has personid, firstname, lastname, address as its columns. When given a studentid, I need to get the student' info, such as
lastname from the db tables.

I can do it with two queries as the following:

1. mysql> select personid FROM Student where studentid=1;
2. mysql> select lastname FROM Person where personid="the id I got above";

Or I can do it with one query:

3. mysql> select p.lastname from Student s, Person p where s.studentid=1 AND s.personid=p.personid

Both works. However, I can't tell which one is actually faster, since MySQL shows me "1 row in set (0.00 sec)" for all these three queries. Both studentid and personid are primary keys, so they are automatically indexed.

Will both indexes be used in query number 3? I saw people said that

MySQL could use in a query only one index...Does it mean only one index will be used in query number 3? I am wondering which one will give better performance.

Analyze Index Usage
I am running a SELECT query that is taking approx. 200+ seconds to complete. I believe I have the tables indexed properly, but apprarently not. How can I determine what indexes a query is using and evaluate the execution plan (as you would in MSSQL)?

Index For A Very Large Table
This is the first time I try to use MySQL for very large tables.
Up till now I used if for small tables, and was very pleased (great job, guys - thanks).

Experienced users warned me against possible problems with *index* definition for large tables: they claim MySQL indices are stored completely in memory, which is problematic to me : I have a huge table and limited memory size (RAM). I did the math, and the entire index just won't fit into the RAM.

Unfortunatelly it will be a while before management approves hardware improvements.

Could anyone please advise whether MySQL really needs to store the entire index in memory ? Or is there some way to configure this ?

Index For A Very Large Table
Experienced users warned me against possible problems with *index* definition for large tables:
they claim MySQL indices are stored completely in memory, which is problematic to me : I have a huge table and limited memory size (RAM). I did the math, and the entire index just won't fit into the RAM.
Unfortunatelly it will be a while before management approves hardware improvements.

Could anyone please advise whether MySQL really needs to store the entire index in memory ?

Index For A Large Myisam Table (MYD ~=7GB, MYI ~= 7GB)
I have a very large table with 66,836,760 rows (in my world), MYI and MYD files each about 7GB in sizes and even though I've created indexes for all the possible columns, some queries are still slow. I think I read somewhere that for a very large table, there is a way to configure Mysql to store the index file separately just for that table to facilitate the query. How do I do that?

Disk Performance
We have an application which tends to get very disk-io bound, spending large amounts of processor time in "iowait" during select queries.  Given that writes are relatively infrequent, which would the experienced admins recommend for the disk configuration:

Raid5 or Raid10?  Raid0 is even an option, since we have a backup server and could easily add another, but it's not preferable unless the read performance would increase substantially.

The hardware looks like this:

Dell PowerEdge 6650, 4x2.8ghz CPU, 12gb RAM, 4x36gb internal drives, raid 5
Powervault 220S, 14x73gb 15K drives, dual-connected to the server.  Current configuration is Raid5.

MySQL 4.0.18, RHEL3 (2.4.21-27.0.1.ELsmp) and now using the megaraid2 driver.

There is probably room for optimization in the application, but that's currently out of our control.  We may even move to a different database platform, but if MySQL can handle the load I'd just as soon not.

Running MtSQL On A RAM Disk
I am trying to set up the same thing but have been stopped right from the
get go as I can not get a larger than 2G RAM disk going (using a 64 bit
Ubuntu installation).

I am hoping someone who has gone through the basic set up might get me
squared away to start. An offline conversation would be fine and
perhaps save the rest of y'all wading through said basics.

Restore Database From Another Disk
I'm wanting to restore my old database by copying the contents of the old /var/lib/mysql folder to the new server, but this isn't working.

Is there a documented procedure for doing this? I'm using InnoDB and MyISAM.

Disk Quota In MySQL
Is there a way to assign a limit on the size of a specific database? For example database A can take up no more than 500 MB.

One of my databases can potentially grow without any bounds and possibly stop other critical databases from operating. We are using MySQL 4.0.21 on Linux. We use innodb tables.

Data Kept On The Hard Disk
my mysql database went down... had to reinstall it... where is all the data for the tables kept - i got the /var/lib/mysql/ bit but thats only the tables

Database Not Dropping
i'm trying to completely remove the database smf. when i log into mysql as root and type
DROP DATABASE smf;
Query OK, 0 rows affected (0.01 sec)

then type
SHOW DATABASES;
its still there...empty

Dropping Foreign Key
What is the symbol to do for in "ALTER TABLE ... ADD CONSTRAINT symbol
FOREIGN KEY ..."?

In the manual, they say that "SHOW CREATE TABLE" gives the internal id
of the foreign key. Trying to do so, i see that it's not true...

How can I do to drop some foreign keys excepted the "DROP TABLE/CREATE
TABLE" solution because my tables have a lot of records

Dropping Databases
I'd like to delete the "Test" database, and I really don't know how to do that.

Dropping Foreign Key
What is the symbol to do for in "ALTER TABLE ... ADD CONSTRAINT symbol
FOREIGN KEY ..."?

In the manual, they say that "SHOW CREATE TABLE" gives the internal id
of the foreign key. Trying to do so, i see that it's not true.
How can I do to drop some foreign keys excepted the "DROP TABLE/CREATE
TABLE" solution because my tables have a lot of records.

Bridge Dropping
I have setup an odbc bridge from an access db to a mysql db (version 4.1.11) running on fedora core 4.
Everytime i restart the mysql server i have to recreate the user in mysql user table to enable the bridge to reconnect.

Excel Keeps Dropping
I am having problems converting a MySQL database into CSV for MS Excel.
When the file is dumped into Excel, the leading zeros from the primary key are lost. I need to retain these zeros, as the field is 9 digits long. i.e. 011234444 or 001234443 but when I import/ export, i get 11234444 and 1234443.
I have tried "format cells" as text, but this does not work. I know there is a way to pad out the field in c++ so that empty spaces on either the right or left are filled with zeros, not sure how to get this to work in excel though.
P.S. anyone working in the military might recognize these as NIINs.

Data Location On Hard Disk
I have just installed MySQL 5.0 and finished the first test from a database application.

I saw that the data is stored in c:program...MySQl...

I am not happy with that. I want MySQL stores all data in a map of my own choice.

Flush A Heap Table To Disk?
As the subject says, is it possible to flush the contents of a heap
table to disk?

For example if I have data that I want to be inserted in to a heap
table (for optimisation reasons) but I don't want to risk losing *all*
the data if mysql crashes. Is it possible to perform something like a
flush so that if mysql does crash some of the data is saved.

Copying Table To Disk During Execution
I seem to be having an issue with a query's results being paged out to
disk during runtime. I notice in processlist it saying "Copying to tmp
table on disk".

select col1, col2, count(ip) as 'COUNT' from db.table group by 2 order
by 1, 3;

None of the columns are a primary/foreign or an index. This query
returns 92K rows and has taken anywhere from 50 seconds to run to over
1.5 minutes. The table size on disk is around 33MB.

After doing some research i found that i should be changing the
tmp_table_size system variable. Originally it was set to the MySQL
default, 33554432. I set the varaible to 90,000,000 and it still
started paging out to the disk (this was the only query running at the
time). I would think that 90MB in space would have been enough to
handle the query since that would assume over 600K row size.

I actually had some trouble setting the tmp_table_size for the global
variable but was able to change it as a session variable.

I have also tried altering the query_cache variable (which has only
slowed the query down - currently it sits at a size of 50MB) and also
the sort_buffer_size but neither have made a difference.

What is the magic variable that i should be messing with?

Free Disk Space On DELETE
I'm building a huge DB. One particular table can contain as much as 125 million records. I need to DELETE records older than a year. That part works fine but it doesn't free any space on my hard drive (C:/). I tried OPTIMIZE TABLE and it still won't free hard disk space.

Moving Mysql To Another Hard Disk
I have a web based intranet application (php) that uses a mysql database. The server has just had a new drive added to it and am looking for a way to move the database from the current c drive to this new drive.I'm looking for a way to either:

move the entire database to the new drive (in which case do i have to do anything within the code - the database name is still the same and i'll presume another drive is still localhost can)

Can i just make Mysql save data into the new drive (and also move just the old data file to the new drive leaving everything else as it was).

Ibdata1 Is Close To Disk Size
I am running MySQL 4.0.13 with my-medium.cnf (attached) on a Solaris 8, 4x450 Mhz, 4gb RAM. All my tables are InnoDB. Currently my ibdata1 is 1.8 gb. I have about 1 gig space left on the same partition. Also it is excruciatingly slow now. How do prevent the ibdata1 from filling up the disk partition ? How do I increase the speed on my MySQL?

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
query_cache_size = 16M
query_cache_limit = 8M

log-bin
server-id = 1

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

( I had to comment the innodb to stop crashing )

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Flush A Heap Table To Disk
For example if I have data that I want to be inserted in to a heap
table (for optimisation reasons) but I don't want to risk losing *all*
the data if mysql crashes. Is it possible to perform something like a
flush so that if mysql does crash some of the data is saved.

What Are The Rules For Dropping Tables?
I have run into a weird problem. Sometimes when I do this:

DROP TABLE IF EXISTS `This_Is_My_Table`

if won't work, but if I'll do (e.g. without the quotes)

DROP TABLE IF EXISTS This_Is_My_Table

the table will get dropped fine.
So what are the rules? Do I need the quotes?

Do I need the quotes on statements such as alter table `tablename` alter column `column_name`?

Dropping Primary Key/unique Key
I have a problem understanding why MySQL is deleting a unique key instead of
a primary key.

from Documentation: DROP PRIMARY KEY drops the primary index. If no such index
exists, it drops the first UNIQUE index in the table.

When I do it then I get this:

mysql> desc uksample4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | | UNI | 0 | |
| name | char(30) | YES | | NULL | |
| tel | char(20) | | PRI | | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table uksample4 drop primary key ;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc uksample4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | | | 0 | |
| name | char(30) | YES | | NULL | |
| tel | char(20) | | PRI | | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

It deletes the unique key (id) instead of he primary key (tel).

---
Valentin Nils
Internet Technology

E-Mail: nils@knowd.co.jp
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw

Dropping Columns From Table
I have a table in the Storage Engine InnoDB.

How can i drop more than one Field from the table in a single " Alter Table Drop "
Syntax?

ODBC Connection Keeps Dropping Off...
I have a local user on our network running MS Access 2003 (on Win XP
Home), which needs to connect to a Back-End MySQL DB on our Cobalt
Qube 3 Server.

I installed the correct software to run the Access on their Win XP OS,
and installed the ODBC MySQL Driver 3.51.

When I test the connection in the ODBC panel, CONNECTED !
Then I open MS Access, and great, the app works.

If I then close MS Access and re-open, as soon as I hit a button which
needs data from MySQL then I get an error stating that this IP cannot
connect to the MySQL DB using Password (Yes). I have tried taking the
password out of the DSN, and user account for MySQL, but this makes no
difference.

After the second time trying to open MS Access, it brings up the ODBC
Admin panel, and I have to delete the password and try to connect,
'connection failed', then enter the password 'connected!'. If I then
open Access it runs, if I then exit & re-start the app 'Cannot connect
using IP........., using Password...)

I have double checked all the settings in the DSN, and their user
account for the MySQL DB.

Surely, the PC should be able to connect either all the time, or not
at all, not just once, and then lose the connection

Dropping Primary Key/unique Key
I have a problem understanding why MySQL is deleting a unique key instead of a primary key. from Documentation: DROP PRIMARY KEY drops the primary index. If no such index exists, it drops the first UNIQUE index in the table. When I do it then I get this:

mysql> desc uksample4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | | UNI | 0 | |
| name | char(30) | YES | | NULL | |
| tel | char(20) | | PRI | | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table uksample4 drop primary key ;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc uksample4;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | | | 0 | |
| name | char(30) | YES | | NULL | |
| tel | char(20) | | PRI | | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

It deletes the unique key (id) instead of he primary key (tel).

Did I do something wrong ?

Dropping The Entire Database
Can anybody guide me on How to drop the entire database??

Dropping An Unnamed Constraint
I need to drop a foreign key constraint from a table (or add an "ON DELETE SET DEFAULT") but the constraint is unnamed. Is there anyway to do that?

How Big ( Disk Size ) Would This Database Be: 4.2 Billion Rows
I have a database in MySQL version 5.0.19 that I'm making. Its structure is:

CREATE TABLE `big` (
`keyval` int(10) unsigned zerofill NOT NULL,
`name` varchar(20) default NULL,
`avail` enum('y','n') default NULL,
PRIMARY KEY (`keyval`),
KEY `keyval` (`keyval`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The 'keyval' will be filled with every value from 00000000 to 4294967295 ( 4.3 billion rows ), and assume worse case of 20 characters in each 'name' element and 'y' or 'n' always set in the 'avail' colmn.

MyISAM Table And Sort Order On The Disk
I ran the myisamchk tool with the options --sort-index
--sort-records=1 against an MYI file. Is there something I can use to
confirm what this command did? I would like to see what the sort order
on the disk for each table in my database.

Release Disk Space By Drop Database ?
OS. linux 2.4.22
MYsql : 4.0.18-standard

I created a database and populate 2 big InnoDB tables, the total size
is > 3G. After all testing, I droped the database .

But, the harddisk space is not release to the filesystem, Why, How
could I recover that ?

How Do I Reclaim Disk Space In Data Folder
I have 5 databases:

- mysql
- test
- product1
- product2
- product3

The hard disk on my server was near full, so I decided to delete databases product2 and product3 as I no longer need them. I dropped the databases product2 and I noticed the disk space was not freeed by MySQL after dropping this database. The tables in databases product2 and product3 are INNODB.

What is the best method to reclaim my disk space when dropping entire databases?

Release Disk Space By Drop Database
OS. linux 2.4.22
MYsql : 4.0.18-standard

I created a database and populate 2 big InnoDB tables, the total size
is > 3G. After all testing, I droped the database .

But, the harddisk space is not release to the filesystem, Why, How
could I recover that.

Dropping Table Problem - PHP/MySQL
The following drop query seems to be causing problems:
mysql_query("DROP TABLE IF EXISTS a.products");

Points to note:
I am doing this from another DB, say called 'b'
I need to drop a table in DB 'a' from a script which uses/connects to DB 'b'.
DB 'a' and 'b' both have have the same user with complete permissions.
Both DBs are on the same server.

The problem this seems to cause is DB 'a' seems to 'crash' and I need to restart MySQL.

Should this cause such a "crashing" problem? Is there a better way of doing this that won't cause such a problem?

MySQL version 4.0.something

Dropping Leading Zeros From Zip Code
I imported a csv file using:

LOAD DATA LOCAL INFILE 'address.csv' INTO TABLE data05
FIELDS ENCLOSED BY '"' TERMINATED BY ',';

The zip code field is defined as:

ZIP VARCHAR(10)

When I look at the data the leading zeros are dropped from the zip field for all the zip codes that are (were) 5 characters long - 01234 becomes 1234 - but 9 character codes are ok - 01234-5678 doesn't change.

How do I get around this problem and get the correct zip codes in the db.

Dropping Database From Command Line
I use PHPMyadmin as a front end to administering my databases
but my simple question is how do I, from the command line (winxp prompt)
drop a certain databse.

so once I log into mysql, using mysql -u root,
how would I go about deleting whole databases?

Dropping Table Using Select Result
I have a shopping cart which creates temporary tables in the format of
zorder_<phpsessionid>;

These tables are used to hold a customers order while they shop and
after checking out, it is dropped. The values of the shopping cart are
put together in an HTML table and stored as their history.

What I want to do is to run a cron job to delete the orders which were
left undone once a day at about midnight. Here is my current query

SELECT table_name FROM orders WHERE RIGHT(started,8) < RIGHT(NOW(),8)
AND completed < 1 AND LEFT(started,10) = LEFT(NOW(),10);

I was hoping to do something like this:

DROP TABLE (SELECT table_name FROM orders WHERE RIGHT(started,8) <
RIGHT(NOW(),8) AND completed < 1 AND LEFT(started,10) = LEFT(NOW(),10));

But from my understanding, MySQL does not support nested queries.

Is there a query-based way to do this.

Dropping Multiple MySQL Tables
if there's a way to drop multiple MySQL tables with the same prefix. I installed phpnuke and all their tables start with nuke_ .. I want to drop all the tables using phpMyAdmin.. is there a way to drop multiple tables with same prefix?

Disk Size Of Tables[or Database] In Mysql Or Oracle
how can i know disk memory occupied by database[tables] in mysql or oracle.

/tmp Usage
are there any best practices for /tmp on a std *nix install for MySQL, and if so, what are they ? Also i can't seem to find any more information on what specifically you'd require a LARGE /tmp for in MySQL.

C# Usage
Does anyone know if C# will work with mysql??? I know it does C++, but I don't know if the connectors or whatever are out there for C#

Full Text Index Before Or After Non-clustered Index
In mySQL should any required non-clusterd indexes be built before or
after a full-text index? Either on the same column or different
columns...

MAX() Query Usage
I have a database of marks and I need to find out the maximum mark taken by each student in any subject. For example If am a student and I have got 60 in Language, 70 in Maths, 80 in Science and 90 in Social Studies, I would need the result "90" and the relevant Social Science subject to be displayed so that I know am good at Social Science of all the subjects.

Likewise, my friend got 65 in commerce, 70 in accountancy, 80 in economics and 90 in Applied Maths. As you may see here, the subjects are all different in both these cases. I only need to know this following result

That I am good at Social Studies and
my friend is good in Applied Maths.

The table name is marks. The fields are subject_id, mark_obtained, student_id.

When I try this query.

Select subject_id, max(mark_obtained) from marks group by student_id

In this case, I get the correct maximum mark, but the subject id displayed is incorrect. Can anyone tell me what am doing wrong here?