mrkeck Sharing the Love

12May/110

SQL Optimization

MSSQL
Ran this query in a table with indexes and 6 million records
select top 50 id from table1 where isBatched is null
Took 13 seconds.

select top 50 id from table1 where isBatched is null order by id desc
Took 0 seconds

All the records that are not batched are at the end of the table so it was able to do soooo much faster.
This appears to work this way on anything where all the results are at the end of the table.

Tagged as: , , No Comments
28Mar/110

mySQL Number Range

Integer Types

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type.

Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32,768 32,767
0 65,535
MEDIUMINT 3 -8,388,608 8,388,607
0 16,777,215
INT 4 -2,147,483,648 2,147,483,647
0 4,294,967,295
BIGINT 8 -92,23,372,036,854,775,808 9,223,372,036,854,775,807
0 18,446,744,073,709,551,615

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Filed under: mySQL No Comments