MySQL Forums
Forum List  »  Performance

Re: Utilize maximum system resources for better performance
Posted by: Ronald Brown
Date: September 16, 2015 11:39AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilize maximum system resources for better performance
1061
September 16, 2015 11:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.