[Answered ]-Speeding up mysql queries / mysql views in django

2👍

✅

SELECT *, date(dt_published) as dpub FROM `news_entry` order by dpub DESC LIMIT 500

This query orders on dpub, while this one:

SELECT * , DATE( dt_published ) AS dpub FROM  `news_entry` ORDER BY id DESC LIMIT 500

orders on id.

Since id is most probably a PRIMARY KEY for your table, and each PRIMARY KEY has an implicit index backing it, ORDER BY does not need to sort.

dpub is a computed field and MySQL does not support indexes on computed fields. However, ORDER BY dt_published is an ORDER BY dpub as well.

You need to change your query to this:

SELECT *, date(dt_published) as dpub FROM `news_entry` order by date_published DESC LIMIT 500

and create an index on news_entry (dt_published).

Update:

Since DATE is a monotonic function, you may employ this trick:

SELECT  *, DATE(dt_published) AS dpub
FROM    news_entry
WHERE   dt_published >=
        (
        SELECT  md
        FROM    (
                SELECT  DATE(dt_published) AS md
                FROM    news_entry
                ORDER BY
                        dt_published DESC
                LIMIT 499, 1
                ) q
        UNION ALL
        SELECT  DATE(MIN(dt_published))
        FROM    news_entry
        LIMIT 1
        )
ORDER BY
        dpub DESC, views DESC, dt_written DESC, headline
LIMIT 500

This query does the following:

Selects the 500th record in dt_published DESC order, or the first record posted should there be less than 500 records in the table.
Fetches all records posted later than the date of the last record selected. Since DATE(x) is always less or equal to x, there can be more than 500 records, but still
much less than the whole table.
Orders and limits these records as appropriate.