Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Dec 2006
    Posts
    417
    Thanks
    168
    Thanked 1 Time in 1 Post

    phpmyadmin question

    I am using the latest version of phpMyAdmin

    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...

  • #2
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    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.
    Last edited by JohnDubya; 01-25-2008 at 03:34 AM.

  • Users who have thanked JohnDubya for this post:

    Bobafart (01-25-2008)

  • #3
    Regular Coder
    Join Date
    Dec 2006
    Posts
    417
    Thanks
    168
    Thanked 1 Time in 1 Post
    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/...n-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....

  • #4
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    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?

  • Users who have thanked JohnDubya for this post:

    Bobafart (01-25-2008)

  • #5
    Regular Coder
    Join Date
    Dec 2006
    Posts
    417
    Thanks
    168
    Thanked 1 Time in 1 Post
    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?

  • #6
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    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.

    http://www.databasejournal.com/featu...le.php/1382791

    On page 3, it goes through multi-column indices.
    Last edited by JohnDubya; 01-25-2008 at 03:05 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •