Innodb full table scan if query return 0 rows
hello mysql community
i have a question about indexes in innodb
i have a db with some innodb tables. the size of the entire db is about 1.5TB
every table has about a bilion of rows
the table index are correctly created and queries are fast
most of query are created with this conditions
SELECT * FROM table WHERE created BETWEEN (date1) AND (date2) AND plug = (n)
plug is a INT
date1 and date2 are DATETIME
if i make a query with a date range where i know that there are some rows, queries are really fast
if i make a query where there are no rows in that date range that has plug field with the value in the query, queries are really slow, like a full table scan has been performed.
the index of the table is a BTREE with the fields (created, plug) in this order
as far as i know, being the index a BTREE if no rows are found in the index in the created date range, the result should be returned immediatly. the same if there are rows in the index with the created value, but not with the plug value. no rows in the index mean no rows in the table, isn't?
why make a full table scan?
Subject
Views
Written By
Posted
Innodb full table scan if query return 0 rows
2015
August 20, 2016 03:59AM
1016
August 20, 2016 09:27AM
940
August 21, 2016 09:40PM
934
August 24, 2016 10:58AM
962
August 24, 2016 05:32PM
926
August 25, 2016 01:31AM
994
August 25, 2016 07:37PM
1037
August 27, 2016 04:31AM
1015
August 27, 2016 01:39PM
1103
August 29, 2016 03:27AM
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.