Index on a table that fetches all fields, all rows

Question

What is the proper way to index a utility table, that holds config values?

Consider, for example, a simple table having 15 fields with a few dozen rows, configuration values for some application widget.

This widget is loaded and called numerous times on each page load, the entire table is fetched.

The query to optimize is akin to a select *, as it selects all fields:

SELECT t0.id AS id_1, t0.created_at AS created_at_2, t0.updated_at AS updated_at_3, t0.color AS color_4, t0.name AS name_5, t0.other_name AS other_name_6, t0.license AS license_7, t0.phone AS phone_8, t0.phone1800 AS phone1800_9, t0.fax AS fax_10, t0.address AS address_11, t0.city AS city_12, t0.province AS province_13, t0.postcode AS postcode_14, t0.latitude AS latitude_15, t0.longitude AS longitude_16, t0.abc AS abc_17, t0.sig_name AS sig_name_18 FROM widget t0;

As you may have guessed by the format, it is generated by an ORM.

I am wondering if and how it is possible to examine a table used in this manner.

A query fetching all records without any WHERE clause or other restrictions won't necessarily benefit from an index at all. An index is useful when you want to select a subset of records from an entire table(s). — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 12, 2018 at 2:51
@TimBiegeleisen - My initial impression also, but why would this appear in the slow_query_log, that I enabled to catch the slow queries? It is a simple query returning 4 rows. The fact it shows in slow_query log is what stumped me. — Stephane Gosselin
– Stephane Gosselin, Commented Jul 12, 2018 at 2:54

The Impaler · Accepted Answer · 2018-07-12 02:52:58Z

a simple table having 15 fields with a few dozen rows

No need to index anything. It's just a few rows, that barely place any load on the database.

Alternatively, load the table in memory, and respond from the cache instead of querying the database each time.

Collectives™ on Stack Overflow

Index on a table that fetches all fields, all rows

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related