AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Mysql speed up query9/12/2023 ![]() ![]() | 1 | SIMPLE | site_ip | ref | PRIMARY,idx_site_ip_id,idx_site_ip_did | idx_site_ip_did | 5 | | 1 | Using where Using index | | 1 | SIMPLE | site | ALL | PRIMARY | NULL | NULL | NULL | 325 | Using where Using temporary Using filesort | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | Mysql> explain select avg(snrup) as snrup,avg(snrdown) as snrdown,site_ip.id,dslam_region from dsl_data,dslam_ports,site_ip,site where site.dslam_region='itr' > and site_ip.did=site.id and dslam_ports.dslam_id=site_ip.id and dsl_data.port_id=dslam_ports.id and dsl_data.time between "" and "" group by site.id order by snrdown desc Mysql> select avg(snrup) as snrup,avg(snrdown) as snrdown,site_ip.id,dslam_region from dsl_data,dslam_ports,site_ip,site where site.dslam_region='itr' ) ENGINE=InnoDB AUTO_INCREMENT=562 DEFAULT CHARSET=latin1 `dslam_region` varchar(100) DEFAULT NULL, `dslam_vendor` varchar(200) DEFAULT NULL, ) ENGINE=InnoDB AUTO_INCREMENT=533 DEFAULT CHARSET=latin1 ) ENGINE=InnoDB AUTO_INCREMENT=194342 DEFAULT CHARSET=latin1 ) ENGINE=InnoDB AUTO_INCREMENT=133606144 DEFAULT CHARSET=latin1 | `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, I want to speed up the following query as i add 7 million entries to dsl_data table every month and now after 6 months it takes too much time to execute, currently i move data to another table after every few months to speed up this query but i am looking for other ways for it, i was thinking to use partitioning on dsl_data.time or move this field to int and use unix timestamps, kindly help me fix it.
0 Comments
Read More
Leave a Reply. |