Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
1 5
Newest
 Custodian
Impact
~4k people reached

  • 0 posts edited
  • 0 helpful flags
  • 1 vote cast
Jul
30
comment select does not use index when using different column
Maybe you want to place an answer for me to accept it. One other thing :-) what's the logic behind the index with two columns?
Jul
30
comment select does not use index when using different column
I don't know the internals, but 1657760 is 47.5% more of rows than those that are returned.
Jul
30
comment select does not use index when using different column
The index you suggested (r_a, ep) returns an EXPLAIN similar to the one I posted above for SELECT r_a ..., so it uses an index now. But, so many rows 1657760 it has to scan?
Jul
30
comment select does not use index when using different column
1123511 rows show up when r_a > 1000.
Jul
30
comment select does not use index when using different column
I simplified and narrowed the problem to this, that's why I showed no join.
Jul
30
awarded  Custodian
Jul
30
comment select does not use index when using different column
With no index, it scans more than three million rows. Not bad in this simple query, but it makes a big difference when this problem happens on a multiple join query, which is my case. And it's strange to me that the optimizer changed it's algorithm of search when the where clause is the same. AFAICT, the fields retrieved by select do not impact significantly the speed of execution of the query.
Jul
30
reviewed Approve select does not use index when using different column
Jul
30
asked select does not use index when using different column
Mar
9
awarded  Notable Question
May
30
awarded  Popular Question
Mar
21
awarded  Supporter
Mar
21
comment Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'
Thanks very much, I guess I wouldn't get at this solution by myself.
Mar
21
accepted Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'
Mar
21
comment Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'
I just want to insert one row into doku_usergroup for each updated record of users.
Mar
21
asked Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'
Nov
7
awarded  Student
Nov
7
awarded  Scholar
Nov
7
accepted Select consecutive number of rows starting from some number
Nov
7
comment Select consecutive number of rows starting from some number
@dezso: rows don't ever get updated or deleted. It's enough for this case to get them in physical order.