CODEWITHSUNDEEP
mysql
gom
gom

Reputation: 897

Telling MySQL the limit of a where condition being false

If I've a statement:

select .. from .. where condition;

And I know beforehand the maximum number of times the condition can be false. How can I tell MySQL this number to increase performance? So that after the condition has been false that many times (while selecting the records), MYSQL will assume the condition to be true for the rest of the records.

Edit:

I don't know what's missing in my question. I'll fill up the blanks:

select * from t where c3 > c2;

Here I know beforehand that out of 50 records there are only 5 records where the condition 'c3 > c2' is false. Can I and should I tell MySQL this number '5' to increase the performence of this select statement? So that while executing this select statement, after this condition (c3 > c2) has been falsed 5 times, MySQL will ignore this condition for the rest of the records and will select all the records from then on.

Upvotes: 0

Views: 93

Answers (2)

kastermester
kastermester

Reputation: 3064

Without more detailed description of the problem at hand, I do not think it is possible to say much more than it seems like your understanding of how the database works lacks quite a bit.

To make matters simple, imagine a binary search tree. Now take your predicate (your where condition) and consider a way to construct a binary search tree which can aid you in getting the results of your query. As a simple example - consider the following query:

SELECT * FROM myTable WHERE some_value = 10

A simple binary search tree on "some_value" (that is - a binary search tree - where for each node in the tree other rows with a lower "some_value" than the current row will go to the left, and rows with higher - or equal - values go to the right) - can help in this situation. Utilizing such a binary search tree one can now simply follow from the root of the tree, following the left or right path down the tree depending on the value of the current node - until the value "10" has been reached - and then continuing to follow the right path until a higher value has been found.

You can imagine how this would work for a query such as

SELECT * FROM myTable WHERE some_value BETWEEN 10 AND 20

Again, the same simple binary search tree can easily answer this query. Of course one could easily dwell into more complex examples - but at this point I am guessing you're thinking: Well this is great, but how do I create this binary search tree?

The answer to this is an index - in this case:

CREATE INDEX idx_myTable__some_value ON myTable(some_value);

This will tell MySQL to create an index on the table "myTable" over the columns (in the specified order) "some_value" - and the index will be named "idx_myTable__some_value".

I think this is about as far into this topic as I can go in a simple answer like this. Let me however state that the above is an oversimplification - there's alot more to be said about this - to start off with, the actual index type being used is typically not even a binary search tree, but a B-tree (or, more likely, a B+-tree). Wikipedia has some decent articles about this, and the actual manual for MySQL should cover this quite well as well.

Upvotes: 1

JensB
JensB

Reputation: 6850

The simple answer is you can't.

But the thing you can do in your quest for performance is smart database design and well placed indexes.

MySQL "preformats" index columns so that it can check your where constraints against these columns very fast.

The MySQL website ( http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html ) list a few reasons why you should use indexes of which the top two are listed below, but you should read through all of that page:

  • To find the rows matching a WHERE clause quickly.

  • To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows

Also a simple example of how to build smarter tables could be that instead of storing the name "John Doe" in one column you could store it in two so that you can search for anyone with the last name "Doe" without splitting or doing LIKE searches.

You should try to avoid conditions such as LIKE as these are slow and tedious no matter how you write them.

Among the fastest columns to do logic on are integer and float columns.

Upvotes: 1

Related Questions