MySQL: What is the most optimized way to use datetime type field in query

Question

I am working on optimization of some slow queries in my application. I found a query in which datetime comparison has been used. Below is the subquery:

select * from jos_content c where c.visible = 1 AND (c.publish_down = '0000-00-00 00:00:00' OR c.publish_down >= 2013-12-19) AND c.state = 1 AND c.deleted = 0;

Below is the explain result:

           id: 1
  select_type: SIMPLE
        table: c
         type: ref
possible_keys: idx_publish_down,idx_content
          key: idx_content
      key_len: 2
          ref: const,const
         rows: 17404
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

What is the best way to handle datetime comparison towards improving such queries.

Below is the table structure:

Field,Type,Null,Key,Default,Extra
id,"int(11) unsigned","NO","PRI",\N,"auto_increment"
title,"varchar(255)","NO","MUL","",""
alias,"varchar(255)","NO","","",""
title_alias,"varchar(255)","NO","","",""
introtext,"mediumtext","NO","",\N,""
fulltext,"mediumtext","NO","",\N,""
state,"tinyint(3)","NO","","0",""
sectionid,"int(11) unsigned","NO","MUL","0",""
mask,"int(11) unsigned","NO","","0",""
catid,"int(11) unsigned","NO","MUL","0",""
created,"datetime","NO","MUL","0000-00-00 00:00:00",""
created_by,"int(11) unsigned","NO","MUL","0",""
created_by_alias,"varchar(255)","NO","","",""
modified,"datetime","NO","MUL","0000-00-00 00:00:00",""
modified_by,"int(11) unsigned","NO","","0",""
checked_out,"int(11) unsigned","NO","MUL","0",""
checked_out_time,"datetime","NO","","0000-00-00 00:00:00",""
publish_up,"datetime","NO","MUL","0000-00-00 00:00:00",""
publish_down,"datetime","NO","MUL","0000-00-00 00:00:00",""
images,"text","NO","",\N,""
urls,"text","NO","",\N,""
attribs,"text","NO","",\N,""
version,"int(11) unsigned","NO","","1",""
parentid,"int(11) unsigned","NO","","0",""
ordering,"int(11)","NO","","0",""
metakey,"text","NO","",\N,""
metadesc,"text","NO","",\N,""
access,"int(11) unsigned","NO","MUL","0",""
hits,"int(11) unsigned","NO","","0",""
metadata,"text","NO","",\N,""
owner_id,"int(10) unsigned","NO","",\N,""
contact_id,"int(10) unsigned","NO","",\N,""
logo_path,"text","YES","",\N,""
high_resolution,"tinyint(2) unsigned","NO","","0",""
deleted,"tinyint(2) unsigned","NO","","0",""
short_desc,"text","NO","",\N,""
visible,"tinyint(2) unsigned","NO","MUL","1",""
approved,"tinyint(2) unsigned","YES","",\N,""
approved_by,"int(10) unsigned","YES","",\N,""
nda_flag,"text","YES","",\N,""
isSolutionPak,"text","YES","",\N,""
temp_securitylevel,"text","YES","",\N,""
temp_oldContentId,"int(10) unsigned","YES","MUL",\N,""
upFlag,"tinyint(2) unsigned","NO","","1",""
tempAssetClass,"text","YES","",\N,""
tempDBName,"text","YES","",\N,""
is_obsolete,"tinyint(2) unsigned","YES","MUL","0",""
attachment_desc,"text","YES","",\N,""
IsRHSSImgLibAsset,"tinyint(2) unsigned","YES","MUL","0",""
IsDownloadable,"tinyint(2) unsigned","YES","","1",""

Below are the indexes

