MySql group by is slow
I have a scenario whereby I am trying to fetch a user's most recent messages from over 2 million rows and group them by their parent (or thread) id. However, this grouping is causing the query time to be around…
how to set GEOMETRY in MySQL using JSON
I want to update my dataset and use the GEOMETRY datatype instead of using latitude and longitude. The problem is I have set a geo column in my table with the datatype yet I can't put my data in it.…
Mysql – group by and count from another table
I have the following tables: s2s_actions id app_id brand_id country_id ------ ------ -------- ---------- 22 15 361 5 29 15 361 5 35 15 7 64 40 15 7 64 41 15 37 5 44 15 361 5 clicks id…
optimize TIMESTAMPDIFF in mysql query
I need help optimizing a query for large data table when I test manually it shows fast , but in my slow query log it is logged as taken 10s+ for some reason . SELECT q.id, q.player_id, q.village_id, q.to_player_id, q.to_village_id,…
Mysql – inner queries with outer group by column
I have this table called s2s_actions: id brand_id event_type ------ -------- ---------- 22 361 0 29 361 1 35 7 1 40 7 0 41 37 1 42 37 0 43 37 1 and I want to get the count…
Error when switching beetween mysql to postgresql connection dynamically in Laravel
I get this error when trying dynamically switch from mysql to postgresql: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "FROM" LINE 1: SHOW KEYS FROM wb_sales WHERE Key_name LIKE '%unique%'; Error occuring when I'm trying to upsert…
How to change DB user/password with Laravel sail?
I'm stucked on a stupid problem.. Maybe I shouldn't even bother about it, but I want to understand. I'm using Laravel with Sail/Docker, on macos. I'd like to change my DB user and password, just to try and test. If…
Mysql – Is it possible to combine these two SQL queries into one (WHERE, NULL, CASE)?
Is it possible to combine these two queries into one ? Meaning: Take the first row if media_group is NULL, otherwise take all rows with the same media_group $get_first_record = R::getRow( 'SELECT * FROM `prn` order by id asc LIMIT…
How to update one column's multiple rows with multiple values in MySQL?
I have a data table like the name as data_table and sample data are like below id name type temp_id 1 n1 t1 1 n1 t1 1 n1 t1 1 n1 t1 2 n1 t1 2 n1 t1 2 n1…
Why does UNION return less rows than one of its subrequests with MySQL?
UNION can be used to merge the rows of two sub-requests: SELECT column_name FROM table1 UNION SELECT column_name FROM table2; By default, MySQL's UNION is actually UNION DISTINCT, which means that the rows table1 and table2 have in common are…