b2evolution b2evolution

  • Sign in
  • Sign up
  • About
  • Downloads
  • Hosting
  • Docs
  • Support
  • Sign in
  • Sign up
  • Manuals Home
  • Latest Updates
 
  1. b2evolution CMS User Manual
  2. Developer Reference
  3. Development
  4. Optimizing a SELECT Query

Optimizing a SELECT Query

When you have a slow running SELECT query, it may have 2 reasons

  1. You don’t have the proper index on your table to let the WHERE conditions filter efficiently.
  2. You have too many joins that join a lot of data before being able to discard unnecessary data

Index issue

If your table has an index on fields "A,B" (one index here, not 2) and you have a WHERE condition on B = something, then that index cannot work.

Try creating an additional index on B.

WARNING: every new index you create will slow down INSERTs and possibly UPDATEs.

Joining to much data

If you have a query like:


SELECT *
FROM T1 INNER JOIN T2 ON ...
WHERE ...

Because of SELECT *, all the fields of the tables are joined in memory, sometimes even before all the unnecessary rows are discarded by WHERE clauses.

This can use a lot of memory and can sometimes even page to disk: super slow!

If you don’t need all columns, start by select only the columns you need. It may result in much faster joins.

Another situation is


SELECT *
FROM T1 INNER JOIN T2 ON ...
           INNER JOIN T3 ON ...
           INNER JOIN T4 ON ...
WHERE ...

This may result in joining 4 tables on many lines before the WHERE clauses apply.

In this case, if we can"t have efficient indexes to reduce the number of lines that will be joined in memory, it may make sense to first SELECT IDs from a smaller query, maybe just from T2 INNER JOIN T3 and then select additional date from T1 and T4 in a second query. (This may also be handled with a subquery);

Created by fplanque • Last edit by fplanque on 2020-06-09 00:25 • •

No feedback yet

On this page

  • Index issue
  • Joining to much data

Search the Manual

Content Hierarchy

  • b2evolution CMS User Manual
  • User's Guide
  • Installation / Upgrade
  • Front-office Reference
  • Back-office Reference
  • Developer Reference
    • Website Skins/Themes
    • Email Skins/Themes
    • b2evolution Files
    • Website Integration
    • Plugins
    • Debugging
    • Technical Reference
    • Hacks
    • Credits
    • Development
      • CSS Guidelines
      • Crumbs (nonces)
      • Setting up a multidomain test environment
      • Roadmap & Unified Process
      • Coding Standard Guidelines
      • Ajax Debug Log
      • Debugging
      • Developer Setup
      • jQuery
      • Optimizing a SELECT Query
      • Dev Menu
    • How to... (Customize)
    • Scheduled Tasks Reference
  • Operations Reference
  • Advanced Topics
  • Glossary
  • Archives
Build your own website!

This online manual is powered by b2evolution CMS – A complete engine for your website.

About b2evolution

  • What is it?
  • Features
  • Getting Started
  • Screenshots
  • Online demo
  • Testimonials
  • Design philosophy
  • Free & open source
  • Terms of service

Downloads

  • Latest releases
  • Skins
  • Plugins
  • Language packs

About us

  • About us
  • Contact

Webhosting Guide

  • Web hosting blog
  • Best web hosting
  • Cheap web hosting
  • Green web hosting
  • Hosting with SSH
  • VPS hosting
  • Dedicated servers
  • Reseller hosting
  • Int'l: UK / France

Docs & Support

  • Online manual
  • Forums
  • Hire a pro !

Other

  • Adsense
  • Press room
  • Privacy policy

Stay in touch

  • GitHub
  • Twitter
  • Facebook
  • LinkedIn
  • News blog
  • RSS feed
  • Atom feed

Founded & Maintained by François Planque