[SOLVED] MySql, SELECT * FROM with Indexed columns


I’m working with a table in MySql that has an int indexed column called “serial”. This table has around 2 million rows.

If I apply a select statement with this column in this way:

 SELECT serial FROM Table WHERE Serial=12345

this returns the data in around < 1 sec.

However, if I use a SELECT * query in this same table, this query takes around 78 seconds

I know it is not useful to apply indexes to all the columns in the table, how can I optimize/minimize the query response time if I need to get all the columns from a specific serial?

 SELECT * FROM Table WHERE serial= 12345

The results from EXPLAIN :

SELECT serial:

1 SIMPLE tableName index idx_tablename_serial 5 6686620 Using index


1 SIMPLE agsensepivotreadings ALL (null values) 6686620

Please, any sugggestion or guide will be very appreciated.


There is part answer for your question.


But also, maybe you should consider partitioning your table:

Answered By – z48o0

Answer Checked By – Mildred Charles (BugsFixing Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *