1487
Quote:
Next one is the prevention of SELECT * in queries. Maybe it is just a rumour I heard, that the performance is better with SELECT xy, yz in queries. Where applicable of course
Never
heard about that.
Maybe there is some gain, when there are 20+ fields in a table and you need only a few. But most XOOPS tables are rather small, so I don't think there is much benefit
Maybe interesting to do once a test and time 1000 requests (or 100 repeated 10 times) eg selecting in the user or a posts table.
Most important is the use of a suitable index.
The permissions has indeed a scaling problem. If you see queries like
this, my stommach turns upside down.
Quote:
0.000398 - SELECT * FROM config WHERE (conf_modid = '0' AND conf_catid = '3') ORDER BY conf_order ASC
0.000236 - SELECT DISTINCT gperm_itemid FROM group_permission WHERE gperm_name = 'block_read' AND gperm_modid = 1 AND gperm_groupid IN (1,2)
0.000351 - SELECT b.* FROM newblocks b, block_module_link m WHERE m.block_id=b.bid AND b.isactive=1 AND b.visible=1 AND m.module_id IN (0,-1) AND b.bid IN (127,9,56,38,58,34,41,44,159,45,128,39,42,125,40,43,7,6,3,35,77,75,12,97,96,95,193,
30,1,174,173,54,55,182,181,180,57,179,178,177,176,175,148,71,72,73,74,162,161,160,
158,157,76,78,79,80,156,155,154,153,133,132,131,130,102,101,129,126,115,103,114,
113,112,111,104,110,109,108,107,106,105,100,98,99,53,52,190,189,51,50,49,48,47,46,
37,136,139,138,137,135,134,140,194,36,33,32,31,11,10,8,5,4,196,195,192,191,2)
ORDER BY b.weight, m.block_id
There should be some class that can join the permissions of the groups with the item records, so that in one query all the permitted items for a user can be retrieved.