Which among the following queries is going to be faster and much more optimal (and why):
SELECT * FROM items WHERE w = 320 AND h = 200(w and h are INT)
SELECT * FROM items WHERE dimensions = '320x200'(dimensions is VARCHAR)
Most likely the only method to know that's to operate it. I'd suspect when all posts used are indexed, there'd be essentially no difference. If INT is 4 bytes, it will likely be almost exactly the same size because the string.
The main one wrinkle is within how VARCHAR is saved. Should you used a continuing string size, it may be faster than VARCHAR, but mostly since your
select * must download it today.
The large benefit of using INT is you can do a lot more sophisticated blocking. That alone ought to be grounds to prefer it. What should you prefer a range, or simply width, or for you to do math on width within the blocking? How about constraints in line with the posts, or aggregates?
Also, when you are getting the values to your programming language, you will not have to parse them before with them (which needs time to work).
EDIT: Another solutions are mentioning string compares. If indexed, there will not be many string compares done. And you can implement extremely fast compare calculations that don't have to loop byte-by-byte. You'd need to know the particulars of the items mysql gives know without a doubt.
Without effort, if you don't create
INDEXes on individuals posts, integer comparison appears faster.
In integer comparison, you compare directly 32-bit values equality with logical operators.
However, strings are character arrays, it will likely be hard to do a comparison. Character-by-character.
However, another point is the fact that, in second query you've 1 area to check, in first query you've 2 fields. For those who have 1,000,000 records with no indexes on posts, which means you might have 1,000,000 string evaluations on worst situation (unluckily last outcome is the one thing you've searching for or otherwise found whatsoever)
However you've 1,000,000 records and all sorts of are
w=320, then you will be evaluating them for
h,too. Which means 2,000,000 evaluations. Nevertheless, you create INDEXes on individuals fields IMHO they'll be almost identical since VARCHAR is going to be hashed (takes
O(1) constant time) and will also be in comparison using INT comparison and take
Conclusion, it is dependent. Prefer indexes on searchable posts and employ ints.
Here are a few actual dimensions. (Using SQLite may check it out with MySQL later.)
Data = All 1,000,000 mixtures of w, h ∈ , in randomized order.
CREATE TABLE items (id INTEGER PRIMARY KEY, w INTEGER, h INTEGER)
Average time (of 20 runs) to complete
SELECT * FROM items WHERE w = 320 and h = 200 was 5.39±0.29 µs.
CREATE TABLE items (id INTEGER PRIMARY KEY, dimensions TEXT)
Average time for you to execute
SELECT * FROM items WHERE dimensions = '320x200' was 5.69±0.23 µs.
There's no factor, efficiency-smart.
There's an enormous difference when it comes to usability. For instance, if you wish to calculate the region and perimeter from the rectangles, the 2-column approach is simple:
SELECT w * h, 2 * (w + h) FROM items
Attempt to write the related query for that other way.
Second query, because the chances to complement the precise string is more compact (which mean more compact group of records however with greater cardinality)
First query, chances matching first column is greater and much more rows are potentially matched up (lesser cardinality)
obviously, presuming index are defined for scenario
first since it is faster to check number data.
It is dependent around the data and also the available indexes. But it's fairly simple for that VARCHAR version to become faster because searching just one index could be faster than two. When the mixture of values supplies a unique (or "mostly" unique) result while every individual H/W value has multiple records, it could narrow the lower to some much more compact set while using single index.
However, for those who have a multiple column index around the to integer posts, that would probably be the best.