MongoDB_Logo

SQL versus NoSQL Databases

Posted by & filed under Blog, Linux, Web Development.

LAMP is a great software stack to get started with in building a reliable and efficient web application, but there are many instances when a program’s complexity requires a different database technology than MySQL—or RDBMS in general, to handle information storage and retrieval. This is increasingly the case as firms find new ways to leverage the Internet and associated technologies in building novel applications that solve industry and domain-specific problems and challenges. For example, mapping and GIS-related applications require complex information storage, processing, and retrieval for geospacial analysis, using data models that are multi-level and more complicated in nature. Additionally, more sophisticated technologies are required to process the massive data explosion that has occurred within the last 5-10 years due to mobile devices, social media, and the exponential growth of the Internet —all of these contributing to the rise of Big Data, or data sets so massive that traditional RDBMS tools have difficulty processing them.

RDBMS technology is over four decades old, with the first commercial RDBMS released by RSI (later to become Oracle Corporation) back in 1979. The original developers of the RDBMS did not anticipate Big Data, and designed it to store all information into a neatly structured, tabular data model that made for streamlined storage and retrieval. This worked for a time, but in this day and age, the nature of data requires the scaling out of data, as opposed to scaling up. For example, instead of increasing the processing power, memory, and storage capacity of one database server, it makes more sense to purchase several inexpensive servers and distribute the load across them as required. This may sound familiar, as this is one of the main tenets of Cloud Computing. NoSQL fits into the cloud schema perfectly, as these two are both contemporary technologies: designed in the same era and solving the same problems with horizontal scaling.

Comparing RDBMSs and NoSQL Databases

It’s worth noting that NoSQL stands for “Not Only SQL,” not “No SQL.” That is to say, SQL—or Structured Query Language, is not entirely absent from NoSQL databases. SQL’s presence varies from vendor to vendor. Indeed, some NoSQL solutions allow the use of SQL-like query languages to manipulate data, with MongoDB as the prime example of this. Rather, the main differences between the two database technologies are related to how NoSQL solves scaling issues inherent with a RDBMS.

We mentioned before that RDBMSs require a fixed, predetermined, tabular data model to perform optimally. NoSQL databases are the opposite of this—they are schema-less, and thus more flexible. Data can be inserted without conforming to a specific structure; the format can thus be changed without modifying or rewriting the application. NoSQL databases store data as key-value pairs, columnar data, or documents—rather than a relational set of tables (and constituent rows/columns).

Different Database Chemistry: ACID vs. BASE

When comparing a RDBMS and a NoSQL database, one may encounter the terms ACID and BASE, and the relative benefits of each. ACID stands for “atomicity, consistency, isolation and durability,” and refers to the standard attributes of a RDBMS, whereas BASE stands for “basic availability, soft-state, eventual consistency.” The attributes detailed in ACID were initially stated as being imperative to optimal database operations, but as systems become larger and more distributed, this is less of a reality—especially with the immense volumes of data and simultaneous transactions that are typical of today’s large systems (think Amazon or Google search). BASE, therefore, describes the attributes of NoSQL databases: data eventually will be in sync, and this is good enough—it may be approximate and not exactly up-to-date, but at least the system is highly-available, performing and responding to requests.

In short, due to their simpler data models and distributed nature, NoSQL databases are generally faster and more scalable than RDBMSs. That being said—NoSQL is still a developing technology, while RDBMSs are mature, stable, and reliable under most circumstances. Realistically, most organizations and firms will not scale out of the realm of a standard RDBMS’ performance threshold. It is therefore not a question of which database technology is superior to the other, but rather, which is appropriate for the scenario at hand. RDBSs such as MySQL, PostgreSQL, Oracle, and SQL Server are still the most widely supported and understood, and will hold up in the vast majority of use cases. NoSQL databases such as MongoDB, CouchDB, BigTable and Apache Cassandra should be used when highly-available, distributed database systems are necessary to handle massive volumes of requests and data processing.

php

Popular PHP MVC Frameworks

