I have the following query: SELECT DISTINCT FIRST_VALUE(business_id) OVER (PARTITION BY b.sub_category_id ORDER BY AVG(stars) desc, COUNT(*) DESC) business_id, sub_category_id FROM purchase_experience pe JOIN businesses b ON b.id = pe.business_id AND b.status = 'active' AND b.sub_category_id IN (1010 ,1007 ,1034…
I quickly wrote this code to test when all sides and diagonals of a cuboid a,b,c,d,e and f are all integers. It seems to work just fine but it takes way too much time for higher and higher values to…
I have some SVG in a list, there are about 100 nodes. They looks like: <li> <div class="icon"><svg><path .... </svg></li></div> </li> ... another 1000 <li> Should I turn them into CSS? <li> <div class="icon"></li></div> </li> .icon { width:20px; height:20px; background-image:…
I have a huge dataset on two tables (users, pos_transactions), and I need to execute a join on them. I wrote the query both with join and subquery approches. Both looks Non-optimal: explain SELECT pt.card_number FROM pos_transactions pt where user_id…
I have a simple subquery in MySQL which produces a set of codes, something like 'LPR','LFR','LFL' (more codes can be returned). Using this in my main query slows it right down, like it can take a minute or 2 to…
I'm encountering a PHP execution time error while inserting approximately 250k records into a MySQL database using Laravel Eloquent. I'm aiming to achieve this without increasing the overall execution time, considering the following constraints: Frontend: Angular Multi-tenancy: Implemented using https://tenancyforlaravel.com/…
How to make the two nested loops into one loop for the function? Please add the explanation to the code in your answer. You can add more explanation on strategy guideline to reduce nested loops for other common cases for…
I need to retrieve an object at a specific index from a massive JSON array. The array contains 2,000,000 objects and the file size is around 5GB. I've experimented with various approaches using jq in combination with Python, but performance…
I have 3 billion IPs stored in the following ip_location table. CREATE TABLE ip_location ( id BIGINT AUTO_INCREMENT PRIMARY KEY, ip VARCHAR(15) NOT NULL, country VARCHAR(2), region_1 VARCHAR(255), region_2 VARCHAR(255), latitude DECIMAL(10, 6) NOT NULL, longitude DECIMAL(10, 6) NOT NULL,…
Given an integer n that represents the number of vertices on a graph, and each vertex connect one another with single edge, except for the start vertex and end vertex, we can calculate the minimum distance pair of vertices with…