So the table is,
folio int vacante int folio2 char(10) and the table type is MyISAM
create index some_index on table(folio2);
and the table has about 200,000,000 rows.
MyISAM creates a file per table for table data, and for index data. You can find the files created underneath the mysql install directory in a directory with the database name (mysql/var if you are using source-compiled and mysql/data if you are using pre-compiled binaries). To quote the docs,
"Each |MyISAM| table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm' file stores the table definition. The data file has an `.MYD' (MYData) extension. The index file has an `.MYI' (MYIndex) extension,"
What's the max file size on your system? I suspect it's greater than 2 gigabytes if you have 200 million rows. But something to check.
You might be exceeding the capabilities of the MyISAM storage engine, or the version of MySQL you are using (which version *are* you using? 3.23 or a 4.0.x, or 4.1?).
Can you reduce the size of the index by creating a partial index, like
create index some_index on table(folio2(5));
to only index part of the data?
David
Oropeza Querejeta, Alejandro wrote
Below are the answers
Best Regards
-----Mensaje original-----
De: David Griffiths [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 30 de Junio de 2004 01:29 p.m.
Para: [EMAIL PROTECTED]
Asunto: Re: Index problem
What's the definition of the table? IE are you indexing an INT, VARCHAR, etc?
3 fields
Folio, Vacante, int
Folio2 char(10)
What's the definition of the index? Is it unique, composite, etc? Nonunique, single column (folio2)
What's the storage engine in use? InnoDB? MyISAM?
Myisam
Can you show the relevant parts of your my.cnf file?
I have the standard My-huge.cnf
What operating system are you using?
Redhat Linux 7.3 David
Oropeza Querejeta, Alejandro wrote:
Hi, i'm trying to create an index on a table with 199 million records. The problem is that is taking too long (8 hours and is not yet finnished).
does anyone have any idea?
the server is 2Xeon 2.8 gigs with 6 Gb of ram.
Best regards
Alejandro
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