Posted by & filed under Blog, PHP, Web Development.

As PHP has evolved into the lingua franca of web application development, so has its sophistication in terms of development methodologies and platforms. So it’s not surprising that the preferred approach in implementing software with PHP these days is to follow a model-view-controller (MVC) development framework. While this design pattern has been a popular in architecting desktop software for some time now, the relatively recent coupling of PHP with MVC has given rise to a myriad of frameworks that address the varying needs and preferences within the PHP developer community. Though many different implementation of MVC for PHP exist, they all share the core benefits of MVC: the separation of application logic from presentation, making application code cleaner and better structured. Furthermore, MVC makes software maintenance easierespecially in the case of multi-developer environments.

The following are three popular PHP MVC frameworks and their attributes, strengths, and application scenarios.

CodeIgniter

Known for its speed, small footprint, and mild learning curve, CodeIgniter is a favorite framework of developers just getting up-to-speed with MVC. It is easy to configure and relatively free of strict requirements and dependenciesfor this reason, developers working in shared hosting environments like using CodeIgniter. Its elegance and simplicity, however, is seen by more advanced PHP developers as its limitation: sparse documentation, lack of libraries/modules, and no ORM support make it too simplistic for business or enterprise-level PHP application development. That being said, it is a godsend for developers learning the ropes of MVC frameworks and object-oriented PHP, and allows one to build a fast, well-structured PHP application in a short amount of time.

Despite its popularity, at the time of this writing CodeIgniter is in the midst of a transition. EllisLabs, the creators of the framework, is in the process of transitioning CodeIgniter out of its domain. Until it finds a new home, the future of this popular framework is uncertain.

Yii

Yii stands for “Yes it is,” which describes the framework’s flexibility in terms of features and applications in various use cases. Yii is known for its exceptional performance, speed, and securityindeed, many developers have adopted this framework for building complex business applications and high performance/high availability web sites. It has a large developer community and subsequently a large library of 3rd party extensions and plugins for rapid application development. A particular feature of Yii that is held in high-regard by developers is the extensive use of wizards for automating application development and code creation. These wizards automate tasks such as creating interfaces and building data structures through easy-to-use modules that can be run either through the command line or through a web interface.

The tradeoff for these attributes is a steeper learning curve, especially when compared to more rudimentary frameworks such as CodeIgniter. Yii is considered by many to be one of the harder PHP frameworks to learn, despite great documentation and a large developer community. And despite its robustness, Yii has still some ways to go before large businesses and enterprises adopt it as the PHP MVC platform of choice. That being said, Yii is built to work easily with other frameworks such as Zendallowing for cross-platform integration and code use.

Zend 2

Arguably the most robust PHP MVC framework, Zend was largely responsible for pushing PHP into the enterprise realm as a serious technology for business-level application deployment. Zend 2 developers have at their disposal an amazing array of components and libraries for building feature rich, complex web applications. It is well documented and has a large, devoted developer community contributing to the framework’s livelihood and ecosystem. Indeed, many of the Fortune 500 have embraced Zend 2 for building their mission-critical applicationsit’s safe to say that it stand strong long after many of the other PHP MVC frameworks have fallen to the wayside. Zend 2 is stable, widely-used, and worth learning if one’s desire is to build rock-solid enterprise applications.

Zend 2 can be problematic when deployed in hosted environments, as configuration requirements are numerous. Most Zend 2 developers choose to select a web host that supports the platform natively, thereby mitigating this issue. A steep learning curve is also inherent of Zend 2, and PHP MVC novices may find picking up this framework a daunting experience. Of particular difficulty to beginners is acquiring a grasp of Zend’s strict set of rules, guidelines, and naming conventions. This proves to be a huge development benefit down the road, however because of these constraints, maintenance of a PHP application built with Zend 2 is far easier, as applications are easier to understand within a Zend 2 construct.

