[SOLVED] MySql, SELECT * FROM with Indexed columns

Issue

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

SELECT * :

1 SIMPLE agsensepivotreadings ALL (null values) 6686620

Please, any sugggestion or guide will be very appreciated.

Solution

There is part answer for your question.

https://stackoverflow.com/a/3211164/2957840

But also, maybe you should consider partitioning your table:
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

Answered By – z48o0

Answer Checked By – Mildred Charles (BugsFixing Admin)

Leave a Reply

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