0

I want to store multiple id(int) in one row, I only see Varchar for this, and I use commas.

The reason is to improve efficiency:

While inserting/updating, this id's are changed periodically, so if I store 40 id's, instead of doing 40 deletes and 40 inserts, I just do 1 Update.

Then I use this data to make a select query with an IN(id,id2,id3) if I select 40 rows (in a 40x bigger index or table column) with the inefficient method it works well, but I prefer to do the 1 row method, the problem is that it is an string, 'id,id2,id3' and it does not work, it only converts to int the first number, the same that CAST or CONVERT to int does.

I found a method that uses a second table but you have to store all the id’s, that’s not possible there are too many and they are created every second, that would be inefficient.

I think there should be something, it's so simple and so useful, but I'm not finding any efficient and simple way to convert the string to a comma separated integer list.

In summary: I want to do this, being varch_ids a varchar '1,2,3,4' in 1 row

SELECT * FROM main_table
WHERE main_id IN (SELECT varch_ids FROM table2 WHERE data_id=$x)

Yes I can do two separated queries and process the string, but the most important and used query is the SELECT, and two queries would probably be slower than selecting 40 rows in 1 query.

Shadow
  • 32,277
  • 10
  • 49
  • 61
Vixxs
  • 585
  • 6
  • 20
  • 6
    Don't store comma-separated strings. Store individual rows, no matter how "inefficient" it may sound. You'll thank yourself later. – Sergey Kalinichenko Dec 03 '16 at 16:48
  • 1
    Comma-separated list of values is a known SQL Antipattern: https://www.safaribooksonline.com/library/view/sql-antipatterns/9781680500073/f_0015.html – LSerni Dec 03 '16 at 16:55
  • I didn't mention that I use a WHERE condition in the subquery, let's call it data_id, if I want the query to be fast, I should set data_id as an index, and if I store each of the 40 main_id in a different row the index from the data_id's would be 40 times bigger right? I don’t see any drawbacks for what I need other that I cannot make it work… Maybe I should not use an index since the data size is small, just two int's. – Vixxs Dec 03 '16 at 17:27
  • Hope it helps ! – Tom Taylor Dec 03 '16 at 17:31
  • 1
    Bottom line is don't do it. You will endure Death by `find_in_set`, a myriad of other problems. See Karwin's answer [here](http://stackoverflow.com/a/3653574) or mine above in the dupe close. Shadow has one too. – Drew Dec 04 '16 at 00:18
  • 1
    Also note that just because a human can visualize it easier in our simple gray matter doesn't make it faster or more efficient. If you can show me that I am wrong on a large dataset with relations to other tables (forgetting that you can't even use Foreign Key constraints), then I will buy you an Online Ice Cream or Coffee card. Just show me how much more efficient Joins, Updates, and Deletes perform. $20. Chocolate ice cream. Yum. – Drew Dec 04 '16 at 00:23

0 Answers0