Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Indexes do not always speed up execution. The effect of an index depends primarily on the "selectivity" of the query: how many rows are processed by the overall query.</p> <p>In general, reading a database (a "full table scan") is an efficient operation. The database engine knows what pages it needs to read and can read ahead to get them. Such I/O often occurs in the background, while processing the pages is in the foreground. When the next page is needed though, there is a good chance it is already in the page cache.</p> <p>The performance issue with full table scans is that tables are big. So even efficient reads take time. When you are looking for one row in a million ("needle-in-the-haystack" queries), the reads are a waste of time. This is where indexes fix things.</p> <p>However, say you have 100 records per page and you are reading more than 1% of the records. On average, every page will need to be read -- whether you are using an index or a full-table scan. The problem is that index reads are less efficient than scan reads. A read-ahead mechanism doesn't help them, because the reads are random.</p> <p>This problem can be further exacerbated through something called thrashing. If the table does not fit into memory, then each random read is likely to be a "cache miss", incurring the overhead of a read from disk. The full table scan would just read the data, and with a decent look-ahead system, there would be no cache misses.</p> <p>In your example, you could increase the selectivity of the index by including both <code>banner</code> and <code>event</code> in the index (these are compared using equality) and one of the other fields.</p>
    singulars
    1. This table or related slice is empty.
    1. POAdding index increases query execution
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USGordon Linoff
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COTried with adding index `banner` and `event` `ALTER TABLE mod_banner ADD INDEX banner_event (banner, event)`, but still over 100ms :P http://i.imgur.com/IxWoPZU.png
      singulars
      1. PO
      1. USmichalzuber
    2. CORemoving `ORDER BY` made it instant. http://i.imgur.com/ZUQxksi.png But I need the one with the lowest `last_view`
      singulars
      1. PO
      1. USmichalzuber
    3. COOne question: if reading from the index hurts performance, why does not the engine decide to not use them?
      singulars
      1. PO
      1. USJohn Dvorak
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload