Databases
StackOverflow2013
Postsdbo
POindex help for a MySQL query using greater-than operator and ORDER BY
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POindex help for a MySQL query using greater-than operator and ORDER BY
    primarykey
    data
    text
    <p>I have a table with at least a couple million rows and a schema of all integers that looks roughly like this:</p> <pre><code>start stop first_user_id second_user_id </code></pre> <p>The rows get pulled using the following queries:</p> <pre><code>SELECT * FROM tbl_name WHERE stop &gt;= M AND first_user_id=N AND second_user_id=N ORDER BY start ASC SELECT * FROM tbl_name WHERE stop &gt;= M AND first_user_id=N ORDER BY start ASC </code></pre> <p>I cannot figure out the best indexes to speed up these queries. The problem seems to be the ORDER BY because when I take that out the queries are fast.</p> <p>I've tried all different types of indexes using the standard index format:</p> <pre><code>ALTER TABLE tbl_name ADD INDEX index_name (index_col_1,index_col_2,...) </code></pre> <p>And none of them seem to speed up the queries. Does anyone have any idea what index would work? Also, should I be trying a different type of index? I can't guarantee the uniqueness of each row so I've avoided UNIQUE indexes.</p> <p>Any guidance/help would be appreciated. Thanks!</p> <p>Update: here are a list of the indexes, I didn't include this originally since I've taken a shotgun approach and added a ton of indexes looking for one that works:</p> <pre><code>start_index: [start, first_user_id, second_user_id] stop_index: [stop, first_user_id, second_user_id] F1_index: [first_user_id] F2_index: [second_user_id] F3_index: [another_id] test_1_index: [first_user_id,stop,start] test_2_index: [first_user_id,start,stop] test_3_index: [start,stop,first_user_id,second_user_id] test_4_index: [stop,first_user_id,second_user_id,start] test_5_index: [stop,start] </code></pre> <p>And here is the EXPLAIN output.</p> <pre><code>*************************** 1. row *************************** id: 1 select_type: SIMPLE table: listing type: index_merge possible_keys: stop_index,F1_index,F3_index,test_1_index,test_2_index,test_4_index,test_5_index key: F1_index,F3_index key_len: 5,5 ref: NULL rows: 238 Extra: Using intersect(F1_index,F3_index); Using where; Using filesort </code></pre> <p><strong>Update for posterity</strong></p> <p>We ended up completely re-evaluating how we were querying the table and chose these indexes:</p> <pre><code>index_select_1: [first_user_id,start,stop] index_select_2: [first_user_id,second_user_id,start,stop] </code></pre> <p>and then we select on the table with queries like these:</p> <pre><code>SELECT * FROM tbl_name WHERE first_user_id=N AND start &gt;= M ORDER BY start ASC SELECT * FROM tbl_name WHERE first_user_id=N AND second_user_id=N AND start &gt;= M ORDER BY start ASC </code></pre> <p>Thanks to everyone that answered, you really helped me think through the problem.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJaymon
    1. USJaymon
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POindex help for a MySQL query using greater-than operator and ORDER BY
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POindex help for a MySQL query using greater-than operator and ORDER BY
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POindex help for a MySQL query using greater-than operator and ORDER BY
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPost the output of EXPLAIN on your queries.
      singulars
      1. POindex help for a MySQL query using greater-than operator and ORDER BY
      1. USnos
    2. COCould you try to do an `EXPLAIN` on the query and paste it here among the list of indexes ? Might help to see what mysql is trying to do.
      singulars
      1. POindex help for a MySQL query using greater-than operator and ORDER BY
      1. USRomuald Brunet
    3. COWait, where did another_id come from? That was never mentioned and I am confused about why the query analyzer would think that it was relevant since it is not even being used in the WHERE.
      singulars
      1. POindex help for a MySQL query using greater-than operator and ORDER BY
      1. USmalonso
 

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