SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql SELECT optimization

    Hi, I have a question about Mysql database optimization.

    I have a table "t1", which has an index "id". but when i execute "EXPLAIN SELECT * from t1 order by id" in phpmyadmin I got the following results:

    table type possible_keys key key_len ref rows Extra
    t1 ALL NULL NULL NULL NULL 1078 Using filesort

    Does this mean that this simple SELECT is very slow since it is not using the index at all?
    Tech Deals & Coupons: http://www.xpbargains.com

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there's nothing in the query that can use an index. no `WHERE id=something' or a join using `id', etc. MySQL can use the index for sorting (e.g. `ORDER BY id'), but only with a LIMIT. in your query, it's selecting all the rows and then sorting them on `id' (the `Using filesort' part). MySQL won't use the index for this sorting because it would have to jump between the index file and data file too much. back and forth once for each row, actually. so it's faster for it to scan the table for all rows, sort them internally, and display them.

    if you LIMIT the rows it will use the index for sorting:

    Code:
    +--------+-------+---------------+---------+---------+------+------+-------+
    | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |
    +--------+-------+---------------+---------+---------+------+------+-------+
    | t1     | index | NULL          | PRIMARY |       4 | NULL | 1078 |       |
    +--------+-------+---------------+---------+---------+------+------+-------+
    the type is `index' rather than `ALL' which means it's using the index, and the key under `key' says which key it's using for sorting.

    you might wanna check these pages in the manual to see how MySQL uses indexes and some general optimization things:

    section 5.2: http://www.mysql.com/documentation/m...ml#Query_Speed
    section 5.4: http://www.mysql.com/documentation/m...base_Structure
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are right, DR_LaRRY_PEpPeR, thank you for your kind help.

    I still have a question. suppose i have a table "t1" with columns "col1", "col2", "col3"...etc.

    Now I want to optimize
    "SELECE * FROM t1 WHERE col1 >= 'abc' ORDER BY col1, col2", then what index should I make? Now I have an index ind1=col1, and ind2=(col1,col2) but the explain result looks like this:


    +--------+-------+---------------+---------+---------+------+------+----------------------------+
    | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +--------+-------+---------------+---------+---------+------+------+-------+--------------------+
    | t1 | ALL | ind1,ind2 | NULL | NULL | NULL | 13 | where used; Using filesort |
    +--------+-------+---------------+---------+---------+------+------+-------+--------------------+

    doesn't look too good right? I read the mysql manual you mentioned but couldn't figure out why. maybe server setting?
    Tech Deals & Coupons: http://www.xpbargains.com

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for the query

    SELECT * FROM t1 WHERE col1 >= 'abc' ORDER BY col1, col2

    you would optimally have an index on `col1' and `col2' together, which it looks like you do. this way, MySQL can use the index for the WHERE and, i think, for sorting. however, you don't need an index on `col1' by itself if you have one on `col1' and `col2' (in that order) together, because MySQL can still use the first part (`col1') of the multi-part key as if it was by itself.

    as to why EXPLAIN is saying ALL, it's probably because you have only 13 rows in the table (and especially if more than about 30% of those rows have col1 >= 'abc'). MySQL won't use an index if it thinks it will have to look at more than about 30% of the rows, because it causes too much jumping back and forth between the index file and data file. it's faster in that case to sequentially scan the whole table (which is what type=ALL means in EXPLAIN).


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
  •