New answers tagged

0

Performance is probably being hurt by all the COMMITs and single-row actions. The following should speed it up by order(s) of magnitude. Do a batch of 100 rows at a time A single multi-row INSERT for the the extra table. Use BEGIN...COMMIT around each batch. Since you need to pull the data out, encrypt it, and then put it back in, here are some more ...


0

INSERT and INSERT IGNORE add a new row unless the row specifies a duplicate for some PRIMARY or UNIQUE key on the table. The only difference is whether the "duplicate key" is an error or ignored. Do you want one row per user? Or can the user provide many passwords? In the former case, PRIMARY KEY(username); in the latter, PRIMARY KEY(username, password). ...


0

You're paying the extra price of network IO and there's not a whole lot you can do if you need to remove the encryption (really the key storage/management) from the database. If your MySQL instance is on a Unix host, you can try connecting with a Unix socket file instead of TCP/IP. Just host the app on the same machine during the encryption process, finish ...


0

character_set_server is not relevant. The setting on the database is just a default for when you CREATE TABLE without specifying a DEFAULT CHARACTER SET. You need The bytes in your client to be encoded utf8. The connection specifies utf8. If you are using mysqli, use mysqli_set_charset('utf8'). The column in the table must be CHARACTER SET utf8. Look ...


1

You can do it by running the following query: SELECT a.sender,a.status,a.type,c.p_id,c.p_name,c.st FROM a INNER JOIN b ON a.sender=b.sender INNER JOIN c ON b.p_id = c.p_id WHERE a.status='approved' AND c.st='current';


3

Not sure about mysql but in mssql like '%val' is a full scan Multiple keywords in a varchar(200) column is probably not the best design. Spit the keyswords into separate words into a separate table with a FK back to company.ID. So each word would be a row. Then you can search on keyword.value = 'xxx' and use an index.



Top 50 recent answers are included