Blazej Klisz's Blog

MySQL stored programs: cursors

2012-06-16T23:23:30+02:00

Every cursor must have at least 4 elements named below: DECLARE keyword cursor name CURSOR FOR keywords SELECT statement The sample procedure that does nothing more that just creating cursor, looks like this: CREATE PROCEDURE sample_procedure () BEGIN DECLARE cur CURSOR FOR SELECT title FROM film; END; I've put every part in a new line but in a such simple code it's not recommended. Declaring cursor it's necessary to put it after all variables declarations. It's just a meter of order that this code will work: DECLARE str VARCHAR(50); DECLARE cur CURSOR FOR SELECT title FROM film; ...and this on will give an error. DECLARE cur CURSOR FOR SELECT title FROM film; DECLARE str VARCHAR(50); MySQL Database Error: Variable or condition declaration after cursor or handler declaration. When the cursor is ready we can finally use it. Use of cursor is based on 3 commands: open fetch close The first one and the last one are very simple. When you starting using cursor just write OPEN cursor_name, where cursor_name is your real cursor's name. According to this rule: OPEN cur; When the cursor is not needed anymore you can close it by typing CLOSE cursor_name. CLOSE cur; You can't open cursor when it's open, you can't closed cursor that isn't open and you can't fetch cursor that is closed. Contrary to the open and the close commands the fetch is can be quite complicated. A fetch statement consists of 4 elements: FETCH keyword CURSOR name INTO keyword list of variables delimited by a comma In this statement we are dispatching data from query's result to the variables declared in stored procedure. Most important thing is to set on a list as many variables as columns return in query - every column must have its own variable. Below I present next stage of a procedure's body. I used SELECT to show that the cursor is working, but obviously it's not necessary. DECLARE str VARCHAR(50); DECLARE cur CURSOR FOR SELECT title FROM film; OPEN cur; FETCH cur INTO str; SELECT str; CLOSE cur; OK. Cursor is working, at least in some way, but shouldn`t there be more that one row (as many as rows in the film table to be exact)? Of course there should be. So what happened? Cursor was opened, then value of the first row was inserted into str variable, the select statement was made and after all of this the cursor was closed. Probably you are already know that cursor processing must be more complex when its select query retrieves result set rather than single row. This is the place where loops came into play. In MySQL stored programs we can make use of 3 types of loops: LOOP - END LOOP WHILE - END WHILE REPEAT UNTIL If you are familiar with some programming language you have probably meet while and repeat loops. Both of them have conditions that tells the program when it should leave the loop. Simple MySQL's loop don't require any type of condition of that type, so loop in its most basic form will run forever. Code of infinite loop is below: DECLARE i int default 1; simple_loop: LOOP SELECT i; SET i = i + 1; END LOOP simple_loop; When instead showing infinite list of number we put inside the loop a fetch cursor command we will receive a list of title and an error... simple_loop: LOOP FETCH cur INTO str; SELECT str; END LOOP simple_loop; No data - zero rows fetched, selected, or processed If there is any sense using loop that don't even know where it have to stop? Of course not, but fortunately we can quickly fix it with at least 2 ways. First one is based on a counting fetched rows and comparing current number with number of rows return by query statement. It can look like this: DECLARE str VARCHAR(50); DECLARE cnt INT; DECLARE i INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT title FROM film LIMIT 5; SELECT COUNT(*) FROM film WHERE film_id <= 5 INTO cnt; OPEN cur; simple_loop: LOOP SET i = i + 1; IF i > cnt THEN LEAVE simple_loop; END IF; FETCH cur INTO str; SELECT str; END LOOP simple_loop; CLOSE cur; The second solution is based on error handling. We have to define what should happen when there is no more rows. Code below is shorter, cleaner and will give the same result as before. DECLARE str VARCHAR(50); DECLARE end_loop INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT title FROM film LIMIT 5; DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_loop = 1; OPEN cur; simple_loop: LOOP FETCH cur INTO str; SELECT str; IF end_loop = 1 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; CLOSE cur; Remember that declaration of a handler must follow cursor declaration. Finally our cursor is completed and it's really working. Next time I'll build the same cursor using two other loops.

MySQL stored programs: functions

2012-04-30T23:16:37+02:00

