MYSQL :: Indexing Table Forces Repair By Sorting

Repair By Sorting
I have an application which uses MySQL to manipulate large amounts of data for short periods of time. The data set contains 13 columns each of which is indexed and typically about 30 Million rows.

I create a new table, load the data in to the table using load local infile from a CSV and then add indexes to each of the columns; the columns are a combination of integer, date and text. The text columns have a simple index and a full text index.

I need to be able to sort on each column (in a grid - I use DevExpress) and search on the textual fields.

I appreciate that getting the data into MySQL and creating the indexes is never going to be quick but I need to optimise this process as much as possible. Typically a data set would be loaded and viewed for a few hours and then a new dataset loaded and the process repeated.

When the index process is underway I get the above state displayed by show processlist and wondered if this is a problem. The whole load/index process takes about an hour and I would like to improve on this if I can.

I am also looking for suggestions as to how I may be able to best optimise this.

I use a dedicated server and the MyISAM engine.

Check Table And Repair Versus Optimize Table
I am confused about the difference between running a CHECK table and REPAIR(if necessary) versus running an OPTIMIZE table. It's not very clear in the documentation, but seems that OPTIMIZE table does a check and repair in addition to the other functions performed by optimize.

I have inherited some legacy code that does a check and repair and optionally follows it with an optimize. That seems redundant to me and I would like to streamline the code to make it an "either-or" selection.

Repair Table
Is there a way to issue a "REPAIR TABLE table_name" command to all
tables at once? Something like "REPAIR TABLE *"

REPAIR TABLE
Can REPAIR TABLE be used for rebuilding indexes on an InnoDB table? The only documentation I have seen is for MyIsam. There are 30 million records which has taken me 9 days to populate. I've made a backup but want to make sure this will work before I try it.

Can't Repair A Table
It seems I have a corrupt table and have tried the REPAIR TABLE command but during the operation I get this return.
mysql> REPAIR TABLE users;
+-----------+--------+----------+-------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+--------+----------+-------------------------------------------+
| sdc.users | repair | error | Can't open file: 'users.MYD'. (errno: -1) |
+-----------+--------+----------+-------------------------------------------+
1 row in set (0.00 sec)

Repair Corrupt Table
I am using xoops (a CMS) and MySQL on a website with not so much traffic. I am using "phpMyAdmin - 2.8.0.3-Debian-1" with "MySQL - 5.0.21-Debian_3-log" on a Debian unstable.
Three tables have gotten corrupt recently and won't let themselves be repaired. When I use repair it says status OK, but if I try to delete a record or if I analyze the table again, it says that the table was reported as crashed and that it needed a repair. The only useful message I get, if I analyze the table is about wrong key at page 111616.

Mysqlcheck: Table Is Marked As Crashed And Last Repair Failed
I'm using the command "mysqlcheck --all-databases --auto-repair --silent"
and I see these warnings and errors (listed below) on a daily occurrence.
What could cause these indexes to be out of order? Another thing I don't
understand is why an increase in the number rows constitutes a warning?

Hoping to here from MySQL users who have came across this. I'm using the
MyISAM storage engine and MySQL 4.1.11 on Red Hat Enterprise Linux.

Indexing MySQL Table
I am looking into indexing my MySQL table.

Should I index each column that i pick out in my script using the WHERE statement?

I ran this command to index 'picconame':

alter table picco add index(picconame)

Should this be done against all columns in the table access regularly?

Indexing A 2 Column Table
I have a 2 column table that looks like this:

id (Smallint) | userid (Mediumint)

I have to use id and userid in my where clause together, so I was thinking of creating 1 PRIMARY KEY on both of these columns, but is this necessary? My table won't have more than 2 columns, would an index be necessary?

Indexing And Table Structure Practices
Im using mysql 4.0.23 (win32)

Show create:
CREATE TABLE `dbcurrent` (
`Filespec` varchar(254) NOT NULL default '',
`ID` int(11) unsigned NOT NULL auto_increment,
`InUse` int(11) default NULL,
PRIMARY KEY (`Filespec`),
UNIQUE KEY `IDX_ID` (`ID`),
UNIQUE KEY `IDX_INUSE` (`InUse`,`Filespec`,`ID`)
) TYPE=MyISAM MAX_ROWS=100000000;

