Posted on 5 Comments

FORCE INDEX

SELECT ... FROM
tblProduct as prd FORCE INDEX(vProductName,iCategoryId,eProductType,vProductImage,fPrice,eProductStatus,dProductDateAdded)
LEFT OUTER JOIN (SELECT bidjoin.* FROM tblProduct as prdjoin, tblBid as bidjoin WHERE bidjoin.iProductId = prdjoin.iProductId) bid ON prd.iProductId = bid.iProductId
, tblCategory as cat FORCE INDEX(vCategoryName,eCategoryStatus,dCategoryDateAdded) 
, tblAuctionProduct as aucprd FORCE INDEX(iRequiredBids,iProductId,eAuctionType,iFreeBidLevel,fAdminFee,dDateClosed)
, tblPurchaseProduct as purprd FORCE INDEX(dProductCloseDate,iProductId,iTotalInventory,dLastUpdated)

I know, unreadable. But the main issue today is the FORCE INDEX. This is just one query (real, with permission – I just adjusted some identifiers) from an app that is litterally full of queries using FORCE INDEX. So what does FORCE INDEX do? It forces the MySQL optimiser to use the specified index (or choose from one of the specified, if multiple), even if it reckons it’s not the best choice. Likewise there’s an IGNORE INDEX modifier that denies the optimiser the choice for using a specified index in a particular query.

I generally hold that these modifiers should only be used for testing things and tracking down optimiser issues (not that common these days, but 3.23 had plenty that 4.0 fixed). This because when you hardcode them into an app, perhaps fixing a real problem you see *now*, your data will still change over time and so the optimiser needs the freedom to change its choices too. It might be ok for a week, a month, or even a year, but at some point it’s going to cost.

The above query takes about 5 times as long compared to the same query without the FORCE modifiers. Considering the app is chockers with it, I’m guessing that it might have been coded by someone who didn’t know that MySQL actually has an optimiser at all. Who can say… anyway it serves here as an extreme example.

The take-away message is: use these modifiers with care, and be very hesitant ever adding it to production code. There’s generally always a better way to resolve whatever problem you might be observing.

Posted on 5 Comments

5 thoughts on “FORCE INDEX

  1. Should FORCE INDEX just be tossed?

  2. Nono. Like I said, I reckon it’s very useful for tracking down problems both in the field and in development.

  3. What about USE index?

    My fear of these three is that it looks like people shoot themselves in the foot most of the time that they use them.

  4. Same, really.
    And yes you are right, the foot shooting with these is prolific. But same with many other including general features of MySQL. How many people actually understand joins? 😉
    Heck it’s part of why my business does well, there’s plenty of need for good training.
    Removing features, let’s not waste time on that.
    Rather fix real problems, for instance the existence of TEXT type fields in the INFORMATION_SCHEMA tables. This causes tmp disk table use. Very nasty/slow. Very easy to fix. There’s a bug open for it, I even listed exactly which tables/cols have the issue and so on.

  5. Thanks for pointing out the I_S table issue. I had not seen that.

    The quick solution is to just change the output to VARCHAR, the longer term solution is that you use a memory engine that keeps blobs in memory.

    For the I_S, did you generate a patch? Or set the needed size for VARCHAR?

Comments are closed.