access time / data location

[stiphu]

Hi guys

I've got a few questions about mySQL:

1. I made a java interface to store and get data to a mySQL database over matlab. The interface works very well and very fast, if I don't have a lot of data in the tables. But if I have about 500'000 rows in a table, it takes a lot of time to load the data (about 2-3 sec). Is this normal?

2. I don't really get where the data is stored. I use MySQL Server 5.0 version 5.0.24a. I just see that there is a new folder (if i create a new database) with the name of the scheme and the information of the created tables. But where is the data i store? I just got that the file "ibdata1" is much bigger than before, so the data must be there. But I want to create a new scheme anbur this onto a dvd. I need portable data. Is this possible?

3. How can I erase data? I know how to erase a table or a scheme, but the data isn't really erased, because the file "ibdata1" is still the same size. I think this is maybe the same problem like in Nr.2!

Looking forward to your answers, thanx for your help,
regards Stephan

[mjlogan]

It can vary depending on the power of the PC. Where are you loading the data to? A grid?

I have MySQL 4.1 installed in this directory.

C:\Program Files\MySQL\MySQL Server 4.1\

and in there, there is a folder called 'data' this is where the databases area. Each database has its own folder and then in there are the tables.

table_name.frm <-- table structure
table_name.MYD <-- the actually data
table_name.MYI <-- the index data if you have this setup, otherwise you want have files with the extension .MYI

If you are looking for a GUI to work with MySQL, try http://webyog.com/en/ very easy to create, edit, empty, backup, drop, query and whatever else to the databases.

[stiphu]

There is a folder on my computer as well, with the name of the scheme, calles "measurement1" for example. In this folder, there is the data of the tables, for each table one file with the same name and the ending ".frm". This files are about 9 KB. There is another file, called db.opt, which is 1 KB. I have no idea what this file is about. But the stored data is not in this folder, because I stored about 2 GB data. The file "ibdata1" in the folder "data" is about 2 GB, so I guess the data is there. But thats not what I want, because the data of every scheme is stored in this file. But I wan't seperate folder with the data for every session. So maybe I should try to install an older version of MySQL?!

[fenway]

That's because you're using InnoDB tables, which have namespace data files, as one large file, as you're discovered.  It's _very_ annoying to remove, compress or otherwise delete information from this file -- all you can do it make it bigger.  This mainly has to do with ACID compliance and the way InnoDB tables are indexed -- MyISAM tables work very differently, and hence can be stored in a 1-table/1-index/1-file format.