Tag Info

New answers tagged

1

Unfortunately, MySQL does not allow for filtered indexes (otherwise you can just filter on open tickets only). The next best thing is to do something similar to the two table approach you describe: Partitioned tables.


2

Yes, a plain INSERT is fastest when no indexes have to be maintained. So it may pay to drop indexes before huge bulk inserts and add them later. Depends on the whole situation, of course. No, you cannot drop a PK or a UNIQUE constraint or even a plain index in the middle of an INSERT, which also writes to the index. No, I don't think your query should take ...


3

Use a VALUES expression to generate an ad-hoc table and join to the other tables to get the respective IDs. INSERT INTO groupmember (person_id, group_id) SELECT p.person_id, g.group_id FROM ( VALUES ('alice'::varchar, 'girls'::varchar) , ('bob','boys', , ('alice','coolkids') , ('bob','coolkids') ) x (username, group_name) JOIN person p ...


5

Why this happens has already been answered by @PaulWhite in the SO question: How does this CASE expression reach the ELSE clause? To solve it, you should calculate the ABS(CHECKSUM(NEWID()))%10 +1 outside/before the INSERT statement so it is calculated once. Something like: DECLARE @i INT = 1 ; DECLARE @rand INT ; WHILE @i <= 10 BEGIN SET @rand ...


0

Some bulk data loading tips from mysql document is possible useful. https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html You can increase insert speed by some ways: - turn off autocommit - turn off unique check - turn off foreign check Hope this help !


0

PROPOSED QUERY insert into rfdate (date) select selected_date from ( select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 t1 ...



Top 50 recent answers are included