* [EMAIL PROTECTED] > Hi is there any noticable efficiency is ordering the position of the field > types?. Like say i place the join keys in a table at the end, and varchars > and text at the top of the table does it really matter?
Have you tried? I would guess it does not matter. But for some reason, I always put my small fixed width columns first and any bigger, variable length columns last... it kind of feels right. :) What _does_ matter is if you can remove any varchar/text/blob column from a table, so that the entire row gets a fixed width. These tables are faster than tables with varchar/text/blob columns: <URL: http://www.mysql.com/doc/en/Static_format.html > > Also when i do a query like select * from table where id IN (1), > where 1 is usually a primary key int is that quicker than doing > where id=1 or is there no difference Both would use the index, so that is not an issue. I would guess "id=1" is faster, because it's such a basic operation. But let's see what mysql has to say about it: mysql> select benchmark(10000000,2 in (2)); +------------------------------+ | benchmark(10000000,2 in (2)) | +------------------------------+ | 0 | +------------------------------+ 1 row in set (1.87 sec) mysql> select benchmark(10000000,2 = 2); +---------------------------+ | benchmark(10000000,2 = 2) | +---------------------------+ | 0 | +---------------------------+ 1 row in set (1.20 sec) 1.9 seconds for the IN operator and 1.2 seconds for the = operator, 10 million operations on my PIII 600Mhz W2K. The = operator is, as expected, faster than the IN operator, at least in our simple integer test. When there are many items to check IN is faster: mysql> select benchmark(10000000,2=1 or 2=9 or 2=3 or 2=4 or 2=5 or 2=6 or 2=7 or 2=8 or 2=2) "2=2"; +-----+ | 2=2 | +-----+ | 0 | +-----+ 1 row in set (9.95 sec) mysql> select benchmark(10000000,2 in (1,9,3,4,5,6,7,8,2)) "2 IN (2)"; +----------+ | 2 IN (2) | +----------+ | 0 | +----------+ 1 row in set (3.01 sec) 10 seconds with = and OR, 3 seconds with IN. Of course, it is the additional OR's that makes it so slow, while the IN operator has the advantage of beeing made for multiple operands. > and is it handling the int as a string or as an int? If the column type is int, it is handled as an int. mysql will not complain if you query using ...id="2", but it will have to convert the "2" to an integer for each row, and this will be slower than if you said id=2 (thus, indicating an integer 2) in the first place. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