In short, a wide variety of PHP MVC platforms exist; these three invariable top the list, though the order depends largely on application use-case and developer skill set. As the saying goes, each tool suits a different task uniquely. Building a simple web site or application with Zend 2 would most likely be overkillsimilarly, using CodeIgniter to build mission-critical enterprise software would also be a mistake.

Foundation 5

Mobile Web Design: Why Mobile-First, Responsive Web Design with Foundation is Key

Posted by & filed under Blog, How-to, Responsive, Web Development.

Mobile First is King

Saying you can’t ignore mobile is like saying you can’t ignore your customers. With 53% of all Americans accessing the internet through mobile, and 31% of them as mobile-only, the better phrasing would be, “You still can’t ignore desktops.”

That being said, the best approach to web design today is a responsive site or app that adapts itself to fit the device it’s viewed on, in a mobile-first development approach. Zurb’s Foundation framework, arguably the most advanced front-end responsive framework out there, serves that up in buckets.

1. Responsive is Best

Responsive web development, simply put, means your site re-sizes to fit whatever device it’s viewed on. Desktop, tablet, phone and what’s next? Maybe smartwatch?

This beats developing a separate website for each platform for a shopping bag of reasons, including:

  • SEO. Search engines favor the single-site approach. Serve up a different site depending on the platform, and your search result position suffers. Google even recommends responsive web design, because a single URL and site regardless of device is easier to crawl, index, share, interact with and link.
  • Cost. It’s easier, faster, and therefore, cheaper to build and maintain a single, responsive site than several. Let’s say you want to change a product description. It’s better to do that once, than once for desktop, once for tablet and again for smartphone, isn’t it?
  • Forward Thinking. We can’t know what new screen shapes and sizes will show up next month. Playing whack-a-mole with new designs for every new configuration makes no sense. Better if your site can change itself to fit whatever container it’s poured into. To quote Bruce Lee, “Be like water.”
  • Reach a Wider Audience. Adaptability means you reach more people, and those people have a bigger chance of coming back to you more often.

2. Mobile-First is the Present and Future of Mobile

A recent Forbes article says e-Commerce is growing at a rate of over 16% per year, while mobile grows at over 68%. That makes mobile a fast-growing slice of an ever-larger pie.Why go mobile-first? Let’s look at how responsive web design was traditionally handled. You made your site for PC, then stripped it down for tablet and then stripped down again for mobile. That was known as “graceful degradation,” a fancy term for showing less and less as screen size shrunk.

The problem was, although you’re showing less, you’re delivering the same. In other words, with PC first, your responsive mobile site still had to download every bit and byte of data on the site, and then select the items to leave out.

Why’s that bad? Because mobile is traditionally slower. So you’re asking your slowest, smallest platform to download more than it needs before displaying it. And that loses customers. Because 75% of web users will abandon a site that takes too long to load. Looked at another way, it’s like going to a slower restaurant, then asking them to cook up one of every item on the menu, and then, when they bring it all out, just eating the burger.

Second, mobile-first forces us to focus on our core content and functionality. That means we zero in on presenting the most important aspects of our business first, and let customers drill down to suit their needs from there. Are they reaching us on a PC? Well great. We’ll show them more detail up front. But why lead off by packing your message with an over-busy Christmas tree of potentially confusing data? If someone walks in looking for the most powerful sports car on the market, you don’t start with a 10-minute comparison of all the different cup holders, floor mats and the like. Mobile-first respects and even forces this direct, get-to-the-point approach.

3. Zurb’s Foundation Masters Mobile-First

Mobile-first was historically difficult and packed with headaches, which made it complicated and expensive, time-consuming and slow to implement and update. Until the Foundation framework came along.

Foundation has been called the most advanced responsive front-end framework in the world. It makes mobile-first both fast and easy, which is why millions of developers are using it worldwide. It simplifies the construction of adaptable sites. It offers better performance and customizability for mobile users. And that means cheaper, faster, higher quality mobile-first responsive sites.

Further, Zend Framework 2 (renowned as a modular, secure, extensible and high-performing framework) dovetails with Foundation nicely to add extra value to the picture.

4. The Bottom Line

With a mobile-first, responsive web design, built on Foundation and Zend Framework 2, the website you want can be created faster, less expensively and to a higher quality, ultimately reaching more customers (and potential customers) more often than at any time in the past. That’s why Otreva uses Foundation and Zend to give our customers the powerful, functional site they want, fast, at lowest cost, and in a way that’s both inexpensive and easy to update, which makes keeping current with the swiftly-changing face of tech a breeze.

flat-design

2014 Trends in Web Application Development

Posted by & filed under Blog, Web Development.

Web application development is ever-changing. As technology evolves, so do the alternatives we have to enhance a user’s experience. There are a number of trends that you’ll see in the coming months. These five are among the most likely to maintain momentum over the coming year.

1.  Different Types and Sizes of Fonts

In the past, developers were limited to web-safe fonts. With web-safe fonts, anyone looking at an application would see the lettering as the developers intended.

With the increasing popularity and ease of embedding fonts into the application itself, developers are now free to use a variety of fonts. Designers can be more creative and a website can communicate with attitude.

Until fairly recently, developers, probably influenced by non-web graphic designers, seemed to think that using a small font made a website more prestigious than in-your-face. What everyone is now realizing is that if a font is so small that it’s difficult to read, people will simply move on rather than straining their eyes. This is especially true of mobile device users.

2.  Flat Web Design

Maybe it’s because we’re all becoming more comfortable with computer usage, but the look of web applications is changing. It used to be that people wanted text with shadows, 3-D effects and elaborate fonts and design elements.

The move to “flat” design is being led by Gmail, Facebook and Windows 8. The look is cleaner, the lines are bolder and the colors richer. The design is intended to help users scan a page and quickly find what they need without the interruption of design elements getting in the way.

3.  Responsive Design

Responsive web design will eventually stop being a trend and become implied in web design. At some point, we’ll stop talking about it as a separate issue.

With increased use of mobile devices of all types, an application needs to restructure itself automatically to conform to the requirements of any device regardless of size. Responsive means the application won’t just appear smaller — it needs to be able to transform itself to actually make mobile use easy.

4.  Blurred Content

Blurred content is becoming more prevalent because the resources used to create that effect are being reduced as the technology evolves.

Today, applications have more and more capability. The difficulty is making sure that the user can move between functions without becoming overwhelmed.

Blurring some layers of an application to focus attention on a related function is proving to be an effective way of providing a wide range of capability while keeping the hierarchy of functions obvious to the user.

5.  Parallax Scrolling

Parallax scrolling can be useful in design at times when you want to tell a story or add depth and movement to images. It brings a definite “wow” factor to an application. And, it helps keep visitors on a web page longer because they’re curious and it’s a novelty.

One of the pitfalls you can encounter if you build a parallax site is that the interface can get too complicated for the user to comprehend. Page load times can be so long that a good number of visitors get frustrated and leave the page before they know what they’re waiting for. It’s also more of a challenge to use when designing for responsive applications.

For an example of an excellent application of parallax scrolling, visit this Google Doodle that was made to celebrate the 112th anniversary of John Steinbeck’s birth. When you click on each image representing one of Steinbeck’s books, you’ll be taken to quotes from the book. But you won’t lose continuity of the overall story because the next book will pop up when the previous book’s story has been told.

what-google-wants-in-2014

4 Things Search Engines (Google) Want in 2014

Posted by & filed under Blog, How-to, Web Development.

Regardless of how much Google tries to discourage apparent search engine optimization (SEO) tactics, it’s still more important than ever to assure your website receives traffic.

Say goodbye to continuous linking and keyword stuffing. Instead, it’s important for websites and content creators to focus on a different set of rules to earn Google’s trust. People are using Google for many queries, and Google uses context clues to provide accurate answers.

What do you need to know for SEO success now? Here are 4 crucial SEO tactics for 2014:

1.  Answering questions in blog posts and website content. Users have switched from searching specific words to searching questions and phrases. One SEO tactic that is hot in 2014 is asking a question as the title, and responding in the page body.

Natural language queries indicate that people use Google as if they’re talking to a friend rather than a computer. Google wants to encourage this method, using phrases and questions as indications of what people are looking for in context.

2. Building Social Media networks and credibility is crucial. Whether your brand is on Facebook, Twitter, Instagram, Pinterest, LinkedIn or Google Plus, social media continues to be important. Building a large, engaged audience increases social shares of your content, bringing traffic alongside. Social also increases brand awareness both for consumers and websites alike.

Further, Google and search engines are taking social signals into consideration for rankings. Websites with large numbers of fans and social shares will get ahead in rankings versus websites who ignore social.

Another social media site to stop ignoring is Google Plus. Website content creators should set up Google Authorship in order to increase SEO rankings, and Google plus profile engagement will play a role. Google prefers verified authors, and author photos next to web results increases the click-through rate.

3. Mobile SEO is crucial for websites. Mobile websites are expected to overtake PCs for searches by 2015, according to Search Engine Land. Updating your website to be responsive is the only one step of the process.

Paid search provides differentiation between mobile and PC searches, so you can create different campaigns for each method. Considering users on mobile searches tend to complete an action within one hour, brands should rethink their mobile strategy to format easy calls to action.

4.  Creating high quality content is more important than ever. Google upgrades its search engine algorithms to simply accomplish one task: separate the spam websites from real, valuable content.

Creating valuable content that readers enjoy and share on their social networks is the most important task for websites. Sprinkling important keywords and related strategically throughout the title, header tags and text reads more naturally for readers and indicates the importance to search engines.

Plus, high quality content is more shareable on social media, reinforcing more engagement and comments from readers.

Moral of the SEO Story.

Google rankings and SEO tactics for 2014 continue to be more and more intertwined. The more resourceful your website is, the more likely users are to share content. The more shared content, the more signals back to Google that your website is valuable, increasing your Google rankings. Paying for cheap content or replicating another website will no longer be acceptable shortcuts.

mysql_part_of_lamp

PHP / LAMP Development Best Practices

Posted by & filed under Apache, Blog, Linux, MySQL, PHP, Web Development.

Open source has its advantages as well as challenges free more often than not means you are the master of your own destiny, technologically-speaking. Be that as it may, solutions are often within reach and well-documented by the developer community. The following are some guidelines, methods and techniques to develop a sound web application deployment with PHP/LAMP.

Security

Though volumes exist on hardening your LAMP stack for security on all levels, the lowest common denominator is staying in-line with the latest updates. This will result in better performance, the newest features, and security updates out-of-the-gate. Installing/configuring a firewall and hardening your linux operating system may require the assistance of a more experienced administrator, but will prove a worthy endeavor in the long run. Apache, PHP, and MySQL configuration directives can be tweaked with relative ease, and should be modified accordingly. If your web application will be transmitting sensitive data, installing SSL and acquiring a proper certificate is a must.

Environments and Version Control

Implementations vary by organization, but the general concept is that for release management purposes, your web application should move upstream to different environments. For example, an organization’s web environment may consist of development, staging, and production serverswhere initial coding occurs in development, testing/review occurs in staging, and live hosting occurs in production. The development environment provides a safe area where you can initially develop without worrying about code or application behavior disrupting other shared processes. Many developers use their own local machines effectively for development. Staging environments typically mimic the production environment in terms of configuration, and provide an area for review and testinga last embarkation point, so to speak, before application code goes into production mode. Production environments are “live” this is where customers and users interact with your application. Word (e.g. coding) should never be done in production. Typically load balancing and other application server-level optimization occurs in the production environment.

Version control methods also vary, but the general idea is that changes to a code base should be tracked and managed, in the event that rollback is necessary. Some popular version control programs used with LAMP are Git and CVS with Git being our go to.

PHP Frameworks

