So, I remove Joins from my queries when I am ready to move to Cassandra, which does not support this, but supports a lot of select statements instead. I did a benchmark test on 50 rows of data in my mysql table (I am currently using), resulting in 101 queries (all selections), and it took ~ 0.035 seconds to complete all these queries. Then I changed this to some array manipulation (currently in PHP) and reduced it to 3 queries with an O (n) bundle for loops.
I assume that my system is in PHP, Python, MySQL or Cassandra (NoSQL), that it is faster to process data with several O (n) loops, and not much more queries, I reduced the time from 0.035 to 0.004 using this new method as shown below.
Are there any alternative methods to reduce this? Or am I on the right track? Any cases where it runs all queries faster (except when it becomes O (n ^ 2))? Thanks:
// Now go through and get all of the user information (This is slower in mysql, but maybe faster in cassandra) /*foreach ($results as $key => $row) { // Create query $query = DB::select('id', 'username', 'profile_picture')->from('users')->where('id', '=', $row['uid']); // Execute it $results2 = $query->execute(null, false); // Join it $data[$key] = array_merge($row, $results2[0]); }*/ // Get all the user information (faster in mysql since less queries) $uids = array(); $ids = array(); foreach ($results as $key => $row) { if (!in_array($row['uid'], $uids)) $uids[] = $row['uid']; if (!in_array($type, array('userProfile'))) $ids[] = $row['comment_id']; } // Create query $query = DB::select('id', 'username', 'profile_picture')->from('users')->where('id', '=', $uids); // Execute it $results2 = $query->execute(null, false); $user_data = array(); foreach ($results2 as $key => $row) { $user_data[$row['id']] = array('uid' => $row['id'], 'username' => $row['username'], 'profile_picture' => $row['profile_picture']); } foreach ($results as $key => $row) { $data[$key] = array_merge($row, $user_data[$row['uid']]); } // End faster user info section
source share