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 :
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.
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)