Mysql – Large MySQL table, inserts with primary key check

MySQLPHP

I have a table with 12 million of rows and a following task I should frequently perform:

Get search results from somewhere (50 rows). Each result has a key looking as a md5 hash and a table's PM is built on this field.
Check what rows are currently stored.
Store all other rows

Question is what is the best way to perform steps 2-3. I use PHP and Doctrine so not all tricky queries are possible to use. For example I can't use bulk inserts so need to run INSERT up to 50 times in a row.
I see two possible ways:

run SELECT … WHERE id IN(…) with all 50 IDs and see what is returned, then run as many inserts as I need
run 50 inserts and catch duplicated ID error

Best Answer

I think what you're looking is simply to use INSERT IGNORE. Forget about steps 1 and 2, just insert and ignore :)

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

Related Solutions

Mysql – InnoDB table vs MEMORY table for a thesql environment with many inserts

Clearly, it will be faster and more efficient to write/read to/from memory. In your case, losing the data is not a big issue as you stated. However, you need to be careful about the memory usage and limit the number of rows stored.

Mysql – How should I save apache logs into a thesql table

Because this is really logging data that you are capturing, I would first store it in its raw form, then ETL/normalize it as needed.

For searching, you can index the front part of the URLs first few characters. Let's say the options are http:// and https:// - then limit the index size to three characters passed the protocol part.

CREATE TABLE log ( 
datetime_created DATETIME, 
url VARCHAR(1024), 
domainname VARCHAR(255),
someotherdata VARCHAR(255), 
...
INDEX `idx_domain` (domainname(3))) 
ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

InnoDB will allow you to search the table w/o locking it.
Compression will help with disk space.
INDEX idx_domain (domainname(3)) will index the first 3 characters of the domain name column and speed up searches. However, the strategy for REGEXP will be to try to match on the front of the domain name.

Scale will eventually be an issue if the site becomes popular, so buyer beware.

Best Answer

Related Solutions

Mysql – InnoDB table vs MEMORY table for a thesql environment with many inserts

Mysql – How should I save apache logs into a thesql table

Related Question