Mysql query performance -

Question

I have the following mysql query

explain SELECT count(*) as assetCount 
          FROM MdhRawAsset m 
         where sysCreationDate > date_add(now(), INTERVAL -1 DAY) 
           AND sysCreationDate <= now() 
           AND itemType = 'TS';

Results:

| id | select_type | table | type |possible_keys                                                    
|  1 | SIMPLE      | m     | range | MdhRawAsset_on_sysCreationDate, MdhRawAsset_itemType            
---------------------------------------------------------------------
|Key                          |Key_len  | ref  | rows  | Extra       |  
MdhRawAsset_on_sysCreationDate| 8       | NULL | 53269 | Using where |

Questions:

• How to find out the execution time of this request?
• The table MdhRawAssetcontains 37.5 million data, is there a better way to write this query?

Answer 1

• The query execution time depends on too many questions to determine how long it takes. Upload to server, number of lines, etc. The best you can do is run it in a typical boot and see how long it takes.

• , , , , - , .

Answer 2

• count (id) . , , *, .

Answer 3

-, , , , .

w.r.t. ,

... KEY itemType_sysCreationDate (itemType, sysCreationDate) ...

. . , .

, "now()" , , mysql - , , , . , MySQL.;)

Answer 4

? .

, , -

• - , .

• - , datawarehousing . " " ( ), ( ).

, , - . , , _summary_count = existing_summary_count + 1, , .

Answer 5

MS-SQL, , , - . , . , , , show index from <table> explain ....

itemType sysCreationDate. .