Say I have a large database consisting of products in groups. Let's say that there are 5 groups, each of which has 100,000 products. product identifiers are random integers (same as group identifiers)
I need to find a product in a specific group. My question is which primary key is more efficient:
sid, pid is intuitive, but when searching in this order, MySQL will have to select 100,000 out of 500,000 lines, and then find one number in 100,000. On the other hand, it (pid, sid)sounds more optimal for me, as this will cause mysql not to create large group 100,000 in the first stage, and go directly to the desired item (or up to 5 points if there are similar pids in different cids).
Is # 2 really faster?
UPDATE: OK. I copied the real table in two copies. table0 has the primary key sid, pid. table1 has pid, sid.
query result:
explain select * from items0, where sid = 22746 and pid = 2109418034 1, "SIMPLE", "items0", "ref", "PRIMARY", "PRIMARY", "8", "const, const", 14, "
explain select * from items1, where sid = 22746 and pid = 2109418034
1, 'SIMPLE', 'items1', 'ref', 'PRIMARY', 'PRIMARY', '8', 'const, const', 11, ''
Another update: I also added two keys to the same table and ran the explanation. got this: (Primary starts with sid_pid1, Index2 starts with pid1, sid)
1, 'SIMPLE', 'items', 'ref', 'PRIMARY, index_2', 'index_2', '8', 'const, const', 13, ''
, ?