Not the answer you need?
Register and ask your own question!

which is better: select distinct VS array_unique

qspyqspy EntrantInactive User Role Beginner
got query like this:

SELECT DISTINCT p.topic_id FROM phpbb_posts p WHERE p.post_id IN (1275, 3128, 3129, 10702, 16451, 37026, 44527, 65259, 68238, 68805, 122704, 254944, 323501, 384915, 387562, 593207, 615833, 639439, 660219, [etc], 9732302, 9734352, 9913929, 9959593, 10033198, 10033449, 7212999);+----+
+
+
+
+
+
+
+
+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+
+
+
+
+
+
+
+
+
+| 1 | SIMPLE | p | range | PRIMARY | PRIMARY | 3 | NULL | 292 | Using where; Using temporary |+----+
+
+
+
+
+
+
+
+
+and then: while ($row = $db_slave->sql_fetchrow($result)) { $search_ids[] = $row; }</pre>


How about this:

SELECT p.topic_idFROM phpbb_posts p WHERE p.post_id IN (1275, 3128, 3129, 10702, 16451, 37026, 44527, 65259, 68238, 68805, 122704, 254944, 323501, 384915, 387562, 593207, 615833, 639439, 660219, [etc], 9732302, 9734352, 9913929, 9959593, 10033198, 10033449, 7212999);+----+
+
+
+
+
+
+
+
+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+
+
+
+
+
+
+
+
+
+| 1 | SIMPLE | p | range | PRIMARY | PRIMARY | 3 | NULL | 292 | Using where |+----+
+
+
+
+
+
+
+
+
+and then: while ($row = $db_slave->sql_fetchrow($result)) { $search_ids[] = $row; }$old_array=$search_ids[]; $new_array=array_unique($old_array); </pre>


So we eliminating Using temporary (dinstinct first query), to make distinct via php. Do you predict any real worth performance gain?

.

Comments

  • gmousegmouse Mod Squad Inactive User Role Beginner
    It is a trade-off. In this situation I would not care about the temp table since it can be kept in memory (no text fields).
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.