Jump to content


Photo

access time / data location


  • Please log in to reply
3 replies to this topic

#1 stiphu

stiphu
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 03 October 2006 - 08:37 AM

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

#2 mjlogan

mjlogan
  • Members
  • PipPipPip
  • Advanced Member
  • 122 posts

Posted 03 October 2006 - 08:50 AM

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.

#3 stiphu

stiphu
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 03 October 2006 - 09:01 AM

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?!

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 October 2006 - 02:46 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users