Pawan Tejpal SA Intellectual Property Ptejpal Knowhow Informatics NIC wrote:
>
> We are using mysql 3.23.39 .
> We have the table p6882 with following structure :
>
> +-------+-------------+------+-----+---------+--------------
> --+
> | Field | Type | Null | Key | Default |
> Extra |
> +-------+-------------+------+-----+---------+--------------
> --+
> | id | int(10) | | PRI | NULL |
> auto_increment |
> | pc | char(2) | YES | | NULL
> | |
> | pn | varchar(10) | YES | | NULL
> | |
> | kd | char(2) | YES | | NULL
> | |
> | st | char(1) | YES | | NULL
> | |
> | pb | date | YES | | NULL
> | |
> | yr | char(2) | YES | | NULL
> | |
> | an | varchar(13) | YES | | NULL
> | |
> | ad | date | YES | | NULL
> | |
> | ec | text | YES | | NULL
> | |
> | dc | text | YES | | NULL
> | |
> | ca | text | YES | | NULL
> | |
> | ip | text | YES | | NULL
> | |
> | ap | text | YES | | NULL
> | |
> | ac | text | YES | | NULL
> | |
> | nm | text | YES | | NULL
> | |
> | ti | text | YES | MUL | NULL
> | |
> | cppr | text | YES | | NULL
> | |
> | pd | date | YES | | NULL
> | |
> +-------+-------------+------+-----+---------+--------------
> --+
> 19 rows in set (0.00 sec)
> and we have to create indexes allmost on all fields. Till
> now we have created
> index on ti field(Fulltext Index) and now we are creating
> index on field pc(char(2))(using create index).It has taken
> over 24 hours and the status is as below :
>
> -rw-rw---- 1 root root 1694718180 Jul 4 22:46
> #sql-299_a8.MYD
> -rw-rw---- 1 root root 894477312 Jul 5 09:45
> #sql-299_a8.MYI
> -rw-rw---- 1 root root 8914 Jul 4 09:39
> #sql-299_a8.frm
> -rw-r--r-- 1 root root 0 Jul 5 09:45
> index.txt
> -rw-rw---- 1 root root 1694718180 Jul 3 22:29
> p6882.MYD
> -rw-rw---- 1 root root 853310464 Jul 3 22:29
> p6882.MYI
> -rw-rw---- 1 root root 8914 Jul 3 09:32
> p6882.frm
>
> Can anyone tell us why it is taking so much time and also
> how to speed up indexing or something wrong with our table.
> records in the tables are 93,50,000.
>
>
>------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Hi,
You don't give too much details on your system configuration
- OS
- RAM size
- mysql configuration
so it is difficult to give you accurate recommandations.
However, obviously, you are building very big indexes; you should
make your 'key_buffer' as big as possible based on your hardware
configuration.
In my case, indexing one table went from 4 hours down to 3 minutes
after changing this parameter from 16M to 64M and resulting index file
was around 50Mb. However, your index file is so big (~900Mb) that it
will not fit in memory unless you can allocate ~1Gb to your key_buffer !
Hope this helps
--
Joseph Bueno
NetClub/Trader.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