You should employ an Model-View-Controller framework in building your PHP application. The Model-View-Controller (MVC) framework has in recent years become the de-facto standard for designing web applications, and with PHP, there is no shortage of frameworks to choose from. Web applications written with the MVC framework can be upgraded and maintained easily. For example, developers can implement a new user interface without rewriting the whole application. Also, because their work is contained in the view, there is no interference with other portions of the software contained in the model or controller. The database developer can rest assured that the aforementioned work on the user interface (the view) will not affect his or her work on the database (the model). This is the essential benefit of MVC, and why it is a standard practice in software development today. MVC essentially provides greater structure and organization to a program, and should be used when developing PHP web applications.

PHP MVC frameworks vary greatly in terms of learning curve, power and breadth of features, and speed/performance. Ultimately, the choice comes down to a particular organization’s needs. For example, CodeIgniter and Yii are relatively easy to learn, fast with a minimal footprint, and well-supported with a sizable developer following. On the other end of the spectrum is Zend Framework 2– arguably the probably the most powerful PHP framework and an associated steep learning curve, as it is Java-like in nature. Zend Framework 2 is the choice framework for building corporate and enterprise PHP software applications. It is also our go to here at Otreva for these reasons.

wordpress

Why are so many sites developed using WordPress?

Posted by & filed under Blog, Web Development.

According to WordPress founder Matt Mullenweg, 18.9 percent of the web is using WordPress. Of that nearly 19 percent, more than half (66 percent) of these websites and blogs are in English.

In May 2013, WordPress celebrated its tenth anniversary. Since then, 69 million sites have been built using the platform.

Why are so many new sites being developed using WordPress?

Ease of use

Because WordPress has made it possible for the average computer user to create, manage and edit their website. (*NOTE: I do not recommend the average computer user to design their own website because you are not an expert on the software, and one you’ll most likely just be guessing what to do.)

The crux of WordPress is its posts: where you write blog articles; pages: where you create or edit your website’s pages, such as your homepage and about page; and plugins: how you customize WordPress and add additional functionality to your website.

Other reasons?

  • It’s free
  • Open-sourced
  • Hosting companies, such as GoDaddy, make it easy to install
  • Search Engine Optimization friendly
  • It’s mobile friendly and responsive

If you can use Microsoft Word, you can learn how to use WordPress.

What should I use WordPress for?

WordPress is best used for small business websites and online newsrooms, otherwise known as blogs, such as Mashable. Here are some great reasons to use WordPress.

Small Business Website Development

  • Easy to Update – WordPress was originally developed as a blog platform so the creators were focused on making it very easy to use.  You don’t need specialized knowledge or skills on how to code a website.
  • Help is easily accessible – Since WordPress is so popular and free, loads of information are available on the internet to help you get started and to learn advance uses of the platform.
  • Easily Scalable – WordPress is there to accommodate your needs as your business grows. Many plug-ins are available to help you add functions to your site. For example, if you want to add sign-up forms, do a survey, sell products and accept payment through PayPal, there will be plug-ins available to accomplish these tasks.
  • Social Media – WordPress will easily allow you to share your website via social media platforms such as Facebook, Twitter, LinkedIn and Pinterest. Social networking sites are very effective to increase web traffic and it only takes seconds to add them to your site.

CMS

  • Organization – WordPress is very useful to manage and organize your content.  Categories can be added in a hierarchical structure making it very simple to modify and add new content.
  • Users -  If you have users contributing to your content, it’s very simple to add them and set security and role levels.
  • Plug-Ins – Plug-ins are necessary if you want a dynamic and functional Content Management System.  Browse through and experiment with the available plug-ins by using the drag and drop features.

Personal Blog

  • Setup - You can setup your own personal blog in under 20 minutes!  No technical skills are required and numerous tutorials are available to get you started.
  • Dashboard – Your blog will come with a dashboard that you can customize to reflect your blog title and tagline.  It’s very easy to do and gives you the opportunity to personalize your presence.
  • Categories and Tags -  Your blogs can be organized and presented by assigning tags and categories to your posts.  This will allow users to more readily find and read your content.

Conclusion

As you can see, WordPress is very flexible in how it can be used.  It’s a great DIY platform for small business owners and writers who have little to no web development skills however we caution users for using it for everything.

mysql-logo

Get Value from Delimited Array in MySQL

Posted by & filed under How-to, MySQL, Web Development.

Problem Getting Values From Delimited Array in MySQL

MySQL does not have an array type, and if you need to implement arrays, a delimted string is a simple way. If you are using strings to pass a delimited array in MySQL, you have run into the problem of getting a value out at a index. The equivalent in a programming language such as Java would be:

array[1]

. MySQL has something close with SUBSTRING_INDEX. It can get a substring up to the nth location of a string. Example:

SELECT SUBSTRING_INDEX('my|delimited|string', '|', 2);
my|delimited

As you can see this will get the subtring up to the second occurrence of ‘|’. If you wanted to get the second value in this delimited array, you could do the following:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('my|delimited|string', '|', 2), '|', -1);
delimited

This is a lot of code to do a simple operation and can quickly clutter up your queries.

Solution

I buried this code into the following function:

CREATE FUNCTION getValueFromArray(
_array TEXT,
_del VARCHAR(255),
_value INT UNSIGNED
)
RETURNS VARCHAR(255)
NO SQL
DETERMINISTIC
SQL SECURITY DEFINER
BEGIN

IF _array IS NULL || _array = '' || _del IS NULL || _del = '' || _value IS NULL || _value > stringOccurrences(_array, _del) THEN
    RETURN '';
ELSE
    RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(_array, _del, _value + 1), _del, -1);
END IF;

END~

This function will handle null and empty strings (empty arrays). To complete the example above, the following query can be made:

SELECT getValueFromArray('my|delimited|string', '|', 1);
delimited

Hopefully this will help clear up queries and have better logical flow.

mysql-logo

Get MySQL Table Disk Usage with Stored Procedure

Posted by & filed under Blog, How-to.

Table Data Usage

MySQL stores the amount of disk space it uses per table in the information_schema database. Getting the information out is a simple SQL statement, but it is stored in bytes, so if you want to see MB, mB, GB, etc it is a different query. To make this a simpler task, I wrote the stored procedure below. It gives the option to the user which data unit size they will see.

DELIMITER ~
DROP PROCEDURE IF EXISTS ADMIN_getDiskUsage~
CREATE PROCEDURE ADMIN_getDiskUsage(
_size ENUM('k', 'm', 'g', 'kk', 'mm', 'gg')
)
READS SQL DATA
SQL SECURITY DEFINER
BEGIN

DECLARE sizeDivisor DOUBLE DEFAULT 1.0;
DECLARE totalSize_data INT UNSIGNED DEFAULT 1;
DECLARE totalSize_indexes INT UNSIGNED DEFAULT 1;

SELECT SUM(data_length), SUM(index_length) INTO totalSize_data, totalSize_indexes FROM information_schema.tables WHERE table_schema = DATABASE();

CASE IFNULL(_size, '')
    WHEN 'k' THEN SET sizeDivisor = 1000;
    WHEN 'm' THEN SET sizeDivisor = 1000000;
    WHEN 'g' THEN SET sizeDivisor = 1000000000;
    WHEN 'kk' THEN SET sizeDivisor = 1024;
    WHEN 'mm' THEN SET sizeDivisor = 1048576;
    WHEN 'gg' THEN SET sizeDivisor = 1073741824;
    ELSE SET sizeDivisor = 1.0;
END CASE;

SELECT CAST(totalSize_data/sizeDivisor AS DECIMAL(20,2)) AS total_size_data, CAST(totalSize_indexes/sizeDivisor AS DECIMAL(20,2)) AS total_size_indexes;

