Search Results

INDEX(library_id, firstdateofinterval) will help the query run faster. Note that the column tested by = should be first and the range should be last. Why not use the desired index? … That is, all 4 of your indexes are equivalent to simply INDEX(firstdateofinterval) when you test for a range of dates. When you used the "index hint", did it actually run any faster? Unrelated... …

Variable #1: If MySQL chooses to build the index(es) on the fly, or wait until all the data is in, then do a sort, etc, to build the index. … Manual splitting leads to unbalanced situations -- a table scan is stuck on the data disk; an index-only operation is stuck on the index disk; a lone query first hits the index disk, then the data disk …

If it is no more than those 3 columns, then a "covering" index will be a slight benefit: INDEX(ot_id, numval1, numval2) INDEX(ot_id, numval2, numval1) Again, this leaves it to the Optimizer to dynamically … Again it leads to 2 choices: INDEX(numval1) INDEX(numval2) Or, if covering: INDEX(numval1, numval2, ot_id) INDEX(numval2, numval1, ot_id) (The last pair of columns can be in either order.) …

Then you need INDEX(id, date) INDEX(id, type, -- in either order date) No single index handles both queries equally well. … (Based on your Comments) If (id, date) is unique, then make that the PK. and toss the other index. If (id, type, date) is unique, make it the PK and toss the other index. …

A UNIQUE key is an INDEX, plus a uniqueness constraint. (Note: may be clustered) An INDEX is a BTree that is used for efficient access. … A FOREIGN key creates an INDEX if there is not already a suitable index. (Note: Non-clustered) The 3 "Notes" are implicitly-created indexes, but not necessarily non-clustered. …

Run CHECK TABLE logs; -- the index may be corrupted. (This is a MyISAM-only thing to do.) Use InnoDB, not MyISAM. …

Scan through the 300 index entries. If the index is very random (eg UUID), then the entries will point to lots of random places in the data. … You say "It has index on all the required columns." -- A "composite" index? Is the index_column the first column in such? If not, then much of what I said does not apply. …

When the Optimizer sees that only a "small percentage" of the index will be needed, it uses the index. … "Using index" -- Read about "covering" indexes. "Using index condition" (new in 5.6) -- This is something different. …

Index creation for a large table uses lots of disk space; memory usage is relatively stable. Are you loading that billion-row table repeatedly? Maybe we should look at ways to avoid reloading. …

If that is "small" (say, less than 20%, but that is not a hard number), use the INDEX; else do a table scan. Step 1 depends on the "statistics" that are kept with the MyISAM table. … The reason for the to-INDEX-or-not-to-INDEX question goes something like this... When using the INDEX, the execution has to bounce between index 'rows' and data rows. …

Query 1: INDEX(person_type, person_id) -- in that order INDEX(person_type, person_id, full_name) -- to be "covering" Query 2 ("covering" is not practical because of *): INDEX(person_type, full_name …

Since AGE is not in any index, there is no INDEX will be modified. …

In this case, it is always better to do an index scan instead of a table scan. All of the needed columns are in the index; that is, it is a "covering" index. … An index and the data have essentially identical BTree structures. Scanning the index is faster because less data is handled. …

UUIDs are terrible when the table becomes too large to be cached. (If your table will never get very big, you can ignore the rest of my rant.) The problem comes with the randomness of UUIDs. You fe …

I have multiple ways to efficiently do that SELECT on that kind of a table. Yes, it involves changes to the table. And a change to the SELECT. One involves adding an AUTO_INCREMENT PRIMARY KEY and …