Probably you are using functions such as min(), max(), count(), sum() on a regular basis. Functions written as stored programs works the in the same way (from a developer's point of view). Most important features of functions are: they can't modify parameters, they return a single value, not a result set, they can be called as a part of SQL statements. Functions consists of: declaration, which is build from: function name, list of parameters, returns statement. function body, which is build from: declarations, processing block, return statement. Before we go further please make sure you have the sakila sample database and a SQL query editor. If you don't have the database, you can download it from here. As the editor I recommend Toad for MySQL. It's free, very comfortable and have a lot of features. Now lest look at the sakila database. If you are connected to your MySQL server you should see the Object Explorer just below the Connection Manager. Click on the functions tab and locate the inventory_in_stock function, click on it, and the click the second image from left (letter f with blue triangle) - alter the selected function. If everything went fine, you should see function code on your screen. Now I'll try comment every line of this code. DROP FUNCTION IF EXISTS sakila.get_customer_balance; If you're altering any stored program you have to first delete it and than create it. There is no ALTER statement that you may know from T-SQL. If you are creating function for a first time, changing its name or you have deleted it manually, you don't have to use drop statement. Thanks to IF EXISTS part even in situations that I've listed above it won't cause any errors. Long story short this line is universal template for creating and altering stored programs, so memorize it and use it. CREATE FUNCTION sakila.`get_customer_balance`(p_customer_id INT, p_effective_date DATETIME) RETURNS decimal(5,2) It's a very important part so let's split it into pieces: CREATE FUNCTION sakila.`get_customer_balance` - this part is necessary. Every function have to be created and must have a name. (p_customer_id INT, p_effective_date DATETIME) - after a function name there must be a pair of brackets. If the function don't have any parameters just leave it empty (). In this example we see that function takes as parameters two separated by a comma values: p_customer_id and p_effective_date. First one must be an integer type, second must be a type of datetime. RETURNS decimal(5,2) - in last part we see type declaration of value returned by the function. READS SQL DATA This line means that some data will be retrieved from database, but nothing will be changed. Other possible values in this place are: CONTAINS SQL (function have SQL statements that don't read or modify data), NO SQL (no SQL statements at all), MODIFIES SQL DATA (data will be modified). DETERMINISTIC If your function always returns the same value for the same parameters that means it is deterministic, otherwise it is nondeterministic. Though wrong declaration can't cause an error it may mislead SQL optimizer. BEGIN . . . END; Every block of code must start with BEGIN statement and end with END;, so do a body of function. #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID Sample comment. As you can see below it can also start after SQL statement. DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY Three declarations of values with its types. SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees FROM film, inventory, rental WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration, ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees FROM rental, inventory, film WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date <= p_effective_date AND rental.customer_id = p_customer_id; SELECT IFNULL(SUM(payment.amount),0) INTO v_payments FROM payment WHERE payment.payment_date <= p_effective_date AND payment.customer_id = p_customer_id; Three statements retrieving data. Each of statements use parameters values p_customer_id and p_effective_data and each returning query result into a previously declared value. Beside executing statements you can use loops, conditional statements and assignments. RETURN v_rentfees + v_overfees - v_payments; Finally function is returning of result of addition v_rentfees and v_overfrees and subtracting v_peyments. You can call this function using simple select query: SELECT get_customer_balance(16, '2012-00-00 00:00:00'); First function behind you. Of course it's only a part of stored procedures complexity. Hope you've learned something and I'll have time soon to write next parts about procedures, triggers, error handling. If you're looking a comprehensive guide strictly about SQL/PSM I recommend reading MySQL Stored Procedure Programming by Guy Harrison and Steven Feuerstein. As far as I know it's only book in this field. It was published in 2006 but and I'm not sure if latest releases of MySQL didn't added more features, but still it's a good introduction (with more that 600 pages).

Basic chart with Raphael and a few learning resources

2012-01-29T15:03:01+01:00

For those who are not common with a whole concept cross-browser SVG generating with a help of JavaScript, please look at Raphael official site: Raphael If you think that's impressive enough to spend some time with this library documentation might be needed: Raphaels documentation g.Raphael - plug-in for building charts that I've mentioned previously. It's nice to create basic line, pie, dot and bar charts. It look really neat, works great, has a few fine options so if you are building something own definitely look at its code. g.Raphael - Official charting plugin for Raphael Github repository Unfortunately there is no documentation, so if you want to use it you have to learn from examples` source codes and look at unofficial references like those two: g.Raphael - Official charting plugin for Raphael Github repository The second isn't current (latest version number is 0.5) but gives a nice overview. Finally a list of most interesting articles, tutorials and snippets about Raphael: Creating a worlsd map with Raphael Raphael.js Sketch Animation Raphael JS Quickstart Raphael SketchPad Export SVG from Raphael JS to create a PNG bitmap Raphaels Live examples Introduction to Raphael.js An Introduction to the Raphael JS Library Some of them might be little out of date, but still they are very good source of knowledge. Now my chart. It's buggy, messy and not universal but it has some neat solutions which could be time saving. So watch the chart and grab the code on Github.

JavaScript's and PHP's grid tools

2011-11-27T13:21:08+01:00

Here it's a summary. I've divided all grids into 4 categories depending on place of code execution and comprehensiveness of the library. Some of libraries are mentioned in my previous post about RIA. Except name and link to the home page of plug-ins/libraries I'm giving a short information about license and date of the last update, the most important things beside grid's features. Grid tools written in JavaScript Name License Last update jqGrid GPL and MIT November 2011 ingrid GPL November 2011 SlickGrid MIT November 2011 DataTables GPL and BSD November 2011 tablesorter GPL and MIT March 2008 Sigma Ajax Grid LGPL and commercial April 2010 flexigrid GPL and MIT May 2011 tQuery commertial Grid tools written in PHP Name License Last update php Grid free and commercial March 2011 AJAX Grid LGPL Eye Data grid open source December 2011 Libraries with grids written in JavaScript Name License Last update dhtmlx GPL and commercial November 2011 dojo toolkit BSD and AFL 2.1 May 2011 Ext JS 4 GPL and commercial October 2011 jqGrid commercial November 2011 Wijmo commercial October 2011 qooXdoo commercial April 2011 Bindows free and commercial Libraries with grids written PHP Name License Last update apPHP Data Grid free and commercial KoolGrid free and commercial August 2010

PHP jQuery cookbook review

2011-11-20T20:26:41+01:00

The front page headline says "Over 60 simple but highly effective recipes to create interactive web applications using PHP with jQuery", it couldn't be surprise taking into account its title. Since it's a cookbook not a handbook we have a package of ready to use solutions rather that tutorials learning what the JavaScript is. For me as a intermediate jQuery programmer it's a well title. I don't have to read once again basic topics about selectors, events and effects. Most common problem that I have with this type of books is that the solutions that I count find there not only makes me ponder over them but sometimes even contradict the knowledge that I have. This time it is different. From the beginning I've a filling and mental comfort that the author bases on best practices that we could be find in many web resources. It helps to straighten up information I have. For me most interesting recipes were: checking for missing images. detecting an AJAX requests. aborting AJAX requests. loading JavaScript on demand to reduce page load. fading element after updating it. sending cross-domain requests using server proxy. making cross-domain requests with jQuery. creating endless scrolling page. Beside that it help me to do a small revision of simple XML and DOM libraries. Recapitulating it's a well written book with interesting and useful recipes and with really in-depth explanations. Exactly those explanations and a form of book as a coherent source of a proven knowledge are its main advantages. Before you buy check table of contents if you need it. If you do, you shouldn't be disappointed.

Attaching an event to a form's elements inside an iframe with a use of jQuery.

2011-10-30T11:02:05+01:00

Did I mentioned that the side where the form belongs was on a different domain then the target of the form (an action attribute)? I thought it would be really easy, even if it wasn't only a pure HTML form because of a unacceptable redirection to the other side. Just a few lines of jQuery and I've got a nice asynchronous form that will add any provided e-mail address. It would be easy if only not the cross domain policy about which I forgot. An AJAX request didn't sound optimistic at the time. How about $getJSON()? Nope, it had to be a POST method. I had to quit with a simple AJAX form, there was no way it could work. Next I've tried something little more complex an AJAX form calling a PHP script located on the same domain, which with a use of a cURL extension should create exactly the same POST request as a real HTML form, send it to the target and get a response. It's a popular method to walk around the cross domain JavaScript's limitations and it's called proxy. In theory it looks well, but for a some reason it failed. I had the same values, post arrays looked exactly the same, even the cookies were identical and all I got was an application error. Here it's my proxy script. $ch = curl_init(); $data = array('email' => 'email@test.com', 'subscription_type' => 'E', 'id' => '6', 'extra_ar' => ''); curl_setopt($ch, CURLOPT_URL, 'http://www.domain2.com); curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0.1) Gecko/20100101 Firefox/7.0.1"); $strCookie = 'zenid=2ff8666d6c5f92054a5e0d9f10206bc0; path=/'; curl_setopt($ch, CURLOPT_COOKIE, $strCookie ); curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/x-www-form-urlencoded")); curl_setopt($ch, CURLOPT_POST, TRUE); curl_setopt($ch, CURLOPT_HTTPGET, FALSE); curl_setopt($ch, CURLOPT_POSTFIELDS, $data); curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); curl_exec($ch); It was time for plan C. Iframe + content manipulation. As usual it sounds easy and as always it almost is. Putting a HTML document located on the same server as a parent document of iframe into iframe went smoothly. Attaching an event handler to a submit button in the loaded form turn out not to be obvious. First try. $('iframe form').bind(funtion(){ submit: function(){ console.log('Success!'); } }); Not working. A little research on Stackoverflow and I've a similar problem and a few answers, a quick look on the jQuery's documentation and a working example at http://api.jquery.com/contents/. $('iframe') .contents().find('form').bind(function(){ submit: function(){ console.log('Success!'); } }); It's not working. Instead of DOM element tree I'm getting an empty jQuery object. More reading, more answers. Looks similar but I had to give it a try. Unfortunately it wasn't the solution for my problem either. Still getting a blank document. A snippet from Stackoverflow below: $(document.getElementById('IFrameId').contentWindow.document).keydown( function(){ alert('Key down!'); } ); More and more reading and finally I understood what was the problem. Document in iframe wasn't ready at the time of executing the event attach. I found an answer on Stackoferwlow and made my script based on it: var url = 'http://localdomain.com'; //creating an iframe $('.iframewrap'). append(''); //caching jQuery object var iframe = $('#frameid'); //styling and setting url iframe.css({'border': 'none', 'overflow': 'hidden'}); iframe.attr('src', url); //making sure iframe's content is ready iframe.load(function(){ callback(this); }); function callback(frame){ //pay attention to the way of creating //selector's context $('#frameid').contents()! $('#sendnewsletter', $('#frameid').contents()).bind({ submit: function(){ //hiding a page loaded after submission... $('#frameid').fadeOut('slow', function(){ //...and showing our gratitude $('#postsendmsg') .html('Thanks for subscribing!'); }); } }); } It cost me a couple of hour but now I'm satisfied with how it looks and works, and what's the most important I really learned a lot. Recapitulating: if you want to do a cross domain AJAX request you can: Make a get request and get a JSONP answer. Make a proxy server side script which will make a request instead of JavaScript. You can use an iframe and manipulate it's structure when you are sure the hole document is loaded. Of course there is more solutions based on YQL and Flash. I haven't tested them yet so I'm just giving a sort note about theirs existence.

11 jQuery performance tips

2011-08-10T19:56:54+02:00

Today I've watched a slides from a great presentation whose author is Addy Osmani. Slides can be found here: jQuery Proven Performance Tips And Tricks (Slides). I strongly recommend watching it (with all linked tests on http://jsperf.com) and if necessary read my short summary posted bellow. Stay up to date If it's possible try to use the latest version of jQuery. If you're updating library check if all plug-ins still work, if not - look for a plug-in updates. New releases of jQuery can be divided into two groups. If a second number is changed (1.3 => 1.4) we can expect a new features and a performance improvements, also a chance of lack of compatibility with currently used plug-in is quite high. If a third number is changed it means we're dealing with a bug fixes so some problems can be fix and there shouldn't be any problems with a compatibility. Of course we shouldn't expect any speed improvements. Use the fastest selectors The best selectors are those using ID. jQuery can take an advantage of plain JS method getElementById(). Second in a line are element selectors using native getElementByTagName() method. Selectors based on classes are not so great even if modern browser have native getElementsByClassName() method. Pseudo and attribute selectors are the slowest one. The list of browsers with native querySelector() and querySelectorAll() methods is shorter then the previous one. Choosing the best method for obtaining children and parents If you want to find a children of a given element you got at least a few methods to chose from. You can use context (created or cached), children() method, find() method or use all kinds of selectors based on CSS. According to Addy Osmani find() method used on a cached parent element is generally the fastest one. It'll be the best in most cases but really it depends on situation... so to be sure, you have to check it anyway. Try to use plain JavaScript For simple task such as retrieving value of ID attribute it's faster to do it by yourself in pure JS that doing it in jQuery which will do it in JS anyway spending more time on it. Cache elements Traversing through DOM tree can be time-consuming task so it's better to avoid it. Instead of creating the same element over and over again try to cache it by assigning it to a JS variable. Next time you'll be using this element, DOM traversing will be omitted. Use method chaining Most of jQuery methods return a jQuery objects. It means that immediately after one method you can call another method on the object returned by the first method. Sounds complicated? Please look at example: $('div').find('span') .addClass('hidden') .fadeOut(); It's clearer way of writing code and it's got a better performance. For mass event creating use delegate() method It have advantages over bind() and live() methods. The bind() method doesn't support elements created after the bind() method was called. For this type of situations you have to use the live() or the delegate() method. The live() is a simple implementation of delegation used in vanilla JavaScript (you can read about it here: Event delegation). It's a great concept helping to avoid cloning event handlers. So why not the live()? It's slower and it don't allow for chaining. Watch out for adding elements to DOM Don't append new elements if you are looking for a place to store data. Use a data() method instead. When you're append an element try to do it on a cached element. Beside that do it once with prepared string. Look at this performance test: http://jsperf.com/string-concat-single-append-vs-multiple-append Try to use detach() method. Use data() method in these way: $.data('#elem', key, value) rather then this way: $('#elem').data(key, value) . Use native loop rather than $.each() It's better to take advantage of selector engine than use loops. If you have to use a loop try the native one in the first place. A great loops` compression: http://jsperf.com/jquery-each-vs-for-loop/24 Avoid creating unnecessary objects This way: $.text($elem) is much better than this way: $elem.text(); DRY that is Don't Repeat Yourself More repeated code means greater size, greater mess and more problems with maintenance. Looks quite obvious.

Time-consuming PHP and JavaScript

2011-06-25T19:26:43+02:00

First of all 2 main projects. Learning PHP 5.3, PHP design patterns and learning plain JavaScript. After years of searching a good book about PHP, that could help me rise my skills into upper level, I've found it. It's PHP Objects, Patterns, and Practice by Matt Zandstra. What's so great about this book? Almost 200 pages about design patters from the perspective of PHP. It helped me understand a lot and still helping because I haven't finished yet. After I've read it once it becomes my textbook during creating my private projects. Beside design patters, this book have nice chapters about OOP - nothing fancy, but it may help make some quick review, PEAR library, phpDocumentator, SVN, PHPUnit, Phing and Continuous Integration. Cutting a long story short this book is giving you a great foundations of skills that you need to have if you like to consider yourself as advanced PHP programmer. As additional learning resource I recommend series about design patterns - Practical PHP patterns and new series about refactoring Practical PHP refactoring. Second most time consuming project, came up unexpectedly. After spending hours and hours on PHP I needed some break, so I decided to do a small research on things that would be nice to learn. This way I've "lost" a reading about what is really happening the world of web applications. I've rediscovered a term RIA and diversity of frameworks for creating one page applications. As many times before questions and answers on stackoverflow helped me to straighten up issues and give a good starting point. Let me just name most important tools for creating rich applications. I'll try back to this topic and do a small review on each of frameworks listed below, but for now I'll start with something simpler. If you see any mistakes let me know. Rich user interfaces based on the most popular open source JavaScript frameworks: jQuery UI YUI Library MochaUI Dijit and DojoX Frameworks based on JavaScript, more complex, tconcentrated only on RIA Ext JS 4 Bindows qooxdoo More advanced solutions based not strictly on JavaScript: Cappuccino SproutCore Frameworks giving a foundation for creating applications rather than a set of widgets: JavaScriptMVC Knockout JS Backbone Based on JAVA: ZK Vaadin OpenFaces SmartClient Echo3 If you're looking for small handy tools for some JS job? Try at microjs. When I saw all of this I've realized that my basic skills at JavaScript are much too low to go further then applications based on jQuery User Interface which I already know. Then I've found probably the best on line tutorial ever, it's available at http://javascript.info/. After a few weeks of learning I've not only organized my knowledge about JS, but also filled knowledge gaps and learned many new thing. What's interesting from day to day writhing scripts in jQuery became much easier. My advise - if you know some of JavaScript frameworks, take a step back and learn at least solid foundations of pure JavaScript. Beside things mentioned before I've started reading The Algorithm Design Manual by Steven Skiena. Definitly it's an interesting book, but I'm sure that wading through this book will take a several months. At the end a list of most interesting articles, libraries and presentations I've read and seen lately: First 3 links lead to Andy Osmani's works. He really showed me a new level of building applications using jQuery. Building Large-Scale jQuery Applications Building Single Page Applications With jQuery's Best Friends Tools For jQuery Application Architecture (Extended Slides) Nice library build on a top of jQuery UI. Partly free, can by used under MIT or GPL license. WIJMO If you're looking for a good resource about HTML5 take a look at: DIVE INTO HTML5 Great collection of JavaScript libraries and jQuery plug-ins: Useful JavaScript and jQuery Tools, Libraries, Plugins Last link is a nice presentation of what can be done in a JavaScript: Gravitational Particle System Sandbox

Difference between JOIN with WHERE condition and JOIN with subquery with WHERE condition

2011-04-06T21:16:51+02:00

As example I'll use sakila sample database. Our objective is to get a list of movies with the number of actors which first or last name starts with letter a. First lets check how many movies that meet that condition is there. SELECT count( DISTINCT film_id) FROM film LEFT JOIN film_actor using (film_id) LEFT JOIN actor using (actor_id) WHERE first_name like 'a%' OR last_name LIKE 'a%' Now we know that in 407 films appeared the definite actor. So now lets list them: SELECT film_id, title, count( actor_id ) FROM film LEFT JOIN film_actor using (film_id) LEFT JOIN actor using (actor_id) WHERE first_name like 'a%' OR last_name LIKE 'a%' GROUP BY film_id ORDER BY 1 ASC The result looks like this: All right, we got list of all movies that has at least one specific actor, but what if we want list of all movies with the number of actors even if the number is 0? We have to modify our query. SELECT film_id, title, IFNULL(count, 0) FROM film LEFT JOIN ( SELECT film_id, count( actor_id) as count FROM film_actor LEFT JOIN actor using (actor_id) WHERE first_name like 'a%' OR last_name LIKE 'a%' GROUP BY film_id) as act using (film_id) ORDER BY 1 ASC Now that's more like it: The the difference is clear but how does it work? Lets split this statement into two separate queries: first one is simply a list of films with no conditions SELECT film_id, title FROM film ORDER BY 1 ASC Probably you could imagine the result yourself: second is quite similar to the query that gave as a result list of 407 movies with specific actors: SELECT film_id, count( actor_id ) as count FROM film_actor LEFT JOIN actor using (actor_id) WHERE first_name like 'a%' OR last_name LIKE 'a%' GROUP BY film_id Adding a title we could get a result identical to the result of first query: How many rows do we get as a result of this query? Any guess? Lets check. SELECT count( distinct film_id) FROM film_actor LEFT JOIN actor using (actor_id) WHERE first_name like 'a%' OR last_name LIKE 'a%' 407 rows. I've to say I was suprised by the result. So basically what we are doing in the correct query is a selecting the list of all film, and than joining to each of those films (using a film_id column) a number of actors. What if there is no actors' number? We are using LEFT JOIN, so if it is empty we are attaching null value. If we were using INNER JOIN we wouldn't get this row at all. On the image below you can see what will be joined to each titles. First query was creating a list of titles with actors' number and that filtering result using WHERE clause, removing all rows that don't meet the conditions (that is those who had no specific actors - 0 as a number). The most important thing in this axample was a changing a scope of WHERE condition from "global" - affecting all 3 joined tables into "local" - filtering only this part of result that will be later attached into outer query result

MySQL EXPLAIN step by step - Part 1

2011-04-02T17:16:37+02:00

All demonstration statements were run on Sakila sample database which can be downloaded from here. Lets start with a first, very simple query: SELECT * FROM `sakila`.`film`; As a result we get all rows and columns from film table. Now let see what we get when we precede previous query with a EXPLAIN word. Query: EXPLAIN SELECT * FROM `sakila`.`film`; Result should be similar to this: One row and five non null columns: id, select_type, table, type, rows. Some of those columns, like id, table, rows, are self-explanatory (it doesn't mean that they always be, but right now they are), some look much more cryptic, so lets look closer to them. id is simply a number that identifies query - we got one statement so it's marked as 1. select_type shows the kind of SELECT we are dealing with. Basically we can distinguish between simple and complex statements. At this point this columns doesn't give much information because it's clear that query was SIMPLE. Other possible types will be discussed later. table we can see that film table was used in this query. Quite obvious. type according to the MySQL manual this columns show a join type. You may find this a bit strange considering lack of any joins and other tables. We should look at this table wider that a join type. Please look on the quote below from High Performance MySQL. It's more accurate to say the access type - in other words, how MySQL has decided to find rows in the table.High Performance MySQL So how MySQL find ALL rows? It have to do a full table scan, read each row in table. On manual we can find it information that the ALL value is the worst of all. Why? Because it means a server has a really much to do, but in query like this, when we want to find all rows in table there is no other option. rows column shows as a value an estimated number of rows that server must read to find what we want. It's quite interesting that this number can be wrong, it's just an estimation made by MySQL. Actually film table has a 1000 rows... Lets make a brief summary what information we get from EXPLAIN statement: we know that we made a one simple SELECT and that to get the result MySQL had to read 953 rows from film table. Pretty nice for a few additional letters before source statement. OK, lets try something more complex. A query that gives as a result list of all films starting with letter "f". EXPLAIN SELECT * FROM `sakila`.`film` WHERE title LIKE 'f%' If everything went all right you should see something like on screen below. Probably you've already notices that this time there is only a one null column. Lets look at rest of them: id - just like a a last time, number identifying our query. select_type - again we have a SIMPLE SELECT. table - once again, we are selecting rows from film table. type - at last some new values - a range access type. What does it mean? Explaining our first query we get an ALL for a access type because there wasn't any conditions. This time we were looking for titles starting with letter f - a subset of table. It makes a perfect sense, doesn't it? (actually it's more complicated, but lets assume at this point that it's clear). possible_keys - in this column we can find a list of indexes which could be helpful during a query execution. MySQL shows that idx_title is a possible key and if we do a SHOW INDEX FROM film query we'll get to know that on title column there is really a index called idx_title. key - this column shows which of indexes was chosen to execute query as most efficient as possible. We've got only one candidate for this role and it was chosen. Certainly there are also other scenarios but at this point let stick by our example. key_len - a maximum length of key that could possibly used in query. I wrote "possibly used", not "used", because EXPLAIN statement doesn't measure values in rows, it simply looks only at table definition. rows - 46 rows had to be red to serve the result. Extra - it's an additional information about query execution. Looking at out query "Using where" shouldn't be surprise in this place, but honestly I've to admit why there is "Using where" instead of "Using index".Using where mean that result was filtered just before sending it to the client. Why at the stage of reading records? Defenitly have to read more about it. If you have any clues please leave comment below. Now it's time for a second summary. We've made one simple select statement from film table, which returned 46 rows, which used idx_title (that was only possible key and whose maximum length is 767) and range table access. So first EXPLAINs behind us. In next part I'll try to show and tell about a few more values that could appear when you're working just on a single table.

2011-03-28T18:58:38+02:00
First of all, this site is not done already. I've still got a lot to do, a bunch o ideas that will be implemented with the passing of time and beside that I've quite a few things to learn. I count on feedback from you on topics I could write about, on things that could be done better, on lacks of functionalities of the site and exceptionally on any mistakes I've made, and anything I don't understand correctly. I've tested this site under FireFox 4 and Chrome 10, any older versions won't be supported. Cutting a long story short - send e-mails (using contact form) and write comments below articles. As you already are on my site and probably it's not a coincidence (taking into consideration poor positioning on google) you are really determined to find some information about web programming. I hope you find what you're looking for and your next visit here will be more intentional.