New answers tagged

0

You don't need the extra layer SELECT * FROM (...), do this ( SELECT ... ) -- without any ORDER BY UNION ( SELECT ... ) -- without any ORDER BY UNION ... UNION ( SELECT ... ) -- without any ORDER BY ORDER BY ... Because of the parentheses, it is clear that the ORDER BY belongs to the result of the UNION, not the final SELECT. It is usually 'wrong' to ...


2

This composite index may help: INDEX(account_id, lastname) When adding it, you may as well remove INDEX(account_id), since it will then be redundant. This may be even better, but I am not sure: INDEX(deleted, account_id, lastname) However it does not supersede INDEX(account_id).


0

The outer SELECT * FROM does not have to respect the ORDER BY in the inner query. Other RDBMSes will actually error in this situation, as an ORDER BY in subqueries is deemed invalid/not necessary. To fix, alter your query so it's of the form: SELECT * FROM (SELECT sale.name as "Name", -- rest of subquery ORDER BY account.close_date desc) ...


1

Looks like you are sorting by close_date column in all the tables in DESC order then UNION. Instead of this approach SELECT UNION all the tables and, in the final result you can ORDER BY the Close Date:data:200 as DESC. The working query will be: SELECT T.* FROM ( SELECT sale.NAME AS "Name" ,sale.responsible AS "Responsible:data:200" ,...


0

Remove LEFT if you don't need it. The ORDER BY has ISNULL(start), yet the WHERE clause will fail if start isNULL`. Seems like something could be simplified. Will the table(s) become so big that they cannot be cached in RAM? If so, we can talk about the choice of PRIMARY KEY. (I'll need to see SHOW CREATE TABLE for this discussion.) The query, as it stands,...


4

There is no benefit to including the ORDER BY columns in the SELECT list. On the contrary, having unrequired columns in the SELECT list incurs a fractional overhead in run time and a larger one in maintenance.


0

You may use DESC in an index declaration, but it is ignored. (The PRIMARY KEY is an index.) You must say DESC in the ORDER BY clause if you want the resultset sorted that way.


1

If the is what the book says then it is misleading at best At least in MSSQL 2012 The insert (col1, col2) is just index based The first item in the select goes into col1 The data type must be the same The select stands on it own and is not aware of column names in the insert Example1 would only be valid for just need some data and don't care Just need ...


3

And is it correct to assume that the n rows that are chosen from table_name2 are completely random records? When an order isn't specified, it will usually use the sort fields and directions from the design of the primary key of table_name2, but is not guaranteed to. As Aaron Bertrand said It doesn't really matter what the clustered index is. SQL ...


0

INDEX(post_id, meta_data) And switch to InnoDB. Get rid of the prefixing index, in one of these ways: Upgrade to 5.7 VARCHAR(191) instead of 255 utf8 instead if utf8mb4 (thereby not allowing Emoji or all of Chinese) WP should get rid of meta_id and change to PRIMARY KEY(post_id, meta_data) and InnoDB. That would make things even faster. (But still, ...



Top 50 recent answers are included