MySQL Forums
Forum List  »  InnoDB

Innodb full table scan if query return 0 rows
Posted by: thomas schiavello
Date: August 20, 2016 03:59AM

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?

Options: ReplyQuote

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.