Someone16

For one of my websites, I needed solution for randomly displaying different rows from MySQL database. As probably everyone knows, the easiest solution is to use following SQL:

select * from table order by rand()

There are no problems with using that, except one: It's REALLY SLOW. This might not be a problem with table with only few rows, but with bigger tables it really decreases the performance.

After spending a few hours of Googling and testing different methods, I find one, that works really great.

Following query is a lot faster than order by rand() and works without a problem:

SELECT * FROM Table T JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Table) AS x ON T.ID >= x.ID LIMIT 1;

I found this solution on Jay Paroline's blog: http://wanderr.com/jay/order-by-slow/2008/01/30/

I wondered if there is an even faster approach to get random row and then it hit me. So simple solution and it works even faster.

Why use only MySQL if we're building a website? Why wouldn't we rather use MySQL in connection with PHP (or other language) to get random result?

Solution

$x = mysql_fetch_array(mysql_query("SELECT COUNT(*) FROM table"));

$y = $x[0]-1;

$x = rand(0,$y);

$query = mysql_query("SELECT * FROM table LIMIT ".$x.", 1");

Explanation
Firstly, we get count of all rows in table (this value can be memcached if we want even better performance).

Then we subtract 1 from total count and a get random number between 0 and (n-1).

After that we simply use limit to select 1 row at position $x.

I implemented this on my site and everything was a bit faster, but this solution can only fetch one row at a time. In my case I have to display 10 - 40 random rows at once.

So I searched for SQL to randomly get multiple rows and I found following solution:

mysql_query("SELECT * FROM (
SELECT @cnt := COUNT( * ) +1, @lim :=10
FROM table
)vars
STRAIGHT_JOIN (
SELECT table . * , @lim := @lim -1
FROM table
WHERE (
@cnt := @cnt -1
)
AND RAND( ".time(0)." ) < @lim / @cnt
)i");

Again we need to use something else than MySQL to get random seed in this case current time in seconds is used.

This is a great solution, but still slow.

I thought of using PHP's rand to randomly select rows but in that case I will have to have sequential IDs and because of that we won't be able to delete rows somewhere in the middle.

So I ended up with following PHP code:

Multiple rows solution

$query = mysql_query("select id from table");

$ids = array();

while($id = mysql_fetch_query($query)) {
$ids[] = $id[0];
}

$temp = '';

for($i = 0; $i < 40; $i++) {
$temp .= $ids[rand(0, count($ids)-1)].',';
}

$temp = substr($t, 0, strlen($t)-1);

$query = mysql_query("select * from table where id in (".$t.")");

Explanation
This code might seem a bit harder but it's really easy.
First of all we get all IDs from table and put it in array $ids.

Then we use for loop to select 40 random IDs from array and add it to $temp variable and add comma, to separate the.

After that we remove last comma and then select rows from table where id matches ones from $temp.

Result
In my case I have about 50 000 rows and I needed to display 40 random rows. With previous solution with joins the page executed in about 0.7 to 0.8 seconds.
With my solution I speed it up to about 0.1 - 0.15 seconds of execution time. This is about 85% performance improvement.

I’ve been searching with Google from the first day of using the Internet. It’s great search engine, I find everything I want and it always works. Sometimes there are some problems with loading front page of Google, but apart from that I haven’t encounter any problems with it so far.

However, recently I’m testing Bing and I decided to completely switch to it from Google. I’m also unhappy with Google’s policy on keeping my personal information. I’ll only be using Bing search engine and stay with Gmail because it has better UI and I don’t want to change my email address. Of course Google Analytics, Webmaster Tools and other tools have no Microsoft alternative and I’m happy with them, so there is no reason for me to switch.

It’s never bad to check alternatives, and see if they are better (I doubt, but Bing Maps really suprised me in a good way).

More about Google:
http://www.youtube.com/watch?v=R7yfV6RzE30

When playing around with Windows Server 2008 I discovered how good Linux is. Even that I have to use Terminal (which I like anyway), dealing with Linux configuration is easier than dealing with Windows.

My primary area of interest is the Internet and all about it (web sites, databases, programming, servers). I really like open source (from operating system to programming languages), but occasionally I also check on the alternatives (Microsoft’s world, to be precise).

And in the previous week I checked a bit of how stuff works in Windows (DNS, IIS, MS SQL, ASP.Net). On the first look, it seemed a lot easier to work with it because of the GUI, but it soon became clear that I was terribly wrong.

Internet Information Server … great thing, a lot of options and dialogs but there are still stuff that needs to be configured manually. After setting up a few test web sites, I ran into few problems. The biggest was about caching static content on visitor’s computers. I partially configured it by Googling about it.

MS SQL

Again, everything seemed very easy. Creating a database takes few clicks, creating a user takes another few clicks and we have everything we want. Tables can be migrated from MySQL by using ODBC and that’s it. Until…I wanted to access this data from a web application.

Only thing I can get from my web application (just a simple thing for testing some of the MS SQL features and comparing performance to MySQL) was a simple error, that selected user cannot access selected database.

I was confused at first, but then I opened another instance of MS SQL Management Studio, connected to the server using this user, and I was not allowed to access anything. I guessed MS SQL has a unique way of managing rights and so I googled for tutorials and help. As I found out, there is a lot less Internet literature on this topic (MS SQL) than on alternatives.

After reading 30 articles, 400 clicks on buttons, 100 closed dialogs and 2 hours of work, I finally found a solution for this problem. It was pretty simple and I described it on Tech Site & Blog: http://techsiteblog.com/ms-sql-add-user/

I would like to inform you that our website has a 128 bit encryption. With this base, passwords that comprise only of letters and alphabets create an algorithm that is difficult to crack. We discourage the use of special characters because hacking softwares can recignize them very easily.

The length of the password is limited to 8 characters to reduce keyboard contact. Some softwares can decipher a password based on information of “most common keys pressed”.

Therefore, lesser keys punched in a given frame of time lessen the possibility of the password being cracked.

iPad? What’s that? A bigger iPhone!

So Apple announced their first Tablet PC, which is neither a tablet nor a PC. Speculations about what it will look like were all wrong. Apple didn’t make a usable tablet computer, but a friggin’ MP3 player with iBooks application and broken web browser.

Maybe my expectations were too high, but their product is really not something that could be called tablet. I expected something like normal PC tablets with very good battery and Mac OS X operating system with the possibility of using Windows via Boot Camp. However, the only thing they could have came up with was a bigger iPhone intended for reading eBooks and surfing the Internet with Safari (without Flash support!).

As usual, Apple will sell iPads without a problem and without a competition in this market.

Hack a Day has great articles about hacks from all over the world. This time I want to write about a very interesting project about radio controlled lawnmower.

I wanted to build similar lawnmower with the difference that I would use Wiimote for controlling it. But interfering Wiimote directly to an AVR or other micro controller is a bit hard, so I thought of using the laptop to connect Wiimote to a mower. I already made some plans, but then I realized about the biggest problem - danger of using something like this without my direct control over it.

After all, I'm perfectly capable of hurting myself without additional help, so building this could end badly.

Anyway I'm happy that someone actually made something like this, and I have no problems about him using it as long as it's far away from me. :-D

More: http://hackaday.com/2009/11/14/rc-lawnmower/

I stumbled upon an interesting article about wireless transmission of power and thought of sharing it here.

It's really simple and cheap way to wirelessly transmit electricity on really short distance. It's made of big and small coil, a couple of capacitors and LED diode.

More info: http://www.instructables.com/id/Wireless-Power-Transmission-Over-Short-Distances-U/

I'm interested in omnidirectional wheels from when I watched Prototype This!, The Discovery Channel TV series. They used these wheels in episode Six-Legged All Terrain Vehicle.

I thought a few times about building a RC car with these omni wheels, but never got around to even buy needed materials. And of course there is also a lack of tutorials/articles about it on the Internet.

Well, I then forgot about it, but a few days ago I found a post about Omni-car on Hack a Day blog. http://hackaday.com/2009/11/17/omni-car/

Really interesting project, but sadly these wheels are a bit too expensive.

Direct link: http://didyoumakethat.webs.com/projects.htm

/

MS released MVC 1.0 for ASP.Net 3.5 a few months ago. I just didn't take time to learn about it.

And last week I learnt that it's very good addition and something that I had been waiting from the release of first ASP.Net framework. I rather programmed in ASP than in ASP.Net because of freedom. In ASP you are free to make your HTML code as you like. In ASP.Net you have some Web Controls which usually generated really nasty code. And you have no control over this code. Postbacks are pain in the ass too. Google and other search engines can't crawl web pages with postbacks because you need javascript to use them (Google recently announced that they were thinking about making javascript-compatible crawler, but for now no one has that capability).

In ASP.Net MVC you can use your own HTML code and do everything as you like. It's similar to ASP without a framework, except that you actually have a framework. Now you have a choice to use either Web Controls or MVC in your projects. And I also saw some web sites made with it already.

BTW classic ASP is still pretty much alive. There are a lot of sites made with them, I guess they just don't have time/experience/need to rewrite everything.