On 12/1/2011 2:15 PM, SpreadTooThin wrote:
Yes, think about it. To build the index, MySQL has to scan the entire
table. While that is going on, you wouldn't want changes to the table -
it could corrupt the index.

MySQL *could* keep track of the changes and apply them to the index
after the fact, but to do so would be fairly complicated - for an action
which is performed very seldom. So rather, MySQL locks the table while
the index is being built.

Unless you have a huge table, it shouldn't take that long. Just do it
when the system is not very active.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

can I create an alter table and add three indices all at one?
so rather than:
ALTER TABLE MyTable ADD INDEX (a);
ALTER TABLE MyTable ADD INDEX (b);
ALTER TABLE MyTable ADD INDEX (c);

Can this be done?
ALTER TABLE MyTable ADD INDEX (a), (b), (c);

On 12/2/2011 2:22 PM, SpreadTooThin wrote:
You can, syntax would be

ALTER TABLE MyTable ADD INDEX ndxa (a),
ADD INDEX ndxb (b),
ADD INDEX ndxc (c);

P.S. Note that I added names to the indexes. I find its a good idea to
name your indexes - that way if you have you can more easily tell what
the indexes pertain to.

Not sure how much this is going to help, though. MySQL still has to
lock the tables - and creating 3 indexes at once will take more time
than creating 1. I don't know if it's 3x the amount of time, but if it's
taking a long time to create the indexes, I would think a break between
them to allow waiting programs to access the table.

One other thing - are you sure you need all the indexes? Too many
indexes are bad also - they slow down UPDATE (affecting the indexed
column(s)), INSERT and DELETE statements. For best performance you
should have the indexes you need, but only the indexes you need.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Well I looked at the SQL and the WHERE statements and I can see that these indexes are necessary. (My table has 12.5 Million Rows at the moment.)
The insert still seems quite fast, and the application lag that I was experiencing before adding these indexes is GONE! Yay.

Thanks all for the tips.

If the table before and after the ALTER TABLE is acceptable to your
application (as would be the case for adding/deleting indexes),
you're fine (MySQL table locking will handle it), unless the
applications are so real-time that a locked table pause to rebuild
an index is unacceptable. (For example, if your client is running
your heart pacemaker, be careful.)

If you are renaming or deleting a column used by the application and
intend to fix the application to no longer use that column, yes, you
need to stop your clients and fix them.

If the number or order of columns is changing, this may break your SQL.
One way to avoid this is to program defensively. In INSERT sql,
list the columns:
INSERT INTO foo (bar, baz, name) VALUES (99, 12, 'George');
NOT
INSERT INTO foo VALUES (99, 12, 'George');
which will break if someone adds a 4th column.

Don't use SELECT * FROM ...; . Name the columns. This protects you
against problems when adding or re-arranging the order of the columns.

SpreadTooThin wrote:
Always nice to hear good news..
> Thanks all for the tips.

Gordon Burditt wrote:
well will it?

Does Mysql complain or simply fill the first three?

I must say my preference is:

INSERT INTO foo set bar='99', baz='12', name='George');


Simply because I find it easier to relate to things as name/value pairs...


Again, if using - say - PHP the data is orgainised in a field name
indexed associative array, so it doesn't matter what the order is if you
use the indices to access it.

>

Yes, the error i get is:

ERROR 1136 (21S01): Column count doesn't match value count at row 1


I have found that to be a problem on occasion when the value being
selected is an expression (perhaps a messy, complicated one) and
trying to figure out what name it has in the associative array
isn't always easy.

It may also make it much harder to figure out what code actually uses
a given field, should you be thinking of changing its type or changing
what's in it.

On 12/2/2011 11:11 PM, The Natural Philosopher wrote:
Which is non-standard SQL and will fail on every other RDBMS on the
market (and even in MySQL running in STRICT mode).


Once again you miss the point...

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 12/3/2011 12:57 AM, Gordon Burditt wrote:
Gordon, don't try to argue with TNP. There are 10 year olds with more
programming aptitude than he has. But he's not afraid to tell people
why his ways are best.

Quite true. But immaterial for a script kiddie like TNP.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================