Query :
Select Filespec from dbCurrent where inUse is null order by id desc limit 1

Database size : 6,406,347

Is there a better way to index this table so the Query doesnt take forever
(this query takes about 30-45 seconds to execute) or a better way to word my
query???

Essentially I want the newest record in dbCurrent (by natural ID) thats not
marked InUse=1....

Advise On Large Table Indexing
I have a large (50 million records) demographic table, normalized, with about 10 searcheable fields (annual income, number of children, etc etc) all searchable fields are id's related to smaller tables.

The query I will run against the db is a classic multiple options choice :

Income : 1.All 2.Between X and Y 3.Between J and K
Children : 1.All 2.None 3.Between X and Y

etc

What is the best approach in your opinion coming to indexing ? Should I index all searcheable fields, combine fields or?

Unnecssarily Sorting Whole Table
This simple command always takes 0.30 seconds or longer:

mysql> select foo from footable order by -foo limit 20;

(get the 20 most recent foo's)

This however, always takes 0.0 seconds:

mysql> select foo from footable where foo>'2004-01-10' order by -foo
limit 20;

It looks like in the first example, MySQL is sorting the whole table.
But the column in question is indexed (confirmed by explain select...).
The manual says it should stop at 20 rows because I am using a LIMIT.

foo is of type datetime.

Any ideas how I can get more 0.00 second queries? Giving a start date is
not eloquent, nor can I necessarily give one. It doesn't seem like I
should need to, either.

Sorting A Parent Table
What is the best way to order the records in a table according to the number of child records they each have in a given child table? ex:

Table Parent:
id: name:
0 Michael
1 John
2 Alex

And the child table has a foreign_key that references the Parent table

Table Child:
parent_id: name:
1 Ann
1 Sue
1 Jan
0 Sara
2 Tess
0 Jill
1 Alli

I want to sort the Parent table records in the order of how many child records they have (John - 4, Michael - 2, Alex -1)

Sorting A Table According To User Criteria.
I was looking for a some kind of simple solution to this issue. The application requires that certain tables should be showed ordered by the user criteria, for example categories. the user will insert a new category and then he could move up or down the item to the position he wants to show it.

In order to do this, I was thinking to add a column, let's call it 'sort_id' this column will save the position of the record. (this is the best i could come up with, I would love any suggestions). This might create some concurrency problems, but since this is an administration module, I don't think more than one would modify the same table at the same time. Anyway, I couldn't find an easy way to insert a new record in the table with the last position + 1.

Optimize Table And Forced Sorting....
I've optimised a table but I'd like a table to be optimized and sorted according to the primary key in asc What should I do?

Get Selected Record Position After Sorting In Limit-table Browser?
In a table browse control with [first] [prev] [next] [last] buttons and a LIMIT setting, the records in a db table are devided and browsed in virtual pages.

In the GUI for these pages a record can be selected, and columns can be sorted.

When I sort with a record selected, changes are big that the record ends op on a page that is currently not displayed. And I like to switch to that page automatically so the selected record remains in view.

My question:
How can I find out what the selected record position or number would be, after sorting the table's total data?
Then I can calculate on what page the record is after sorting en adust this in the table browse control.

How To Repair A Db ?
when I execute from command line (linux centos server)

# mysqlcheck -o chatspa_main

chatspa_main.BAmigMSG
error : Can't find file: 'BAmigMSG.MYI' (errno: 2)
chatspa_main.BuscaAmigos
error : Can't find file: 'BuscaAmigos.MYI' (errno: 2)
chatspa_main.CLKEXclkcontrol OK
chatspa_main.CLKEXmain OK
chatspa_main.CLKEXstats OK
chatspa_main.CTICelebPhotos
error : Can't find file: 'CTICelebPhotos.MYI' (errno: 2)
chatspa_main.CTICelebrities
error : Can't find file: 'CTICelebrities.MYI' (errno: 2)
chatspa_main.ChatSpain_Users OK
chatspa_main.MCmain
error : Can't find file: 'MCmain.MYI' (errno: 2)
chatspa_main.MCvotecontrol
error : Can't find file: 'MCvotecontrol.MYI' (errno: 2)
chatspa_main.phpCardData
error : Can't find file: 'phpCardData.MYI' (errno: 2)
chatspa_main.phpCardUsers
error : Can't find file: 'phpCardUsers.MYI' (errno: 2)
chatspa_main.phpCatData
error : Can't find file: 'phpCatData.MYI' (errno: 2)

How to fix/remove these "Can't find file" errors please ?

Cannot Repair Tables
I lost my developers and an office i do hardware support for is down. I can do the basics, BUT checking and repairing the tables dont seem to work. I need some better direction. Here is a link to a text file that shows ALL of the errors. Can someone please reply with answers or even a direction to go. The is NO backup of the database and the data is vital.

http://members.planetwebdev.com/web/admin/mysql/report.txt

if "holy $#%@%#$" is the only answer you have. Please spare me your comments.

Repair Message
Running MySQL 4.0. I had an error in my database that wouldn't let me run selects (it said error 127 from table handler). I ran repair on the table and it fixed it dropping one row. I was just wondering what the meaning of the repair message is so that I can try to avoid the error in the future.

Found link that points at 11548 (outside data file) at 10152.

Keycache Repair
I ran the following querry on a large database:
ALTER TABLE vbpost ADD FULLTEXT INDEX (title,pagetext).
The querry is taking a very long time to execute (the database is also very large). I logged in with another shell and ran showprocess. It showed the querry in state keycache repair.
Do I need to do anything at this point? Is there a way to tell if the querry is still running?

REPAIR Repeatedly
I'm running MySQL 4.018 on Fedora. I've got a few tables with more than 1M records, and if a larger number of deletes is thrown at them or a MYSQLDUMP is attempted, I get a "got error 127 ...". I've run "REPAIR", MYSQLCHECK and every other repair possibility I could find. The tables are supposedly ok after the repair operation, and can be queried normally. As soon as I try to run a MYSQLDUMP or a large number of deletes, I get the corrupted tables again.?

Mysql Crash/repair
I've got a couple of directory sites that looks like somehow something's been deleted or crashed (the directory isn't showing any listings).

I had a look in the mysql directory, and all I see are a bunch of .frm files. I don't see any MYI or MYD files.

Is there any way to recover or repair? I'm assuming I need those files, and that the lack of them is what's causing my problems. We've done a couple of things lately that may have caused the problem like a server move and a hard reset.

Optimize Or Repair Db Tables
I bought a dating script online and been running it for 1 year, has about 1500 profiles, but the site is too slow now. Not about the host because I have godaddy host.
When I ask the script owner, they told me that I need to optimize the db to make tables indexed.
I already tried to go to phpmyadmin, click on all tables, then, select OPTIMIZE table from drop-down list,.... but it does not help. The site is still running too slow to load. I am using high speed intenet.

Repair Corrupt Tables
I have a couple of tables in a database the give me the following error when I try to open them:
thanks!
'can't open file: 'clinch_a.ibd (errno:1)'
What can I do to fix this problem.

Server To Repair Mysql DB
I've got a corrupt 8GB mysql table and am obliged to use
myisamchk -r -o -f --safe-recover

But this is taking immensely long time, and is going slower and slower
The time it would take to repair 2.7 million rows would be far too much than I can make my client wait.is there somewhere a service where I could sync a copy of the db files under /var/lib/mysql/, repair it on their powerful server, and bring it back home?

Repair Mysql Tables
is there some code to repair all the tables in my database in one go? instead of typing in "Repair Table <TableName>;" many times.

Auto Repair Tables
This is my first post on this board.
Well.. now to my question.
I´m running MySQL 4.1.11 on a linux-mandriva server.
Some days, there are a few tables in on of my databases that gets "corrupted" for some strange reason.
And this always happends, when i´m NOT around to repair the tables.
So.. i was wondering if there is some kind of php-script or program, that could repair the tables automaticly

ANALYZE, OPTIMIZE, CHECK, REPAIR
when to use the ANALYZE, OPTIMIZE, CHECK, REPAIR commands. From what I can tell, ANALYZE and OPTIMIZE are for making the table optimal to increase performance. CHECK and REPAIR are used to find & fix corruptions. ANAYLZE takes a lot less time to run than CHECK.

The questions I have are:

1. What is the general rule of thumb on regular (daily) maintenance of a MySQL DB?

2. Will ANALYZE report table corruptions? If so, I don't even need to call CHECK, I can directly go
do REPAIR?

3. If the anwer to #2 is no, meaning I have to run CHECK TABLE, should I separate the optimization exercises from the find-corruption-and-repair routines?

MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same).

My server specifications:
Red Hat 9.0
Pentium 4 3.0 GHz
2 GB Ram, Burst RAM 5 GB
Running webserver, mail server as well.

Is there a way I can view the minute process details, as what table record is being updated ?

Indexing
Just wondering what is the normal amount of indexes on a table.

I have a database with many tables although the tables fluctuate in the amount of fields they have within them. How many indexes would you suggest to apply to each table.

At present I have added indexes to all tables which are frequently searched upon and or information is retrieved from as for other less used fields i never added indexes as i understand thats the point of indexes. Am I correct in how i have currently set these up as I am relatively new to the indexing feature.

Indexing
I have saved some records with indexing and without, The saving time makes hardly any difference (I tried saving 20 records to 100 records), I would have thought indexing slowed down the saving. I then wanted to retrieve a record based on the indexed field (email), during the runs I did I think there is some difference (decrease in time taken) but there isnt as much of a difference as I first thought. How worthwile is indexing?

Indexing
My table has ~ 2 million records in it. Out of those records, only a relatively small set (~200) are queried and updated frequently. The rest are queried but almost never updated. Furthermore, I can reliably predict which of the records are going to be frequently updated and which ones are not.

The issue is that even though only the same 200 records are being updated, the indexes have to be adjusted for all 2 million of them. To speed things up, I'm considering two options:

(1) Split the table into two and have my web app switch between them as necessary.

(2) Add a frequently_updated field to my table and index based on that.

I'd like to use option 2 since it's a lot less likely to break something in the app, but I'm not sure how it compares performance-wise. Let's say for option 2, all indexes are of the form [frequently_updated, other_field(s)].

How then is the index structured? Specifically, if I add or update a record where frequently_updated = true, is only a small part of the index updated (e.g. only one branch on a tree) or is the entire index updated?

GIS Indexing..
I have been testing mysql's GIS support for quite some time now. I have one
question. Suppose I execute this simple query:

select * from node where MRBWithin(node.shape_geo, LINESTRING(0 0, 200 200));

This will select all node records that are bounded in in the ractangle (0,0),
(0,200), (200,200) and (200,0).

Is there anyway to get mysql to return the results in the order of 'closest to
the center point of MBR'? Ie the center point (100,100). The result set would
be something like:

(101, 105)
(90, 80)
(20, 35)
(199,173)

Indexing
mysql> desc Jobs;
+----------+----------+----+---+-------+--------------+
|Field     |Type      |Null|Key|Default| Extra        |
+----------+----------+----+---+-------+--------------+
|jobID     |int(10)   |    |PRI|NULL   |auto_increment|
|empID     |int(10)   |    |MUL|0      |              |
|jobStartID|int(10)   |    |MUL|0      |              |
|jobStopID |int(10)   |    |MUL|0      |              |
|jpID      |int(10)   |    |MUL|0      |              |
|jobStatus |varchar(5)|    |   |       |              |
+----------+----------+----+---+-------+--------------+

mysql> desc JobPeriods;
+-----------+----------+-----+---+-------+--------------+
|Field      |Type      |Null |Key|Default|Extra         |
+-----------+----------+-----+---+-------+--------------+
|jpID       |int(10)   |     |PRI|NULL   |auto_increment|
|jpStartTime|bigint(12)|     |   |0      |              |
|jpStopTime |bigint(12)|     |   |0      |              |
+-----------+----------+-----+---+-------+--------------+

Indexing?
I have a Database Table consisting of 16 rows. One is a Card number row which is Unique. The database size is over 3lac. I want to Optimize the database. I read about indexing. How to go about this?? Do i do single indexing or double?

Indexing
I am currently importing about a million rows into temp tables - then the temp tables are indexed and finally the original tables are dropped and the temp tables are renamed - The indexing of the temp tables takes a considerable amount of time - My question is - Would there be a difference if i index the temp table before importing as opposed to after??

Indexing
I have created some indexes. I wish to view them now,like a list files. How can I 'list' my indexes.

Indexing FKs
Should I index fks seperately in the linking table (in relation n:m) (if I already indexed fks usign pk). heres an example:

[student]-1---n-[student_lessons]-n----1-[lesson]

CREATE TABLE students_lessons (
student_id INT(11) NOT NULL,
lesson_id INT(11) NOT NULL,
PRIMARY KEY (student_id, lesson_id)
KEY fk1(student_id), /*add this??*/
KEY fk2(lesson_id) /*add this??*/
)"

Is it a good habit?

Use Indexing
I will make an online game with max 5000 accaunts and max 20000 character.
Online users will be always 200-300.
I want to make it with mysql becouse if I use array and it crashes all the data will be lost. I am new in Mysql. I want to know how to use indexes (How to get the id of a character and how to get character data of an ID).
There was many tutorials on http://dev.mysql.com/doc/ for indexes but I cant understand it! :S
If I use fixed row length would it be faster? Can I get memorypointers to speed up indexing?

Indexing
I hope this is not a "too complex" questions, so please help me with these:

1. What is key indexing (in MySQL)?
2. Assume I already have a database (and all tables inside), how
do I perform a "key indexing" in my database?
3. And can you give me one simple sample of implementation?

Indexing FKs
Should all foreign keys be indexed? Or should I only put indexes on those which I will perform select/order by (etc

Where Are The Best Places To Use Indexing? And
how do you use proper indexing?

I've just added random indexing on things, but I'm not sure if I've done them right

someone should make a sticky thread telling people about indexes

Indexing Problem
I am indexing email addresses, and I want to be able to search by domain name. I can’t use a regular index for this, I would need to to a query like:

SELECT ... FROM users WHERE email LIKE '%.com'

That query would, of course, not use indexes.

I have heard about MySQL needs its own built-in calculated indexes. The ability to create an index based on some expression.

Indexing Question
I know this is probably very simple but I'm lost.

I want to give my MySql database an index so that the first item has the value 0, the second has the value 1 and so on.

I assumed I did this with a primary key set to auto increment but when I delete a record it leaves a gap in the index (if I have files with a primary key of 0, 1, 2, 3 and delete record 2 my index looks like 0, 1, 3, rather than updating the numbers to 0, 1, 2).

How do I set such an index up?

Do I even need to do this or is this built into mysql in some way? If so, what do I need to refer to if I am, for example, deleting a record (what do I need to replace the '?' below with to delete my second record?):

$delete = "DELETE FROM Articles WHERE ?=1";
mysql_query($delete);

I'm using php and phpMyAdmin to communicate with the database.

Indexing Mysql
I have a mysql table, in which I want to preform FULL-TEXT search, but Im unsure how to 'index' rows..

Indexing For Sorts
should you make a column that will always be sorted into an index?

for example: a column that counts the number of views to a particular item. (in this case, the most common usage will be to fetch the item that is most viewed... my method

SELECT * FROM counter SORT BY count DESC, LIMIT 1

Indexing MySQL?
I keep on hearing alot about indexing mysql but can't seem to figure out how to do it,
I'm running mysql on linux redhat 7.2 could some one explain to me how to do this as i'm not very familar with mysql administration tasks.

MySQL Indexing
had much experience with indexing tables for optimality, but the product-search on a site I've been involved with is painfully slow, and I want to speed it up. It's based on OSCommerce, which is woefully inefficient, but there's nothing to be done about that, so will have to struggle with the database. The particulatly noticeable slowness is (ironically enough...) with the 'quick search' functionality.
The query is essentially:

SELECT p.image, pd.name, pd.short_description, p.price FROM products p JOIN products_description pd ON (p.id=pd.productid) WHERE pd.lang=1 AND pd.name LIKE '%keyword%' OR pd.description LIKE '%keyword%' OR p.model LIKE '%keyword%';

It's annoying that the system uses two tables when (in this case at least, with only one language) one would suffice.

When in the context of the page, a search using the query as above takes about 15-20 seconds to load (this is running on a more than adequate server, on a local network), running just the query from the MySQL console registers about 7 seconds.

What should I be indexing? If it were ANDs joining the WHERE-conditions then it'd be simple, but is there anything that can be done?
The OSC paging-system does a SELECT COUNT(*) FROM... using the same query as above, and this is rather slow as well, so any pointers in that direction would be appreciated.

I'm more familiar with postgres, and so don't have much of a clue about FULLTEXT indexing? Would this be of any benefit?

The databases contains around 50k products and accompanying descriptions so while not tiny, is not exactly huge and shouldn't be this slow.