1

I have query

SELECT max(ParameterID) FROM modelparam_ems

Table Name: modelparam_ems

No Of Rows: 9000

It takes time to retrieve max value of parameterId, ParameterId in Unique value.

Output Time :

(Below row copied from Mysql Workbench).

13:51:15    SELECT max(ParameterID) FROM modelparam_ems 1 row(s) returned   0.109 sec / 0.000 sec
1
  • 2
    Execute create index idx_modelparam_ems_parameterid on modelparam_ems(ParameterID); if you haven't an index on it already and it will run almost lightning-fast.
    – tombom
    Jun 19, 2015 at 8:41

1 Answer 1

0

You have two options:

  1. as TomBom Mentioned, you need to create an index on ParameterID column using this statement

    create index idx_modelparam_ems_parameterid on modelparam_ems(ParameterID);
    
  2. If your ParameterID column is AUTO_INCREMENT,

    you can get your max id by getting the next AUTO_INCREMENT value from the following query

    SELECT AUTO_INCREMENT-1
    FROM information_schema.tables
    WHERE table_name = 'modelparam_ems'
    AND table_schema = 'YourDBName' ;
    

    If the engine is MyISAM and you are sure that there is no gap between two ids, you can find your max id by getting number of rows in the table as below. ROWS column contains the value for number of rows.

    EXPLAIN SELECT * FROM modelparam_ems;
    

    Note that In Innodb the value returned in rows is an estimation of total rows. So it is not as accurate as MyISAM.

Disclaimer

Option 2 may be both unreliable, and subjected to failure. Use it only if option 1 is not possible and you can tolerate the failure.

7
  • ParameterId is not auto incremented
    – Sush
    Jun 19, 2015 at 8:59
  • then index it is, it might get a bit better if your ParameterID is INT UNSIGNED NOT NULL
    – Masoud
    Jun 19, 2015 at 9:05
  • The 2nd suggestion is not safe. It may not give correct result. Rows can be deleted from tables, auto-increment may not be 1, it may even be negative. Jun 19, 2015 at 9:05
  • @ypercube when is it not safe? when we are inserting rows in Bulk?
    – Masoud
    Jun 19, 2015 at 9:07
  • 2
    (unsafe meaning: not correct result) I added 3 reasons above. Fourth reason: the table is empty. Jun 19, 2015 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.