lordvader Posted May 8, 2008 Share Posted May 8, 2008 My primary key is unique id numbers, but because of the nature of my php scripts, they get inserted out of order. I assume that this slows down queries, since the engine has to search the entire db to find a specific primary key. But if it was autoincremented, it would know exactly where to look... Right? But my problem is that not every sequential number gets used... there's lots of gaps because the script will not re-use id numbers that belong to the data entries that get deleted before making it into the db. So if I have lets say, 10 entries in the db, # 1-10, and then I need to add a new row at the end of the table whose id is 15... will mysql with autoincrementing turned on, create rows #11-14? Or do I have to do it myself? If i have to do it myself, do I do a select on rows 11-14, to see if they exist yet? Or is there something more efficient? thanks Quote Link to comment https://forums.phpfreaks.com/topic/104665-solved-filling-in-row-gaps-while-autoincremented/ Share on other sites More sharing options...
clearstatcache Posted May 8, 2008 Share Posted May 8, 2008 how about adding another index key.....say, Sequence_Key..... Quote Link to comment https://forums.phpfreaks.com/topic/104665-solved-filling-in-row-gaps-while-autoincremented/#findComment-535736 Share on other sites More sharing options...
lordvader Posted May 8, 2008 Author Share Posted May 8, 2008 Why would I need another index? Quote Link to comment https://forums.phpfreaks.com/topic/104665-solved-filling-in-row-gaps-while-autoincremented/#findComment-535749 Share on other sites More sharing options...
clearstatcache Posted May 8, 2008 Share Posted May 8, 2008 u used that key as unique_key......aside from ur primary key....u need to have another index(s) which is unique in your table.....to be more efficient.... Quote Link to comment https://forums.phpfreaks.com/topic/104665-solved-filling-in-row-gaps-while-autoincremented/#findComment-535773 Share on other sites More sharing options...
fenway Posted May 8, 2008 Share Posted May 8, 2008 But my problem is that not every sequential number gets used... there's lots of gaps because the script will not re-use id numbers that belong to the data entries that get deleted before making it into the db. It's never supposed to re-use UIDs... and it never should... and neither should you. You shouldn't be using these values for anything meaningful in your application -- if you are, you've made a mistake. Quote Link to comment https://forums.phpfreaks.com/topic/104665-solved-filling-in-row-gaps-while-autoincremented/#findComment-535878 Share on other sites More sharing options...
lordvader Posted May 8, 2008 Author Share Posted May 8, 2008 Okay guys, I'll add another column and make it an index. Quote Link to comment https://forums.phpfreaks.com/topic/104665-solved-filling-in-row-gaps-while-autoincremented/#findComment-536148 Share on other sites More sharing options...
fenway Posted May 9, 2008 Share Posted May 9, 2008 Okay guys, I'll add another column and make it an index. Huh? Why? Quote Link to comment https://forums.phpfreaks.com/topic/104665-solved-filling-in-row-gaps-while-autoincremented/#findComment-536685 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.