Does anyone know how you can ALTER TABLE ADD INDEX to your tables without using the command line to do it?

is there a phpMyAdmin GUI feature where you can do this?

I have been hunting for a while now.. no such luck.. not even found when I try to edit the structure of my tables...

JohnDubya

01-25-2008, 03:21 AM

I'm using phpMyAdmin 2.11.3, and in this version, you would go to the "Structure" page and check the fields you want to index, and at the bottom, there is an icon (with a lightning bolt on it) to "Index" those fields.

Bobafart

01-25-2008, 03:54 AM

you rock, thanks.. not sure what that area meant

I have been reading the manual about INDEXING....

somthing i don't understand at this link:
http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

in their example they say:

SELECT * FROM test WHERE last_name='Widenius'; works with the index

but

SELECT * FROM test WHERE first_name='Michael';

doesn't

why is that?

with the way they set up the table it looks like both first_name and last_name have been indexed....

JohnDubya

01-25-2008, 04:52 AM

Basically, it won't use the first_name index because it only uses the indexes in a row. The last_name field is first, so if you search for the last_name or if you search for the last_name AND first_name, it will use the index, since you're searching for the index in the correct order. But searching for just the first_name leaves out the first part of the index, and therefore doesn't use it. Clear as mud? :D

Bobafart

01-25-2008, 10:49 AM

so if you made a table with the last_name and the first_name as separate indices then SELECT * FROM test WHERE first_name='Michael'; will work?

JohnDubya

01-25-2008, 02:59 PM

Yes, it will work that way. I think the only concern there is that if you have, say, 100,000 rows, the indices of last_name and first_name will each contain 100,000 rows, which is quite a bit of info. And with multiple indices, that can get bulky. I'm not sure about all the complexities of this though...read around, or maybe someone else will chime in.

FYI, this is one of the index tutorials I learned from when I was looking into this issue.