Re: [PHP-DB] indexing on existing DB
But, remember - you don't rebuild completely an index - you rather add to it. So, there are three scenarios: 1. Q: You create the table to then send into it lots of data row per row A: create the table first, then build an idex on it once. 2. Q: You have to constantly be adding the data into a table A: create the table with an index and keep adding data to it 3. Q: You add chunks of data into the existing indexed table A: If table is really large, you need a *big* loop to insert one by one and care about that very time - drop index first, insert the data, recreate the index when server is not busy. However, this is rare. Most often you will need to make your SQL smarter to inject data all at once like SELECT INTO. This (supposly) will do indexing only once as it is only one operation. Well, even if mySQL is not THAT relational. -- Maxim Maletsky [EMAIL PROTECTED] "John W. Holmes" <[EMAIL PROTECTED]> wrote... : > > In MySQL, is there any difference between creating an index at table > > creation time, and creating an index on an existing table? Does an > index > > created on an existing table re-index itself after each insert/update, > or > > does it only index itself once - when you create the index? > > In the end, it's the same. Indexes are constantly being built (or added > to) with each insert or update. > > Where it's helpful to create an index after the table is loaded with > data, is if you are loading a bunch of data into a table. Indexes will > slow down INSERTs because it also has to update the index when it > inserts the data. So, it'll be quicker overall to insert all of your > data, and then create an index on the table. This is only applicable if > you're inserting a large amount of data. > > ---John Holmes... > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] indexing on existing DB
> In MySQL, is there any difference between creating an index at table > creation time, and creating an index on an existing table? Does an index > created on an existing table re-index itself after each insert/update, or > does it only index itself once - when you create the index? In the end, it's the same. Indexes are constantly being built (or added to) with each insert or update. Where it's helpful to create an index after the table is loaded with data, is if you are loading a bunch of data into a table. Indexes will slow down INSERTs because it also has to update the index when it inserts the data. So, it'll be quicker overall to insert all of your data, and then create an index on the table. This is only applicable if you're inserting a large amount of data. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] indexing on existing DB
Except when you have a million records, which may take a few seconds to build. :-) Marco - php|architect -- The Monthly Magazine For PHP Professionals Come visit us on the web at http://www.phparch.com! On Fri, 2002-11-08 at 11:12, Maxim Maletsky wrote: > > Index is always re-indexing itself on INSERT/UPDATE. Roughly, I don't > think there is any difference in when you create an index before or > after table is populated. > > > -- > Maxim Maletsky > [EMAIL PROTECTED] > > > > "Jason Vincent" <[EMAIL PROTECTED]> wrote... : > > > (I know this is more of a mySQL question than PHP, but allow me this one if > > you would...) > > > > In MySQL, is there any difference between creating an index at table > > creation time, and creating an index on an existing table? Does an index > > created on an existing table re-index itself after each insert/update, or > > does it only index itself once - when you create the index? > > > > Thanks in advance > > > > Regards, > > > > J > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] indexing on existing DB
Index is always re-indexing itself on INSERT/UPDATE. Roughly, I don't think there is any difference in when you create an index before or after table is populated. -- Maxim Maletsky [EMAIL PROTECTED] "Jason Vincent" <[EMAIL PROTECTED]> wrote... : > (I know this is more of a mySQL question than PHP, but allow me this one if > you would...) > > In MySQL, is there any difference between creating an index at table > creation time, and creating an index on an existing table? Does an index > created on an existing table re-index itself after each insert/update, or > does it only index itself once - when you create the index? > > Thanks in advance > > Regards, > > J > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] indexing on existing DB
(I know this is more of a mySQL question than PHP, but allow me this one if you would...) In MySQL, is there any difference between creating an index at table creation time, and creating an index on an existing table? Does an index created on an existing table re-index itself after each insert/update, or does it only index itself once - when you create the index? Thanks in advance Regards, J
