| SitePoint Sponsor |
|
|
User Tag List
Results 1 to 5 of 5
-
Jun 29, 2007, 04:43 #1Currently Occupied; Till Sunda
- 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 | +----+-------------+----------+------+---------------+------+---------+------+------+----------------+
Joined tablesCode: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)
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)
-
Jun 29, 2007, 04:55 #2SQL Consultant
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Jun 29, 2007, 05:01 #3Currently Occupied; Till Sunda
- Join Date
- Aug 2001
- Location
- London
- Posts
- 2,475
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi,
Originally Posted by r937
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
-
Jun 29, 2007, 05:23 #4SQL Consultant
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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...
-
Jun 29, 2007, 05:49 #5Currently Occupied; Till Sunda
- 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