Databases
StackOverflow2013
Postsdbo
POSimple MySQL indexing issue
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSimple MySQL indexing issue
    primarykey
    data
    text
    <p>I ve got this table:</p> <pre><code>CREATE TABLE IF NOT EXISTS `test1_nopart` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `idAccount` int(10) unsigned NOT NULL, `data` mediumint(8) unsigned NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`), KEY `date` (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; </code></pre> <p>I fill this table with 10 000 000 rows. The repartition by date is homogenous</p> <pre><code>EXPLAIN SELECT * FROM `test1_nopart` WHERE date = "2014-03-04" </code></pre> <p>Here is the result</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1_nopart ALL NULL NULL NULL NULL 7875981 Using where </code></pre> <p>=> without the explain 3.6sec for a result of 3000 rows (approx)</p> <p>As you can see the index is not used and it is not part of the possible_keys column!</p> <p>the same request with a covering index way</p> <pre><code>EXPLAIN SELECT date FROM `test1_nopart` WHERE date = "2014-03-04" </code></pre> <p>the result:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1_nopart index NULL date 3 NULL 7875981 Using where; Using index </code></pre> <p>=> without the explain 2.8sec for a result of 3000 rows (approx)</p> <p>Why MySQL doesn't use properly this index(DATE) ???</p> <p>Info: - VM Server (our dev environment, I don't know what is the hardware composition) - MySQL 5.5.8</p> <pre><code>SHOW INDEX FROM test1_nopart </code></pre> <p>result:</p> <pre><code>Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment test1_nopart 0 PRIMARY 1 id A 7875981 NULL NULL BTREE test1_nopart 1 date 1 date A 6077 NULL NULL BTREE </code></pre> <ul> <li>For date 2014-03-04 => 3134 rows</li> <li>The total (rollup) => 7 875 488 </li> <li>There is 2556 differents 'date' values in the table</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USnemenems
    1. USnemenems
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSimple MySQL indexing issue
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat's the output of `SHOW INDEX FROM test1_nopart`, especially index cardinality? Also, why name your columns as MySQL's reserved words?
      singulars
      1. POSimple MySQL indexing issue
      1. USN.B.
    2. COOuch, seems that 6077 is very low...
      singulars
      1. POSimple MySQL indexing issue
      1. USnemenems
    3. COCardinality is not the real problem. When you run the query `SELECT COUNT(1) datecount,`date` FROM test1_nopart GROUP BY `date` WITH ROLLUP;` you will see the real cardinality. You will also see how many rows 2014-03-14 occupies.
      singulars
      1. POSimple MySQL indexing issue
      1. USRolandoMySQLDBA
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload