Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    8

    Red face Unanswered: Table Indexes and Optimisations

    I found this quite annoying.

    I have a table which has already been created without any indexes.

    I then ran an SQL against it which was slow due to a number of table joins. So I add indexes into the table to speed things up. I even ran the myisamchk to re-index the table, but to no avail.

    I had to create a temporary table to store the data, drop the original table and then recreate it with the indexes. Then port all the data back in.

    The SQL runs fine and at speed.

    Anyone know if this is usual, or have I not configured something properly? This seems like a major problem if I have to do this everytime I need to optimize the table.

    Anyone help?

  2. #2
    Join Date
    Sep 2002
    Posts
    7
    Hi,

    I dont know if this will help buut:

    myisamchk sorts the INDEX file by default, in order to sort the DATA according to the INDEX, use the -R option when running myisamchk.

    but you are saying that your table is slow with indexes... now I have a table with 24 million rows and MY table is slow even though the index is up to date, because of the data not beeing sequential (not sorted) in the file... and when i select from that it takes lots of disk seeks to select...

    but unless you have millions and millions of rows like me, ordering the data should not be a important performance increase as the index sort should be adequate.. or else you're not using your index properly..

    I have on the other hand this problem:
    "C:\mysql\bin>myisamchk -R r:\dalist
    The value of the sort key is bigger than max key: 32"

    now whatta .... is that?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •