Sebastian wrote:
> is it ok to add index after the tables and data are already built, or is
> it better to create the index before data gets inserted?
Michael Stassen wrote:
> It's probably better to create the indexes up front (assuming you know in
> advance which ones will be needed), but I think that's a moot point if your
> table already exists and is full of data. There's no sense starting from
> scratch when you can simply add the index with
>
> ALTER TABLE yourtablename ADD INDEX name (item, type);
Scott Noyes wrote:
Some sections of the manual seem to indicate that it's better to create
indexes after data population, rather than before.
See section 7.2.14, "Speed of INSERT Statements"
<http://dev.mysql.com/doc/mysql/en/insert-speed.html>. The general
procedure there is to load the data using LOAD DATA INFILE, and then use
myisamchk. "This creates the index tree in memory before writing it to
disk, which is much faster because it avoids lots of disk seeks. The
resulting index tree is also perfectly balanced." (ibid)
Sort of. First, this doesn't apply to InnoDB tables. Thus, if your table is
MyISAM, and you intend to create the table and fill it with data all at once,
it is faster to create the table with indexes, disable them, load the data,
then enable the indexes, as described on the page you referenced. Note also
that you don't need myisamchk if you use LOAD DATA INFILE to populate an empty
table, or you can use "ALTER TABLE tablename DISABLE KEYS" before inserting
and "ALTER TABLE tablename ENABLE KEYS" after. This trick is also good for
bulk inserts later.
The basic idea is that building/updating an index is a cost best paid as few
times as possible. Best case is to update the index all at once after all
data has been inserted. Worst case is to use single row inserts, updating the
index after each.
The preceding only applies to MyISAM tables which can be filled when created,
or where later inserts can be done in bulk. If your table grows as customers
use it, however, it's not very helpful, as it makes no sense to disable/enable
keys around a single row insert.
I intended my reply to be specific to the original poster's issue, existing
full table with slow queries, rather than as general advice. I meant that
adding indexes in the beginning is better (planning ahead) than adding them
down the road when queries are slowing because there aren't any indexes
(reacting). I did not mean to make a statement about speed of creating pre
filled tables. I agree I was unclear.
It also seems I misunderstood the situation, because
Sebastian wrote:
> i am merging about 3 tables (each about 10-15k rows) into one table, so
> that is why i asked about when i should create the index (before or
> after) since i will be moving things around.
>
> since i have 3 tables which all are similar it is a pain because i have
> to repeat a lot of code in the app... this way i can just fetch the rows
> i need by specifying the item and type for the apps. so it's a new table
> with no data yet which i will dump into from other tables.
In that case, Scott's advice is apt. This will go fastest if you follow the
directions in the manual page he cites.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
