State can't be indexed (index will not help.) Since it's a range lookup.
Type is already indexed as first element of the extension index. I fear that the range lookup of enabled is preventing the query optimizer to do use the index.

On November 14, 2014 7:18:52 AM EST, javier gomez notifications@github.com wrote:

Hi, I just saw this in my debug:

I checked and the Indexes in this table are:

I'm not skilled in query performance but I was wondering if we can add
some Indexes to "enabled", "type", "state"...

What do you think?

cc: @beat @gunjanpatel, @nadeeshaan, @alikon, @aschkenasy,
@mrunalpittalia, and rest of members of the
http://docs.joomla.org/SQL_Optimisation_Working_Group

---

Reply to this email directly or view it on GitHub:
#5101

Sent from my mobile device. Please excuse my brevity.

It should work if the query is changed to AND state IN (0,1), right? Then state could be indexed and it could be used.

However I wonder what the purpose of that column is. In my test database this value is always 0. We don't use a state there to my knowledge. We use the enabled column instead.

State in is perfect.
Likewise any finite option should be
IN(1,2,3,...)

On November 14, 2014 10:00:57 AM EST, Thomas Hunziker notifications@github.com wrote:

It should work if the query is changed to AND state IN (0,1), right?
Then state could be indexed and it could be used.

However I wonder what the purpose of that column is. In my test
database this value is always 0. We don't use a state there to my
knowledge. We use the enabled column instead.

---

Reply to this email directly or view it on GitHub:
#5101 (comment)

Sent from my mobile device. Please excuse my brevity.

See #5120