The Table:
CREATE TABLE `T1` (
`UserId` int(10) unsigned NOT NULL,
`FriendUserId` int(10) unsigned NOT NULL,
`IsDisplayed` tinyint(1) unsigned NOT NULL,
`Created` datetime NOT NULL,
KEY `FriendUserId` (`FriendUserId`,`IsDisplayed`,`UserId`,`Created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
The Query:
SELECT `UserId`, `FriendUserId`, UNIX_TIMESTAMP(`Created`) AS `Created`
FROM `T1` WHERE `FriendUserId` = 22
AND `IsDisplayed` = 0
GROUP BY `UserId`
ORDER BY `Created`
EXPLAIN result:
id: 1
select_type: SIMPLE
table: T1
type: ref
possible_keys: FriendUserId
key: FriendUserId
key_len: 5
ref: const,const
rows: 1
Extra: Using where; Using index; Using temporary; Using filesort
Question:
How can I optimize it so that no temporary table is used?
A valuable alternatives for the SQL temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory without the need to touch the TempDB database, providing the best data access performance.
Looking at SQL Profiler results from these queries (each were run 10 times and averages are below) we can see that the CTE just slightly outperforms both the temporary table and table variable queries when it comes to overall duration.
The reason, temp tables are faster in loading data as they are created in the tempdb and the logging works very differently for temp tables. All the data modifications are not logged in the log file the way they are logged in the regular table, hence the operation with the Temp tables are faster.
MySQL documentation says:
Temporary tables can be created under conditions such as these:
If there is an
ORDER BY
clause and a differentGROUP BY
clause, or if theORDER BY
orGROUP BY
contains columns from tables other than the first table in the join queue, a temporary table is created.
So, you can only avoid of using temporary table only by removing order by Created
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With