SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Index and Explain

    Hi:

    I have following table:
    create table instituteEmail (
    id int auto_increment primary key,
    name varchar(50) not null unique,
    idInstitute int references instituteInfo(id),
    isActive tinyint not null default 1,
    index(name,idInstitute)
    );

    I have 4 rows in it.

    When I "explain" following Select command,
    describe SELECT `instituteInfo`.`id`, `instituteInfo`.`name`, `instituteInfo`.`email`, `instituteInfo`.`idType`, `instituteInfo`.`idInstitute`, `instituteInfo`.`isActive` FROM `instituteInfo` WHERE (name like '%%') AND (idInstitute = 0) LIMIT 4

    It shows "type=all"
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE instituteInfo ALL NULL NULL NULL NULL 4 Using where


    Can someone please help me on how can I improve? From what i read, "type=all" is bad. Is it true in this case too?

    thakns
    ---------------------------
    Errors = Improved Programming.
    My Site

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you don't have that many records in total, mysql won't scan index tree. Seeing your explain, you only have 4 records so mysql will choose to go for full table scan rather than using index file as well. Your query also isn't suitable for use of indexes, as you have LIKE %% which forces mysql to perform a full table scan.

    However, you have indexed 3 out of 4 columns of your table. Whether it'll be a full table scan or not, it doesn't really matter as the performance will be nearly the same.

  3. #3
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. So, if I din't have like and had 1000 column then it should kick in?

    from that perspective, is that index good?
    ---------------------------
    Errors = Improved Programming.
    My Site

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, with query like that the index won't help.

  5. #5
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you please clarify?

    Is there anything that can be done to improve this? or will I have to leave it like this?
    ---------------------------
    Errors = Improved Programming.
    My Site

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course.

    First off, you are not selecting a single thing from your instituteEmail table so the SHOW CREATE is not really helpful there since we're looking at the wrong table, as you're using instituteInfo table.

    Second thing is that your WHERE clause is forcing MySQL to perform full table scan due to LIKE '%%'.

    As I don't really know what your end goal is (I could be guessing), until you explain what you want to achieve - it's hard to suggest optimization plan.

    Also, indexes aren't that magic. You can't just index a column and then expect that LIKE '%%' will be able to use that index - it won't.
    You don't achieve much by indexing nearly the whole table.
    The goal of the index is that RDBMS is scanning smaller file (index file) in order to obtain where the record is in the larger file (data file) without having to scan the larger file. That's why indexes speed up certain queries.

    If you have an index on column named `name`, and if that column contains record "Lorem Ipsum Dolor Sit Amet" and if you query for

    Code SQL:
    WHERE `name` LIKE '%Dolor%'

    the index will not be used. Why you might wonder - because the database HAS to scan everything containing word 'Dolor' so it cannot use the index properly.

    If you were to use

    Code SQL:
    WHERE `name` = 'Lorem Ipsum Dolor Sit Amet'

    There are certain tricks on how to use indexes for char/varchar columns but in order not to overcomplicate things - it'd be better if you could explain what you want to achieve and I'm sure there's a better approach hiding somewhere

  7. #7
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah sorry. din't realize I posted wrong table

    create table instituteInfo (
    id int auto_increment primary key,
    name varchar(25) not null,
    email varchar(255) not null,
    idType int references instituteDetailType(id),
    idInstitute int,
    isActive tinyint not null default 1,
    index(name,idInstitute)
    );


    As for the end result, I want user to select a certain alphabet and then show ALL institutes "starting" with that alphabet. If no alphabet is specified then show everything.

    For startes, I think i can make it NOT use like if i am showing all records.

    But as you explained, i think there might not be anything possible in this case as I have to use like (unless you can share you "hidden" methods )
    ---------------------------
    Errors = Improved Programming.
    My Site

  8. #8
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How many records do you think you will have in total? These optimizations aren't that necessary if we're not talking about high concurrency, many users and few hundred thousands of records. If you think you'll have something like 10k records, I think you should be ok.

    Tweaking your innodb settings will probably yield better results too (look up innodb_buffer_pool_size), and seeing the table is small anyway - I see no need in tampering with indexes to try to speed up LIKE '%%' searches.

  9. #9
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I don't think I'll have more than 10,000 records. So, I guess I'll be safe in that regards. I won't have access to settings file (Shared Server) so can't change that buffer_pool_size but its good to know.

    thanks
    ---------------------------
    Errors = Improved Programming.
    My Site


Bookmarks

Posting Permissions

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