Work out database query cost instantly with clear inputs, formula shown and shareable results.
Databases read whole pages, so the cost of a query is pages touched rather than rows matched. Scanning 250,000 rows to return 40 means a selectivity of 0.016 percent and thousands of pages read for a handful of results — the classic signature of a missing index. Rows per page follows from row size and page size, which is also why narrow rows make scans cheaper.
Page-based cost
rows per page = floor(page size / row size); pages read = ceil(rows scanned / rows per page); selectivity = rows returned / rows scanned
Below roughly 5 to 10 percent an index usually wins. Above that, the random I/O of index lookups plus row fetches can cost more than a sequential scan.
Substantially. If the index contains every column the query needs, the base table is never touched, which removes the row-fetch cost entirely.