MySql, exclude selected row from query or search results
I have spent hours scratching my head, to solve this this simple issue .
Basically i have DB table that stores records of users and their details, which contains well over a million rows. The users can login into the system,then can search for other users detail,(name,gender & etc).
The search work fines, the problem is that the search return the results that also include the current logged in users and his/her details. I don't want details of the current logged in user in the search results.
I tried using "WHERE 'userID' != 'loggedinuserId'" , this works, but i wanted to know is their a more effective way going about it?
NOT LIKE SQL Statement
You can try using the 'not like' statement in the where part of SQL statement this would be better because you also could include blocked users so they those users wouldn't display as well. They way you do it now is okay if it works for you but this is what I would recommend because it is more flexible.
"WHERE 'userID' != 'loggedinuserId'" sounds perfect... but this depends on your query, if you select from a derived table it can not see the outer WHERE clause (so this would not be perfect).
You need to show the entire query (or if it is dynamic- show the application code also).
If you want to stress test it- you can use mysqlslap: http://dev.mysql.com/doc/refman/5.1/en/programs.html
There is one way to make it really really fast, which is to have a SET column called userAttributes and one of the attibutes, lets say the first, is SET('loggedInUser'). Then you can SELECT .... WHERE !(userAttributes & 1), it should be faster because it wont have to compare the entire string- it can look at the BIGINT and just see that they're logged in.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread