Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:
share|improve this question
    
What do you mean by "improving" the query? Make it run faster? –  Thomas Kejser Jan 5 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 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 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 at 14:22
    
@ThomasKejser:updated my question –  ursitesion Jan 5 at 14:23
show 2 more comments

1 Answer

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:

  1. 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
  2. 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".

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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