Index help for MySQL query using statement greater than ORDER BY

Question

I have a table with at least a couple of million rows and a diagram of all integers that looks something like this:

start stop first_user_id second_user_id 

Strings are pulled using the following queries:

 SELECT * FROM tbl_name WHERE stop >= M AND first_user_id=N AND second_user_id=N ORDER BY start ASC SELECT * FROM tbl_name WHERE stop >= M AND first_user_id=N ORDER BY start ASC 

I can’t determine the best indexes to speed up these queries. The problem is that ORDER BY, because when I accept this, queries are fast.

I tried all different types of indexes using the standard index format:

 ALTER TABLE tbl_name ADD INDEX index_name (index_col_1,index_col_2,...) 

And none of them seem to speed up query execution. Does anyone know which index will work? Also, should I try to use a different type of index? I cannot guarantee the uniqueness of each row, so I avoided UNIQUE indexes.

Any guidance / help would be appreciated. Thanks!

Update: here is a list of indexes, I did not include this initially, since I took a shotgun approach and added a ton of indexes that are looking for the one that works:

 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] 

And here is the output of EXPLAIN.

 *************************** 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 

Update for posterity

We completed a complete re-evaluation of how we requested the table, and selected these indexes:

 index_select_1: [first_user_id,start,stop] index_select_2: [first_user_id,second_user_id,start,stop] 

and then we select in the table such queries as:

 SELECT * FROM tbl_name WHERE first_user_id=N AND start >= M ORDER BY start ASC SELECT * FROM tbl_name WHERE first_user_id=N AND second_user_id=N AND start >= M ORDER BY start ASC 

Thanks to everyone who answered, you really helped me solve this problem.

Answer 1

Could you display the sample table and EXPLAIN results? Because, obviously, this is not the same situation, and we do not know, maybe you were mistaken in abstracting your real query only by looking at the provided EXPLAIN results. If you do not want to display too much structure, then cancel it and create the structure of the cited tables and get the EXPLAIN result (perhaps you will understand the problem this way).

Now one thing is that sorting uses filesort , which is bad.

To simplify (we will return to it) - complex indexes useful for sorting should have a sorting field in front.

Example idx (ID, Start)

 ID Start 1 5 8 8 10 25 2 3 9 10 40 41 42 42 ... 

In the above example, the index doesn’t really help sorting unless you have a condition in which the identifier is limited to only one value.

But , this exception is important because you have one row selectivity for one or both id fields.

So, from your indexes, the only indexes starting from the beginning are

 start_index: [start, first_user_id, second_user_id] test_3_index: [start,stop,first_user_id,second_user_id] 

Mysql ignores index

 start_index: [start, first_user_id, second_user_id] 

because he has the best choice in terms of selectivity - he will need to scan the index using this index, and he has indexes that will allow him to index the intersection, directly proceeding to the (unsorted) results. He expects better selectivity from the intersection, and selectivity drives the planer.

Once the result is obtained, mysql should understand that it can use a different index to sort the results, but it seems that it cannot see how cheap it would be.

So, to help the planer, you can create an index that will use your sample unit with an index, for example:

 two_ids_with_sort: [first_user_id, second_user_id, start] 

I assume that the above will work very well on your second request, where you have conditions for both identifiers, which gives you access to the pre-configured pointers to the initial entry. The following query should do the same for the first query:

 one_id_with_sort: [first_user_id, start] 

Only if you end up with a lot of records in the result sets would I look at indexing it.

There are two ways: a) adding a field to the end of the index b) creating two more similar indexes with a stop instead of the beginning (an index intersection can be used here, and a wider range of queries could benefit from it)

But check all of the above theories.

A couple of common suggestions

• first write your terms in the most selective way.
• during the first testing, indices start with indices with a single column, and then expand to composite indices (for example, for sorting at startup, I would add the index only at startup)
• too many indexes are not so good in mysql, because the query planner cannot quickly execute all possible combinations and cannot properly estimate the costs of all operations (therefore, it reduces corners, and the best combination of indexes and plan can be omitted)
• so check indexes with USE INDEX (index1) FOR ORDER BY in your choice to evaluate the advantage for a particular index on the planer, see more details here (esp FORCE In addition, the goal is to leave only useful indexes and see if the glider can use them , and if not, then only as a last resort, force indexes in your queries for which performance is crucial. Keep in mind that this is bad practice in terms of administration and design).

Answer 2

Try to avoid using ranges (e.g.>,> =, <, <=) as the leftmost part of the WHERE clause. MySQL cannot use the index for any fields in the WHERE clause to the right of the range.

At first glance, I would say at least create an index on first_user_id, stop, second_user_id. Then indicate the request accordingly:

select * from tbl_name, where first_user_id = N and stop> = M and second_user_id = N

UPDATE: D'oh, so I completely contradicted myself in the above query - since including second_user_id in the index is useless when specifying it in WHERE after stopping "range", so try again.

ALTER TABLE tbl_name ADD INDEX index_1 (first_user_id, stop) ALTER TABLE tbl_name ADD INDEX index_2 (first_user_id, second_user_id, stop)

Answer 3

It is strange that your query returns 238 rows (?)

Since you stated that the query is faster without ORDER BY , can I suggest you sort after the query?
This may be the fastest way to fix the problem.

Also, don't forget to delete unused indexes later :)

---

change

This is a wild assumption (because I'm not sure mysql will not factor the query to its current form), but you can try the following:

 SELECT * FROM ( SELECT * FROM tbl_name WHERE stop >= M AND first_user_id=N ) AS derived ORDER BY start ASC