*************************** 1. row ***************************
        Table: jos_content
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 30831
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_section
 Seq_in_index: 1
  Column_name: sectionid
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_access
 Seq_in_index: 1
  Column_name: access
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_checkout
 Seq_in_index: 1
  Column_name: checked_out
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 5. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_catid
 Seq_in_index: 1
  Column_name: catid
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 6. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_createdby
 Seq_in_index: 1
  Column_name: created_by
    Collation: A
  Cardinality: 354
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 7. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_id_jos_content
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 30831
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 8. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_title_jos_content
 Seq_in_index: 1
  Column_name: title
    Collation: A
  Cardinality: 30831
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 9. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_oldcid_jos_content
 Seq_in_index: 1
  Column_name: temp_oldContentId
    Collation: A
  Cardinality: 6166
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 10. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_rhssimg_jos_content
 Seq_in_index: 1
  Column_name: IsRHSSImgLibAsset
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 11. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_obsolete_jos_content
 Seq_in_index: 1
  Column_name: is_obsolete
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 12. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: IDX_MODIFIED
 Seq_in_index: 1
  Column_name: modified
    Collation: A
  Cardinality: 30831
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 13. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_publish_down
 Seq_in_index: 1
  Column_name: publish_down
    Collation: A
  Cardinality: 15415
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 14. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_content
 Seq_in_index: 1
  Column_name: visible
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 15. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_content
 Seq_in_index: 2
  Column_name: state
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 16. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_content
 Seq_in_index: 3
  Column_name: publish_up
    Collation: A
  Cardinality: 7707
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 17. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_content
 Seq_in_index: 4
  Column_name: deleted
    Collation: A
  Cardinality: 7707
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 18. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_created
 Seq_in_index: 1
  Column_name: created
    Collation: A
  Cardinality: 30831
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 19. row ***************************
        Table: jos_content
   Non_unique: 1
     Key_name: idx_publish_up
 Seq_in_index: 1
  Column_name: publish_up
    Collation: A
  Cardinality: 7707
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

What do you mean by "improving" the query? Make it run faster? — Thomas Kejser, Jan 5, 2014 at 1:34
yes Thomas, improving the query means reducing the execution time of a query. This question doesn't deserve for down vote. — ursitesion, Jan 5, 2014 at 13:49
Some information is lacking then. Please provide: 1) The SHOW TABLE (so it is clear which indexes are in place and what data types are used). 2) How fast is the query? 3) How fast do you need it to be? 4) Is the query doing I/O or reading from memory? — Thomas Kejser, Jan 5, 2014 at 14:09
Which indexes are on the table and which columns do they contain? Do you need all column in the query (SELECT *) or could you do with fewer? — Thomas Kejser, Jan 5, 2014 at 14:22

Thomas Kejser · Accepted Answer · 2014-01-05 15:08:26Z

Thanks for providing the details.

First of all, the filter on the query itself is what it is - there is nothing special you can do to improve datetime as opposed to any other type of column.

However, from the index definitions, it looks like the indexing on the table is not well suited to support this query.

The mySQL optimiser can choose between two indexes:

idx_content with columns: visible, state, publish_up, deleted

and:

idx_publish_down with the column: publish_down

From the cardinality of the indexes, is is clear that the column publish_down has the most potential for being filtered effectively (and thus, reduce the number of rows to scan). However, mySQL picks idx_content instead. This is likely because it believes the selectivity of publish_down is weaker than the low cardinality columns in idx_content. But you can have the best of both words...

There are two things you can do to improve the response time of this query:

Add the columns visible, state, deleted to idx_publish_down (after the column publish_down. This makes the index much better suited for this query
If possible, return a much smaller number of columns - ideally only the columns from the PRIMARY index and the ones from idx_publish_down. (General best practise: NEVER use SELECT * in user queries, it makes it very hard to index effectively and maintain the application)

If you do 1 above, you should see idx_publish_down being used in the EXPLAIN output. Before you do this, it might also be worth forcing the idx_publish_down index to see if that is fast enough.

If you also do 2, then you should see the EXPLAIN output contain this string: "using index".

Stack Exchange Network

MySQL: What is the most optimized way to use datetime type field in query

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
performance
mysql-5.1
datetime
query-performance
or ask your own question.

Hot Network Questions

MySQL: What is the most optimized way to use datetime type field in query

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlperformancemysql-5.1datetimequery-performance or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
performance
mysql-5.1
datetime
query-performance
or ask your own question.