Re: Utilize maximum system resources for better performance
Rick James Wrote:
-------------------------------------------------------
> One big UPDATE that hits all the rows? Or one row
> at a time? Or chunked?
>
one row at a time using php
--------------------------------------------------------------
1) one new issue i am facing is ,
every night at the time of sitemap creation , i need to fetch all in 50k batches., few of the starting queries runs faster but later on queries takes much more time, when i checked process list it showed "Creating sort Index", what index is need to be added ?
here is my query
e.g.
SELECT record_num, title FROM content WHERE enabled = 1 ORDER BY record_num ASC LIMIT 0 , 50000
this is is fatsre , .
but
SELECT record_num, title FROM content WHERE enabled = 1 ORDER BY record_num ASC LIMIT 2250000 , 50000
this one is slower
mysql> explain SELECT record_num, title FROM content WHERE enabled = 1 ORDER BY record_num ASC LIMIT 2250000 , 50000 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: content
type: ref
possible_keys: e_c_d_v,e_d_v,e_c_v,e_v,e_u,e_s_ud,enabled_record_num
key: e_c_d_v
key_len: 1
ref: const
rows: 3193949
Extra: Using where; Using filesort
1 row in set (0.00 sec)
CREATE TABLE `content` (
`hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
`title` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`og_name` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`keywords` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`files_count` smallint(5) unsigned NOT NULL DEFAULT '0',
`more_files` smallint(5) unsigned NOT NULL DEFAULT '0',
`files` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`category` smallint(3) unsigned NOT NULL DEFAULT '600',
`size` bigint(19) unsigned NOT NULL DEFAULT '0',
`leechers` int(11) NOT NULL DEFAULT '0',
`completed` int(11) NOT NULL DEFAULT '0',
`seeders` int(11) NOT NULL DEFAULT '0',
`creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`upload_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`vote_up` int(11) unsigned NOT NULL DEFAULT '0',
`vote_down` int(11) unsigned NOT NULL DEFAULT '0',
`comments_count` int(11) NOT NULL DEFAULT '0',
`imdb` int(8) unsigned NOT NULL DEFAULT '0',
`video_sample` tinyint(1) NOT NULL DEFAULT '0',
`video_quality` tinyint(2) NOT NULL DEFAULT '0',
`audio_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
`subtitle_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
`verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
`uploader` int(11) unsigned NOT NULL DEFAULT '0',
`anonymous` tinyint(1) NOT NULL DEFAULT '0',
`enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
`tfile_size` int(11) unsigned NOT NULL DEFAULT '0',
`scrape_source` tinyint(1) unsigned NOT NULL DEFAULT '0',
`record_num` int(11) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`record_num`),
UNIQUE KEY `hash` (`hash`),
KEY `upload_date` (`upload_date`),
KEY `seeders` (`seeders`),
KEY `leechers` (`leechers`),
KEY `vote_up` (`vote_up`),
KEY `comments_count` (`comments_count`),
KEY `tfile_size` (`tfile_size`),
KEY `e_c_d_v` (`enabled`,`category`,`upload_date`,`verified`),
KEY `e_d_v` (`enabled`,`upload_date`,`verified`),
KEY `e_c_v` (`enabled`,`category`,`verified`),
KEY `e_v` (`enabled`,`verified`),
KEY `e_u` (`enabled`,`uploader`),
KEY `e_s_ud` (`enabled`,`seeders`,`upload_date`),
KEY `enabled_record_num` (`enabled`,`record_num`)
) ENGINE=InnoDB AUTO_INCREMENT=7013461 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED
my quereis gets fired like these
SELECT record_num, title FROM content WHERE enabled = 1 ORDER BY record_num ASC LIMIT 0 , 50000
SELECT record_num, title FROM content WHERE enabled = 1 ORDER BY record_num ASC LIMIT 50000 , 50000
SELECT record_num, title FROM content WHERE enabled = 1 ORDER BY record_num ASC LIMIT 100000 , 50000
.
.
.
SELECT record_num, title FROM content WHERE enabled = 1 ORDER BY record_num ASC LIMIT 6700000 , 50000
SELECT record_num, title FROM content WHERE enabled = 1 ORDER BY record_num ASC LIMIT 6750000 , 50000
SELECT record_num, title FROM content WHERE enabled = 1 ORDER BY record_num ASC LIMIT 6800000 , 50000
i am using order by just to make sure i am getting each and every row in the output .
2) whats the default order by when i dont specify one ?
.e.g. lets say i am not using any order by in my query. and doing select in 50k batches
SELECT * FROM content WHERE enabled = 1 LIMIT 0,50000
SELECT * FROM content WHERE enabled = 1 LIMIT 50000,50000
and so on until end of the records.
and at the same time many rows are getting inserted in the table.
so how can i make sure that recently inserted rows gets in the SELECT query ?
how does this works exactly ? when no ORDER BY is used ?
how does mysql keeps track of whats already been included in SELECT and what to include in next SELECT ?
Thanks for your time.