StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I optimize my query/environment to improve MySQL sorting on a large data set?
text
Body
copied!<p>Here's a sample MySQL query:</p> <pre><code>SELECT DISTINCT item.ID, item.CREATED_AT FROM `item` , `image` WHERE item.COLLECTION_ID =9 AND item.REJECTED IS NULL AND image.ITEM_ID = item.ID ORDER BY item.CREATED_AT DESC LIMIT 0, 30 </code></pre> <p>And here's the explain results:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE item ref PRIMARY,item_FI_1,rejected item_FI_1 4 const 313775 Using where; Using temporary; Using filesort 1 SIMPLE image ref item_id item_id 3 dbname.item.id 1 Using where; Using index; Distinct </code></pre> <p>Sorry, but I can only show the phpMyAdmin explain output at the moment.</p> <p>Here's the CREATE TABLE:</p> <pre><code>CREATE TABLE `item` ( `id` int(11) NOT NULL auto_increment, `collection_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `variant_id` int(11) default NULL, `duplicate_id` int(11) default NULL, `rejected` tinyint(4) default NULL, `name` varchar(255) default NULL, `remote_key` int(11) default NULL, `created_at` datetime default NULL, PRIMARY KEY (`id`), KEY `item_FI_1` (`collection_id`), KEY `created_at` (`created_at`), KEY `rejected` (`rejected`), KEY `variant_id` (`variant_id`), CONSTRAINT `item_FK_1` FOREIGN KEY (`collection_id`) REFERENCES `collection` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=691849 DEFAULT CHARSET=latin1 </code></pre> <p>Here's my variables:</p> <pre><code>auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 (Global value) latin1 character set connection utf8 (Global value) latin1 character set database latin1 character set filesystem binary character set results utf8 (Global value) latin1 character set server latin1 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci (Global value) latin1_swedish_ci collation database latin1_swedish_ci collation server latin1_swedish_ci completion type 0 concurrent insert 1 connect timeout 10 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 keep files on create OFF engine condition pushdown OFF expire logs days 0 flush OFF flush time 0 ft boolean syntax + -><()~*:""&| ft max word len 84 ft min word len 4 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine YES have compress YES have community features NO have profiling NO have crypt YES have csv YES have dynamic loading YES have example engine YES have federated engine YES have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl NO have ssl NO have query cache YES have raid NO have rtree keys YES have symlink YES hostname server1.gocodev.com init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 268,435,456 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb adaptive hash index ON innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb rollback on timeout OFF innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 innodb use legacy cardinality algorithm ON interactive timeout 28,800 join buffer size 131,072 key buffer size 8,384,512 key cache age threshold 300 key cache block size 1,024 key cache division limit 100 language /usr/share/mysql/english/ large files support ON large page size 0 large pages OFF lc time names en_US license GPL local infile ON locked in memory OFF log OFF log bin OFF log bin trust function creators OFF log error log queries not using indexes OFF log slave updates OFF log slow queries OFF log warnings 1 long query time 10 low priority updates OFF lower case file system OFF lower case table names 0 max allowed packet 1,048,576 max binlog cache size 4,294,963,200 max binlog size 1,073,741,824 max connect errors 10 max connections 500 max delayed threads 20 max error count 64 max heap table size 134,217,728 max insert delayed threads 20 max join size 18446744073709551615 max length for sort data 1,024 max prepared stmt count 16,382 max relay log size 0 max seeks for key 4,294,967,295 max sort length 1,024 max sp recursion depth 0 max tmp tables 32 max user connections 0 max write lock count 4,294,967,295 multi range count 256 myisam data pointer size 6 myisam max sort file size 2,146,435,072 myisam mmap size 4,294,967,295 myisam recover options OFF myisam repair threads 1 myisam sort buffer size 8,388,608 myisam stats method nulls_unequal net buffer length 16,384 net read timeout 30 net retry count 10 net write timeout 60 new OFF old passwords OFF open files limit 2,500 optimizer prune level 1 optimizer search depth 62 pid file /var/lib/mysql/server1.gocodev.com.pid plugin dir port 3,306 preload buffer size 32,768 protocol version 10 query alloc block size 8,192 query cache limit 1,048,576 query cache min res unit 4,096 query cache size 0 query cache type ON query cache wlock invalidate OFF query prealloc size 8,192 range alloc block size 4,096 read buffer size 131,072 read only OFF read rnd buffer size 262,144 relay log relay log index relay log info file relay-log.info relay log purge ON relay log space limit 0 rpl recovery rank 0 secure auth OFF secure file priv server id 0 skip external locking ON skip networking OFF skip show database OFF slave compressed protocol OFF slave load tmpdir /tmp/ slave net timeout 3,600 slave skip errors OFF slave transaction retries 10 slow launch time 2 socket /var/lib/mysql/mysql.sock sort buffer size 6,291,456 sql big selects ON sql mode sql notes ON sql warnings OFF ssl ca ssl capath ssl cert ssl cipher ssl key storage engine InnoDB sync binlog 0 sync frm ON system time zone GMT table cache 64 table lock wait timeout 50 table type InnoDB thread cache size 0 thread stack 196,608 time format %H:%i:%s time zone SYSTEM timed mutexes OFF tmp table size 134,217,728 tmpdir /tmp/ transaction alloc block size 8,192 transaction prealloc size 4,096 tx isolation REPEATABLE-READ updatable views with limit YES version 5.0.92-community version comment MySQL Community Edition (GPL) version compile machine i686 version compile os pc-linux-gnu wait timeout 28,800 </code></pre> <p>Any thoughts on how I can improve performance on this? My queries are averaging around 3 seconds on the above (pertaining to about 600,000 query matches). Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload