1

How's it going. I'm hoping that this isn't considered to be a duplicate of... mysql two column primary key with auto-increment

My question is a little different though as I am not trying to reference a different database. I would just like to know the most 'performant' way to go about this... I want to have a comments table that has say page_id as the primary key, but I still want to have an auto increment id associated with each row so I can address them by id when updating or deleting them. As of now my table is structured as follows

id INT(8) NOT NULL AUTO_INCREMENT,
page_id INT(5) NOT NULL,
comment VARCHAR(2500) NOT NULL,
PRIMARY KEY (id)

And my queries look like....

SELECT comment FROM comments WHERE page_id = '$page_id' AND id >= $start_at_id

I've been watching a bunch of MySQL optimization videos and I keep hearing index on the left, but my problem is that I am most of the way blind so I can't see their code examples. Here my screen reader will make all of that possible. My question is this. Would it be better to set my table up like...

page_id INT(5) NOT NULL,
id INT(8) NOT NULL AUTO_INCREMENT,
comment VARCHAR(2500) NOT NULL,
 PRIMARY KEY (page_id)

And if so... What would be a better way to structure the query. Keep in mind that "start_at_id" is 0 unless it's a pagination page. I'm using the default engine for mysql 5.6 which I believe is my (pardon my spelling) isam???

Thanks in advance.

Community
  • 1
  • 1
Ian
  • 61
  • 5
  • It's spelt MyISAM, (that's M, y, I, S, A, M), with all the letters except the 'y' in uppercase - not sure how easy it is to get the spellings of words when using a screenreader. But MyISAM is *not* the default storage engine any more - the default is InnoDB. – Mark Amery Oct 12 '14 at 19:10
  • Also, your second table schema example (with `page_id` as the PRIMARY KEY) doesn't make sense to me; presumably you can have multiple comments per page, so the `page_id` will not be unique in the Comment table? A PRIMARY KEY implies a uniqueness constraint, so - performance aside - your second approach wouldn't work. – Mark Amery Oct 12 '14 at 19:11

2 Answers2

0

Looks like you do not have to add any modifications at all because the Id column has the primary key index itself and your query SELECT comment FROM comments WHERE page_id = '$page_id' AND id >= $start_at_id uses the Id column in the where clause. So, no requirement of any more indexes. You may add an index to the page_id column. But from my experience, that will not make any difference in YOUR QUERY. But page_id ONLY queries will definitely speed up.

Deval Khandelwal
  • 3,458
  • 1
  • 27
  • 38
  • Just a quick follow up question to this answer... Do you mean that if I add an index to the page_id column that my queries will speed up or that if I remove the AND clause from the query in general, then they will speed up? I should have made that more clear at first. On the initial page there is no AND clause anymore thanks to your insight since it is stupid to use one when I don't need one since it was >= 0. I also forgot to put in the fact that I have a LIMIT 51 after everything above. I use that extra 1 to decide if I need to add a next link where I will then say AND >= that id. – Ian Oct 13 '14 at 04:47
0

Your best used index for your query would be a 

ALTER TABLE comments ADD UNIQUE INDEX myIndex USING BTREE (page_id, id)

This is because page_id is constant what is very good for optimization. But you have a >= for id so dont use the Default HASH index-type. BTREE is best uses for that since HASH (Default) are best used for equal/unequal comparison (see here)

Benvorth
  • 7,416
  • 8
  • 49
  • 70
  • This sounds like a good idea too. If you get time could you throw in an example of how to properly implement this when creating the table instead of having to use the alter statement. Thanks again for the insight. – Ian Oct 13 '14 at 05:01
  • CREATE TABLE ( ..., UNIQUE INDEX myIndex USING BTREE (page_id, id)) – Benvorth Oct 13 '14 at 09:42