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 …

InnoDB, when building a secondary index (as with your name index), appends the columns of the PRIMARY KEY. This happens to make it a "covering index" (as indicated by "Using index"). …

The Optimizer looks at the situation as says (roughly): "If I need to scan more than 20% of the table, I won't bother using the index." … This is a reasonable decision because using an index requires bouncing between the index's BTree and the data's BTree. …

It is always better (as far as I know) to build a composite index: INDEX(id, flag) -- in either order If this is a 1-row table, then the effort of looking in two indexes to ultimately get a single … (But the answer will be the same -- use a composite index instead of hoping for "index merge intersect".) …

WHERE a op 3 -- usually can use INDEX(a) WHERE a = 3 ORDER BY b -- use INDEX(a,b), but WHERE a > 3 ORDER BY b -- won't get past the "range"; that is, INDEX(a,b) will work, but the b part will not be used … IN ( constants ) is somewhere between = and > for index-ability: WHERE a IN (constants) AND b > 8 might use both parts of INDEX(a,b), but WHERE a IN (constants) ORDER BY b cannot use both parts of INDEX …

(id), INDEX(category, date), INDEX(post_url), INDEX(user_url), INDEX(user_id, date), INDEX(msg_receiver_id, msg_sender_id, date) msg_table PRIMARY KEY(id), ?? … INDEX(msg_receiver_id, msg_status), INDEX(msg_url, date) …

The way to approach indexing is to take each SELECT and decide what the optimal index is for that select. Be sure to consider "composite" indexes. … Be sure to avoid redundancies, such as: INDEX(a) is not needed if you have INDEX(a,b) INDEX(id) is not needed if you have PRIMARY KEY(id) If you get to more than, say, 10 indexes, then you should make …

Usually the Optimizer "does the right thing" in deciding between using the index (and bouncing between the index's BTree and the data BTree) versus simply scanning the entire table (and throwing out unwanted …

Long answer: A "covering" index is usually better than a non-covering one. A covering index for the query you mention would contain all the columns in the table. … If so, simply PRIMARY KEY(phone_number) is better than any "covering" index. Always provide SHOW CREATE TABLE when asking performance questions. …

So add that index. As for INDEX(joining_date, ...) When using a 'range', that index will not get past joining_date to whatever other column(s) follow it. … So you may as say just INDEX(joining_date) As for the GROUP BY... Neither of your queries has any hope of using an index for the GROUP BY. …

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. …