MySQ Two vs One Column Index

Anon

I have a large table (approximately 100,000 records) that has no unique reference columns (in it's current configuration).

Example configuration:

Main Table - Support Call IDs (PRIMARY KEY)

call_id

0001
0002
0003

Data Table - Comments

call_id comment_num

0001 0001
0001 0002
0001 0003
0002 0001
0002 0002
0003 0001
0003 0002
0003 0003
0003 0004
0003 0005

Now, my question is this .. would it be smarter (and more importantly faster) to combine the two columns into one unique indexed column rather than indexing the two non-unique columns in the Comments Table like this:

callid_commentnum

0001_0001
0001_0002
0001_0003
0002_0001
0002_0002
0003_0001
0003_0002
0003_0003
0003_0004
0003_0005

.. and then make that column a primary key since each callid_commentnum value would be unique?

The bottom line question I have is ..

Is it faster to scan a large table using one PRIMARY KEY column or two INDEXed columns for records or does it matter with MySQL?

Thanks,
twixxer

iugin

I think you better keep two fields in this way you can join tables to. But sometimes maybe your second exemple will be faster. All depend on what you need

Anon

With the large table, we could have many thousands (maybe higher) of searches per day so I really need to know which method is faster within MySQL. Even reducing one second off each search would represent a significant overall increase.

twixxer

Anon

Speed also depends on types of columns. If now you're having two int(11) (for example) columns, it is working better than you'll combine them into one column like "111_222" of string type, especially text types, of course, but varchar() is not good for sorting, too. And next. If you're creating index for fields call_id, comment_num, remember that it will work only if you're mentioning these fields in the same order in SQL statement. For example:
... WHERE call_id=1 AND comment_num=2
will use this index, but:
... WHERE comment_num=2 AND call_id=1
will NOT! And, of course, if you're having:
... WHERE comment_num=2
this again will not use your index.
Now, if you're using these columns separately more often than together in your statements, it makes sence to create separate indexes for that columns. But more indexes - more time for updating... I've just measured time when I had to choose what kind of indexing I need.
Read MySQL manual for all these things. It worths reading.