mySql Optimize date queries and inserts

High Level of use



DROP INDEX VENTAS_fecven_idx ON VENTAS

select * from VENTAS where date(fecven)='2019-03-09' #~2.81

select * from VENTAS where fecven >='2019-03-09 00:00:00' and fecven<'2019-03-10 00:00:00'  #~2.9


select * from VENTAS where fecven Between '2019-03-09 00:00:00' and '2019-03-10 00:00:00'  #~3.6


CREATE INDEX VENTAS_fecven_idx ON VENTAS (fecven)


select * from VENTAS where date(fecven)='2019-03-09' #~2.794

select * from VENTAS where fecven >='2019-03-09 00:00:00' and fecven<'2019-03-10 00:00:00'  #~0.265

select * from VENTAS where fecven Between '2019-03-09 00:00:00' and '2019-03-10 00:00:00'  #~0.261

CREATE INDEX MOVPRO_fecmov_idx ON MOVPRO (fecmov)
#change date(fecmov) between f1 and f2
#to         fecmov between f1 and f2  #implicit add 00:00:00

DROP INDEX MOVPRO_fecmov_idx ON MOVPRO


Setting mysql

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 128;
[mysqld]
bulk_insert_buffer_size=128M

References
[1] Insert https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html
[2] Amazon Best Practices https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_BestPractices.html