Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>Introduction</h2> <p>I like your question so I've played a bit with MySQL and tried to find the source of the problem. For that, I've created some tests.</p> <h2>Data</h2> <p>I've generated 100.000 rows of sample data using a tool called <a href="http://forge.mysql.com/wiki/RandomDataGenerator" rel="noreferrer">Random Data Generator</a> (the documentation is a bit out-dated I think, but it works). The configuration file I've passed to <code>gendata.pl</code> is as follows.</p> <pre class="lang-perl prettyprint-override"><code>$tables = { rows =&gt; [100000], names =&gt; ['ebay_items'], engines =&gt; ['MyISAM'], pk =&gt; ['int auto_increment'] }; $fields = { types =&gt; ['datetime', 'int'], indexes =&gt; [undef] }; $data = { numbers =&gt; [ 'tinyint unsigned', 'smallint unsigned', 'smallint unsigned', 'mediumint unsigned' ], temporals =&gt; ['datetime'] }; </code></pre> <p>I've ran two separate batch of tests: one that used a MyISAM table, and another that used InnoDB. (So basically you replace MyISAM with InnoDB in the above snippet.)</p> <h2>Table</h2> <p>The tool creates a table where the columns are called <code>pk</code>, <code>col_datetime</code> and <code>col_int</code>. I've renamed them to match your table's columns. The resulting table is just below.</p> <pre class="lang-none prettyprint-override"><code>+---------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+----------------+ | endtime | datetime | YES | MUL | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | price | int(11) | YES | MUL | NULL | | +---------+----------+------+-----+---------+----------------+ </code></pre> <h2>Indices</h2> <p>The tool creates no indices, because I'd liked it to create them by hand.</p> <pre class="lang-sql prettyprint-override"><code>CREATE INDEX `endtime` ON `ebay_items` (endtime, price); CREATE INDEX `price` ON `ebay_items` (price, endtime); CREATE INDEX `endtime_only` ON `ebay_items` (endtime); CREATE INDEX `price_only` ON `ebay_items` (price); </code></pre> <h2>Query</h2> <p>The query I've used.</p> <pre class="lang-sql prettyprint-override"><code>SELECT `ebay_items`.* FROM `ebay_items` FORCE INDEX (`endtime|price|endtime_only|price_only`) WHERE (`endtime` &gt; '2009-01-01' - INTERVAL 1 MONTH) ORDER BY `price` DESC </code></pre> <p><sub>(Four different query using one of the indices. I've used <code>2009-01-01</code> instead of <code>NOW()</code> because the tool seems to generate dates around 2009.)</sub></p> <h2>Explain</h2> <p>Here is the output of <code>EXPLAIN</code> for the query above for each indices on a MyISAM (top) and an InnoDB (bottom) table.</p> <h3>endtime</h3> <pre class="lang-none prettyprint-override"><code> id: 1 select_type: SIMPLE table: ebay_items type: range possible_keys: endtime key: endtime key_len: 9 ref: NULL rows: 25261 Extra: Using where; Using filesort id: 1 select_type: SIMPLE table: ebay_items type: range possible_keys: endtime key: endtime key_len: 9 ref: NULL rows: 21026 Extra: Using where; Using index; Using filesort </code></pre> <h3>price</h3> <pre class="lang-none prettyprint-override"><code> id: 1 select_type: SIMPLE table: ebay_items type: index possible_keys: NULL key: price key_len: 14 ref: NULL rows: 100000 Extra: Using where id: 1 select_type: SIMPLE table: ebay_items type: index possible_keys: NULL key: price key_len: 14 ref: NULL rows: 100226 Extra: Using where; Using index </code></pre> <h3>endtime_only</h3> <pre class="lang-none prettyprint-override"><code> id: 1 select_type: SIMPLE table: ebay_items type: range possible_keys: endtime_only key: endtime_only key_len: 9 ref: NULL rows: 11666 Extra: Using where; Using filesort id: 1 select_type: SIMPLE table: ebay_items type: range possible_keys: endtime_only key: endtime_only key_len: 9 ref: NULL rows: 21270 Extra: Using where; Using filesort </code></pre> <h3>price_only</h3> <pre class="lang-none prettyprint-override"><code> id: 1 select_type: SIMPLE table: ebay_items type: index possible_keys: NULL key: price_only key_len: 5 ref: NULL rows: 100000 Extra: Using where id: 1 select_type: SIMPLE table: ebay_items type: index possible_keys: NULL key: price_only key_len: 5 ref: NULL rows: 100226 Extra: Using where </code></pre> <p>Based on these I've decided to use the <code>endtime_only</code> index for my tests, because I had to run queries against a MyISAM and an InnoDB table too. But as you can see the most logical <code>endtime</code> index seems to be the best.</p> <h2>Test</h2> <p>For testing the efficiency of the query (regarding the generated I/O activity) with a MyISAM and InnoDB table I've written the following simple Java program.</p> <pre class="lang-java prettyprint-override"><code>static final String J = "jdbc:mysql://127.0.0.1:3306/test?user=root&amp;password=root"; static final String Q = "SELECT * FROM ebay_items FORCE INDEX (endtime_only) WHERE (endtime &gt; '2009-01-01'-INTERVAL 1 MONTH) ORDER BY price desc;"; public static void main(String[] args) throws InterruptedException { for (int i = 0; i &lt; 1000; i++) try (Connection c = DriverManager.getConnection(J); Statement s = c.createStatement()) { TimeUnit.MILLISECONDS.sleep(10L); s.execute(Q); } catch (SQLException ex) { ex.printStackTrace(); } } </code></pre> <h2>Setup</h2> <p>I was running the Windows binary of MySQL 5.5 on Dell Vostro 1015 laptop, Intel Core Duo T6670 @ 2.20 GHz, 4 GB RAM. The Java program was communicating with the MySQL server process via TCP/IP.</p> <h2>State</h2> <p>I've captured the state of the <code>mysqld</code> process before and after running my tests against the table using MyISAM and InnoDB (using <a href="http://technet.microsoft.com/en-us/sysinternals/bb896653" rel="noreferrer">Process Explorer</a>).</p> <h3>Before</h3> <p><img src="https://i.stack.imgur.com/RT88F.png" alt="mysqld Performance tab"></p> <p><img src="https://i.stack.imgur.com/Cowo1.png" alt="mysqld Disk and Network tab"></p> <h3>After—<em>MyISAM</em></h3> <p><img src="https://i.stack.imgur.com/hHR3Q.png" alt="mysqld Performance tab / MyISAM"></p> <p><img src="https://i.stack.imgur.com/aMerW.png" alt="mysqld Disk and Network tab / MyISAM"></p> <h3>After—<em>InnoDB</em></h3> <p><img src="https://i.stack.imgur.com/DV2AM.png" alt="mysqld Performance tab / InnoDB"></p> <p><img src="https://i.stack.imgur.com/ETmqO.png" alt="mysqld Disk and Network tab / InnoDB"></p> <h2>Conclusion</h2> <p>Basically the two runs differ only in the number of individual I/O reads, which is quite large when the table used the MyISAM engine. The two tests ran for 50–60 seconds both. The CPU's maximum load in case of the MyISAM engine was around 42 percent while using InnoDB it was around 38.</p> <p>I'm not quite sure what are the implication of the high number of I/O reads, but in this case smaller is better (probably). If you have some more columns in your table (other than the one you've specified) and have some non-default MySQL configuration (regarding buffer sizes and such), it's possible that MySQL would use disk resources.</p>
    singulars
    1. This table or related slice is empty.
    1. POOptimizing a mysql index on an ordered, range query
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USKohányi Róbert
    1. USKohányi Róbert
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks a lot for your well thought out answer. I have clarified my problem with the query in my description. The query pegs my disk I/O and causes contention with other resources. You make a good point about your 3rd run reducing the amount of row on which the filesort would have to traverse. I'll have to do some more experimentation with the single column `endtime` query and get back to you.
      singulars
      1. PO
      1. USWill Sulzer
    2. CO@WillSulzer I assume that the `filesort` is the culprit that causes I/O pegs. You can benchmark the actual `filesort` operation's duration and the number of sorted rows (see the _[`filesort` Probes](http://dev.mysql.com/doc/refman/5.5/en/dba-dtrace-ref-filesort.html)_ section in the manual). It would be interesting to know that how much _performance_ is gained/lost, by using the individual indices in the query (relatively to each of the runs at least). Does _caching_ an option for you? If not, you probably have to redesign your scheme, but to help you with that, more details are needed.
      singulars
      1. PO
      1. USKohányi Róbert
    3. COthis is a bit overdo to me, do you have any conclusive to show innodb do better?
      singulars
      1. PO
      1. USajreal
 

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