Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an 8 digit number in my db. I want to do a select on the table in MySql (Innodb) and get the list of all numbers that are equal or smaller than my number. Then I sort them from larger to smaller.

I store the number as Integer. Let's say my number is : 12040670

and after the select I get this list :

10340070 12005608 . . . etc

Then I sort them from large to small.

If my table contains 1,000,000 records, and I run such comparison, do you think it causes lots of load on the db server ? How about indexing the column ? Does indexing help in lowering overhead when it comes to integer comparison ?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

My answer assumes that you are describing a simple query like this:

SELECT `Number` FROM `Table` WHERE `Number` <= 12040670 ORDER BY `Number` DESC

From your question, it's not clear how you're storing the numbers. If the column isn't an integer data type, you should make one (probably INT UNSIGNED). That will help the performance whether the column is indexed or not.

There probably should be an index on the numbers column; it will significantly increase the speed of the query. If the numbers column is the PRIMARY key, then it's already indexed.

The overhead of integer comparison isn't really relevant here; if the numbers are INTs, the comparison is very simple and efficient and if they aren't, the data types of the data and test value would be cast to the same type, which doesn't cost much at all. The real concern would be having to read the entire table if there isn't an index available.

1,000,000 rows is nothing to MySQL. If this query uses an index, there will be practically no load from it. If there is no index and it has to read the entire table, there may be some load and the server may need to hit the disk if the table isn't all in memory, but this query is simple and it probably wouldn't cause significant load. It may take up to a second to complete, but unless you would be running the query with a different reference number frequently, you probably wouldn't notice the query.

share|improve this answer
    
Thank you so much. This answered my question very well. Appreciate it! –  David Mar 7 '13 at 17:48
    
+1 but one additional point is that if the column is an INTEGER column, and is indexed, and the index can be used by the query, then MySQL doesn't actually need to sort them, because it can just read them directly from the index, where they're already stored, sorted... which is the reason, as you suggested, that the query will be fast, and is another reason why good indexing and good queries are important -- because the difference between well-optimized and not-so-much, even at a relatively small 100,000 rows, could be significant. –  Michael - sqlbot Mar 8 '13 at 2:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.