SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL optimization & ignored indexes:

    I'm a little confused as to why the following indexes would be ignored? Can someone shed a little light on this?

    Simple order by

    Code:
    mysql> EXPLAIN SELECT code, state FROM ozstates ORDER BY state ;
    +----+-------------+----------+------+---------------+------+---------+------+------+----------------+
    | id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
    +----+-------------+----------+------+---------------+------+---------+------+------+----------------+
    |  1 | SIMPLE      | ozstates | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using filesort |
    +----+-------------+----------+------+---------------+------+---------+------+------+----------------+
    Code:
    mysql> SHOW INDEXES FROM ozstates;
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | ozstates |          0 | PRIMARY  |            1 | ozsid       | A         |           8 |     NULL | NULL   |      | BTREE      | NULL    |
    | ozstates |          1 | ozState  |            1 | state       | A         |        NULL |     NULL | NULL   | YES  | BTREE      | NULL    |
    +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    3 rows in set (0.01 sec)
    Joined tables
    Code:
    mysql> EXPLAIN SELECT pid, stockportfolio.sid, epic, feedcode, instrument, name, bidPrice, midPrice, askPrice, partial, stockportfolio.openPrice, quantity, quantity * midPrice AS position, sector, colour, (midPrice - stockportfolio.openPrice) * quantity AS pl, dayValueChange FROM stockportfolio, stocks, sectors WHERE stocks.secid = sectors.secid AND stockportfolio.sid = stocks.sid AND uid = '15267' ORDER BY name
        -> ;
    +----+-------------+----------------+--------+-------------------------------------+---------+---------+-------------------------+------+----------------------------------------------+
    | id | select_type | table          | type   | possible_keys                       | key     | key_len | ref                     | rows | Extra                                        |
    +----+-------------+----------------+--------+-------------------------------------+---------+---------+-------------------------+------+----------------------------------------------+
    |  1 | SIMPLE      | stockportfolio | ref    | uid,bySid                           | uid     | 5       | const                   |    1 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | stocks         | eq_ref | PRIMARY,secid,stockNamePublic,bySec | PRIMARY | 4       | asx2.stockportfolio.sid |    1 | Using where                                  |
    |  1 | SIMPLE      | sectors        | eq_ref | PRIMARY                             | PRIMARY | 3       | asx2.stocks.secid       |    1 |                                              |
    +----+-------------+----------------+--------+-------------------------------------+---------+---------+-------------------------+------+----------------------------------------------+
    3 rows in set (0.00 sec)
    mysql>
    Code:
    mysql> SHOW INDEXES FROM stockportfolio;SHOW INDEXES FROM stocks;SHOW INDEXES FROM sectors;
    +----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | stockportfolio |          0 | PRIMARY  |            1 | pid         | A         |        3307 |     NULL | NULL   |      | BTREE      | NULL    |
    | stockportfolio |          1 | uid      |            1 | uid         | A         |         661 |     NULL | NULL   | YES  | BTREE      | NULL    |
    | stockportfolio |          1 | uid      |            2 | sid         | A         |        3307 |     NULL | NULL   | YES  | BTREE      | NULL    |
    | stockportfolio |          1 | bySid    |            1 | sid         | A         |         194 |     NULL | NULL   | YES  | BTREE      | NULL    |
    +----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    4 rows in set (0.00 sec)
    +--------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table  | Non_unique | Key_name           | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +--------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
    | stocks |          0 | PRIMARY            |            1 | sid          | A         |        1880 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | secid              |            1 | secid        | A         |          26 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | secid              |            2 | game_public  | A         |          47 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | secid              |            3 | game_schools | A         |          49 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | secid              |            4 | isDelisted   | A         |          49 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | secid              |            5 | isSuspended  | A         |          52 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | instrument         |            1 | instrument   | A         |           2 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | stockNameOrdPublic |            1 | name         | A         |        1880 |     NULL | NULL   | YES  | BTREE      | NULL    |
    | stocks |          1 | stockNamePublic    |            1 | secid        | A         |          26 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | stockNamePublic    |            2 | game_public  | A         |          47 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | epic               |            1 | epic         | A         |        1880 |     NULL | NULL   | YES  | BTREE      | NULL    |
    | stocks |          1 | gpindex            |            1 | game_public  | A         |           2 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | gsindex            |            1 | game_schools | A         |           2 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | gpnindex           |            1 | game_public  | A         |           2 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | gpnindex           |            2 | name         | A         |        1880 |     NULL | NULL   | YES  | BTREE      | NULL    |
    | stocks |          1 | gsnindex           |            1 | game_schools | A         |           2 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | gsnindex           |            2 | name         | A         |        1880 |     NULL | NULL   | YES  | BTREE      | NULL    |
    | stocks |          1 | bySec              |            1 | secid        | A         |          26 |     NULL | NULL   |      | BTREE      | NULL    |
    | stocks |          1 | bySec              |            2 | game_public  | A         |          47 |     NULL | NULL   |      | BTREE      | NULL    |
    +--------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
    19 rows in set (0.00 sec)
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | sectors |          0 | PRIMARY  |            1 | secid       | A         |          27 |     NULL | NULL   |      | BTREE      | NULL    |
    +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    1 row in set (0.00 sec)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your first query totally ignores the index because indexes do not improve table scans, and you are getting a table scan because you asked for every row in the table

    your joined query does use indexes, so i don't understand the question there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    your first query totally ignores the index because indexes do not improve table scans, and you are getting a table scan because you asked for every row in the table

    your joined query does use indexes, so i don't understand the question there
    Hi,

    Thanks for that. Sorry the latter query is using temporary and a filesort. I would like to optimize that query so that it does not occur.

    Thanks

    Andrew

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    eliminate temporary and filesort? start be eliminating your ORDER BY clause

    obviously, this may not be very useful for your application, so...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks that worked perfectly, i'm sure users wont get too irrate that their stocks are not ordered by name.

    We've also been hitting our max connections despite using persistent connections, whilst load testing our application.

    We've set max_connections to 500.

    It was load tested with a virtual number of users of 559. (Not sure why the other dev's set it to 500 and I don't know if there was a warm up to 559 concurrent users?)

    Code:
    [bbuser@psmg203 ~]$ mysqladmin -uroot  processlist
    +------+-----------------+-----------------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
    | Id   | User            | Host                        | db   | Command     | Time  | State                                                                 | Info             |
    +------+-----------------+-----------------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
    | 1    | system user     |                             |      | Connect     | 12451 | Waiting for master to send event                                      |                  |
    | 2    | system user     |                             |      | Connect     | 1422  | Has read all relay log; waiting for the slave I/O thread to update it |                  |
    | 11   | anon_user        | dynamic1.svr.com:35488 | db2 | Sleep       | 3476  |                                                                       |                  |
    | 12   | anon_user        | dynamic1.svr.com:35489 | db2 | Sleep       | 113   |                                                                       |                  |
    | 33   | anon_replication | db1.svr.com:57579 |      | Binlog Dump | 12274 | Has sent all binlog to slave; waiting for binlog to be updated        |                  |
    | 46   | anon_user        | dynamic1.svr.com:35551 | db2 | Sleep       | 141   |                                                                       |                  |
    | 77   | anon_user        | dynamic1.svr.com:50122 | db2 | Sleep       | 117   |                                                                       |                  |
    | 78   | anon_user        | dynamic1.svr.com:50123 | db2 | Sleep       | 2256  |                                                                       |                  |
    | 99   | anon_user        | dynamic1.svr.com:50124 | db2 | Sleep       | 424   |                                                                       |                  |
    | 100  | anon_user        | dynamic1.svr.com:50125 | db2 | Sleep       | 1338  |                                                                       |                  |
    | 101  | anon_user        | dynamic2.svr.com:36849 | db2 | Sleep       | 3980  |                                                                       |                  |
    | 102  | anon_user        | dynamic2.svr.com:36850 | db2 | Sleep       | 11640 |                                                                       |                  |
    | 114  | anon_user        | dynamic1.svr.com:53426 | db2 | Sleep       | 388   |                                                                       |                  |
    | 146  | anon_user        | dynamic1.svr.com:40992 | db2 | Sleep       | 3952  |                                                                       |                  |
    | 182  | anon_user        | dynamic1.svr.com:50734 | db2 | Sleep       | 2868  |                                                                       |                  |
    | 205  | anon_user        | dynamic1.svr.com:58141 | db2 | Sleep       | 1031  |                                                                       |                  |
    | 206  | anon_user        | dynamic2.svr.com:39200 | db2 | Sleep       | 10579 |                                                                       |                  |
    | 210  | anon_user        | dynamic1.svr.com:58142 | db2 | Sleep       | 6472  |                                                                       |                  |
    | 228  | anon_user        | dynamic2.svr.com:54405 | db2 | Sleep       | 9178  |                                                                       |                  |
    | 229  | anon_user        | dynamic2.svr.com:54406 | db2 | Sleep       | 4325  |                                                                       |                  |
    | 230  | anon_user        | dynamic1.svr.com:58143 | db2 | Sleep       | 1951  |                                                                       |                  |
    | 234  | anon_user        | dynamic2.svr.com:54407 | db2 | Sleep       | 4829  |                                                                       |                  |
    | 308  | anon_user        | dynamic1.svr.com:39931 | db2 | Sleep       | 709   |                                                                       |                  |
    | 334  | anon_user        | dynamic1.svr.com:48926 | db2 | Sleep       | 1646  |                                                                       |                  |
    | 370  | anon_user        | dynamic1.svr.com:43810 | db2 | Sleep       | 1341  |                                                                       |                  |
    | 441  | anon_user        | dynamic2.svr.com:35020 | db2 | Sleep       | 8370  |                                                                       |                  |
    | 475  | anon_user        | dynamic1.svr.com:49426 | db2 | Sleep       | 729   |                                                                       |                  |
    | 502  | anon_user        | dynamic1.svr.com:46249 | db2 | Sleep       | 148   |                                                                       |                  |
    | 579  | anon_user        | dynamic1.svr.com:49177 | db2 | Sleep       | 396   |                                                                       |                  |
    | 625  | anon_user        | dynamic2.svr.com:40864 | db2 | Sleep       | 6535  |                                                                       |                  |
    | 634  | anon_user        | dynamic1.svr.com:43365 | db2 | Sleep       | 1948  |                                                                       |                  |
    | 635  | anon_user        | dynamic2.svr.com:40865 | db2 | Sleep       | 6415  |                                                                       |                  |
    | 740  | anon_user        | dynamic2.svr.com:33291 | db2 | Sleep       | 5433  |                                                                       |                  |
    | 741  | anon_user        | dynamic2.svr.com:33292 | db2 | Sleep       | 5429  |                                                                       |                  |
    | 751  | anon_user        | dynamic2.svr.com:33293 | db2 | Sleep       | 3163  |                                                                       |                  |
    | 878  | anon_user        | dynamic2.svr.com:42315 | db2 | Sleep       | 3476  |                                                                       |                  |
    | 879  | anon_user        | dynamic2.svr.com:42316 | db2 | Sleep       | 4007  |                                                                       |                  |
    | 885  | anon_user        | dynamic2.svr.com:42317 | db2 | Sleep       | 3989  |                                                                       |                  |
    | 939  | anon_user        | dynamic2.svr.com:42276 | db2 | Sleep       | 3470  |                                                                       |                  |
    | 1219 | anon_user        | dynamic2.svr.com:46303 | db2 | Sleep       | 703   |                                                                       |                  |
    | 1262 | anon_local       | db2.svr.com:40116 | db2 | Sleep       | 275   |                                                                       |                  |
    | 1268 | anon_local       | db2.svr.com:40122 | db2 | Sleep       | 275   |                                                                       |                  |
    | 1288 | anon_local       | db2.svr.com:40270 | db2 | Sleep       | 0     |                                                                       |                  |
    | 1289 | anon_local       | db2.svr.com:40271 | db2 | Sleep       | 35    |                                                                       |                  |
    | 1290 | anon_local       | db2.svr.com:40272 | db2 | Sleep       | 0     |                                                                       |                  |
    | 1292 | root            | localhost                   |      | Query       | 0     |                                                                       | show processlist |
    +------+-----------------+-----------------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+


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
  •