What is the point of this?

Anyway, there is no sane way of doing this query, so it's WONTFIX

Bryan.TongMinh wrote:

There is an index on cat_pages which can be used for this, but we need to take care that we use the proper index for sorting.

Note, the index is only on cat_pages

So this is only doable for the number of cat_pages, NOT cat_subcats or cat_files, so would need extra indexes adding for the latter if it's desired...

Ok, poking at this.

If we just filter by the cat_pages, it's fine. If we then order by anything (cat_title or cat_pages), we create a filesort.

This basically means we can't do both pages and title filtering together... (Would need to be mutually exclusive)

Though, I just noticed, if we do WHERE cl_title >= something, and order by, it filesorts....

(In reply to comment #4)

Ok, poking at this.

If we just filter by the cat_pages, it's fine. If we then order by anything
(cat_title or cat_pages), we create a filesort.

This basically means we can't do both pages and title filtering together...
(Would need to be mutually exclusive)

Though, I just noticed, if we do WHERE cl_title >= something, and order by, it
filesorts....

Seemingly, just on the version of mysql my vm is running. FFS

r83833

Bryan.TongMinh wrote:

(In reply to comment #3)

Note, the index is only on cat_pages

So this is only doable for the number of cat_pages, NOT cat_subcats or
cat_files, so would need extra indexes adding for the latter if it's desired...

cat_pages includes cat_files and cat_subcats.

(In reply to comment #7)

(In reply to comment #3)

Note, the index is only on cat_pages

So this is only doable for the number of cat_pages, NOT cat_subcats or
cat_files, so would need extra indexes adding for the latter if it's desired...

cat_pages includes cat_files and cat_subcats.

Duh. Helps if I read the text in tables.sql properly

(In reply to comment #5)

(In reply to comment #4)

Ok, poking at this.

If we just filter by the cat_pages, it's fine. If we then order by anything
(cat_title or cat_pages), we create a filesort.

This basically means we can't do both pages and title filtering together...
(Would need to be mutually exclusive)

Though, I just noticed, if we do WHERE cl_title >= something, and order by, it
filesorts....

Seemingly, just on the version of mysql my vm is running. FFS

Depends on which index is chosen. When you use cl_title >= 'foo' with ORDER BY cat_pages , MySQL can either use the cat_pages index for ordering and resolve the WHERE by scanning rows and dropping them if they don't satisfy the condition, or it can use the cl_title index for quickly obtaining all rows that satisfy the WHERE clause then filesort that.

For this reason, the code committed for fixing this bug is likely to produce inefficient queries. I'll comment on that in more detail on CodeReview.

Interestingly, I was looking at this.

It filesorted locally, but doing DESCRIBE query; on WMF it didn't....

(In reply to comment #10)

Interestingly, I was looking at this.

It filesorted locally, but doing DESCRIBE query; on WMF it didn't....

Part of the choice seems to be informed by the size of the table and the cardinality of the index (MySQL's perception of the degree of 'uniqueness' of non-unique indexes, AIUI) and probably a few other things as well, which are very different on WMF compared to your box :)