SELECT table_name, CAST(data_length/sizeDivisor AS DECIMAL(20,2)) AS size_data, CAST(index_length/sizeDivisor AS DECIMAL(20,2)) AS size_indexes, CAST((data_length/totalSize_data) * 100 AS DECIMAL(20,2)) AS size_data_percentOfTotal, CAST((index_length/totalSize_indexes) * 100 AS DECIMAL(20,2)) AS size_indexes_percentOfTotal
    FROM information_schema.tables
        WHERE table_schema = DATABASE()
        ORDER BY data_length DESC
;

COMMIT;END~
DELIMITER ;
COMMIT;

Example calls

CALL ADMIN_getDiskUsage('k'); -- kilobytes (1000 bytes)
CALL ADMIN_getDiskUsage('kk'); -- kilobytes (1024 bytes)
CALL ADMIN_getDiskUsage('gg'); -- gigabytes (1024 megabytes)
responsive web development company

Should I Go With Responsive Design Vs. Mobile Site

Posted by & filed under Blog, Responsive.

Internet access is no longer confined to just desktops or laptops. According to Search Engine Land, more than 95% of people access the web from a mobile device. Although mobile devices make accessing the web much more convenient, sites that aren’t optimized for mobile viewing are often unresponsive on these devices. The font displays too small, some images don’t load and the screen has to be manipulated just fit the device. This causes users to click off of the page, which in turn increases the bounce rate. To avoid these issues, your website has to be optimized for mobile viewing. One way to do this is to use responsive design, another is to use a mobile site.

Responsive Web Design

A responsive website automatically responds to the browser that’s accessing it. When a person with a mobile device clicks on your website, the website displays perfectly on the device and all of the buttons respond to touch.

With  responsive websites, there’s no need to redirect users since the server registers the device type before the page even loads. In many cases, the viewer will be given the option of remaining on the mobile-friendly site or navigating to the full computer version.

There are a few significant differences between the responsive site and the regular site that you should know.

  • Responsive websites display a completely different format from the full site. The CTA buttons are much larger and are usually at the top or right of the page.
  • When you click into forms to enter numerical information, the number pad will appear rather than the entire keyboard.
  • The screen size automatically scales when the device rotates with a fluid layout.
  • Content is sometimes hidden on responsive sites to account for the screen size and user experience however good responsive sites shouldn’t remove / hide content but rather tailor it to the screen.

If you use mobile devices to access the web, you’ve probably seen a few responsive websites. As you notice, they are radically different from how the website would look on your PC, and it almost seems reminiscent of an app. No skeuomorphic design, just flat touch-friendly buttons and minimal images. Responsive websites use one code for all platforms and provide more options for user experience than regular sites.

Mobile Site

Most people think that responsive websites are the same thing as mobile sites, but there are some slight differences. For one, a mobile website works from a completely different code and URL (in most cases). When a person navigates to a mobile site, the server hosting the site, recognizes the user’s device and scales the site to fit the screen. People with already established websites may not want to re-code the page to be responsive, so they choose to adopt a mobile site instead. This gives them the freedom to create a user experience that works best for their current audience’s needs. Mobile sites use smaller files, so the pages may load quicker than a responsive site.

For the most part, mobile sites look identical to responsive sites, but the user experience is noticeably different. Mobile sites tend to focus on what the current audience wants to see, whereas responsive websites focus on giving the most important information to all users.

For the sake of optimization, Google recommends responsive websites over mobile sites. These sites rank better in SERPs and are more Google-friendly. On Google’s Developer page, they state that they fully support mobile websites that meet the following configurations:

  • Responsive websites (sites that use the same URLs on all devices), that serve the same HTML to all devices and using just CSS to change the page’s display.
  • Sites that dynamically serve all devices on the same set of URLs, with each URL serving a different HTML and CSS, depending on the user’s device.
  • Sites that have separate mobile and desktop URLs.

If a specific user experience is what you’re after, then a mobile site may be the way to go. However, if you’re looking to offer a more personalized approach to mobile web viewing then a responsive site is definitely a good bet. For SEO and SEM purposes, responsive websites offer more flexibility. So, if you’re looking to optimize your site, take Google’s advice and go responsive.