Top 10 best practices in MySQL
MySQL is the second most widely used open-source relational database management system in the world. It has become so popular because of its consistent fast performance, high reliability and ease of use. This article presents some of the best practices in MySQL.
1. Always use proper datatype
Use datatypes based on the nature of data. If you use irrelevant datatypes it may consume more space or may lead to errors.
Example: Using varchar (20) to store date time values instead of DATETIME datatype will lead to errors during date time-related calculations and there is also a possible case of storing invalid data.
2. Use CHAR (1) over VARCHAR(1)
If you string a single character, use CHAR(1) instead of VARCHAR(1) because VARCHAR(1) will take extra byte to store information
3. Use CHAR datatype to store only fixed length data
Example: Using char(1000) instead of varchar(1000) will consume more space if the length of data is less than 1000
4. Avoid using regional date formats
When you use DATETIME or DATE datatype always use YYYY-MM-DD date format or ISO date format that suits your SQL Engine. Other regional formats like DD-MM-YYY, MM-DD-YYYY will not be stored properly.
5. Index key columns
Make sure to index the columns which are used in JOIN clauses so that the query returns the result fast.
If you use UPDATE statement that involves more than one table make sure that all the columns which are used to join the tables are indexed
6. Do not use functions over indexed columns
Using functions over indexed columns defeats the purpose of the index. Suppose you want to get data where first two character of customer code is AK, do not write
SELECT columns FROM table WHERE left (customer_code,2)=’AK’
but rewrite it using
SELECT columns FROM table WHERE customer_code like ‘AK%’
which will make use of index which results in faster response time.
7. Use SELECT * only if needed
Do not just blindly use SELECT * in the code. If there are many columns in the table, all will get returned which will slow down the response time particularly if you send the result to a front-end application.
Explicitly type out the column names which are actually needed.
8. Use ORDER BY Clause only if needed
If you want to show the result in front-end application, let it ORDER the result set. Doing this in SQL may slow down the response time in the multi-user environment.
9. Choose proper Database Engine
If you develop an application that reads data more often than writing (ex: search engine), choose MyISAM storage engine.
If you develop an application that writes data more often than reading (ex: real-time bank transactions), choose INNODB storage engine.
Choosing wrong storage engine will affect the performance
10. Use EXISTS clause wherever needed
If you want to check the existence of data, do not use
If (SELECT count(*) from Table WHERE col=’some value’)>0
instead, use EXISTS clause
If EXISTS(SELECT * from Table WHERE col=’some value’)
which is faster in response time.
The post Top 10 best practices in MySQL appeared first on Big Data Made Simple - One source. Many perspectives..
How to run Linear regression in Python scikit-Learn
You know that linear regression is a popular technique and you might as well seen the mathematical equation of linear regression. But do you know how to implement a linear regression in Python?? If so don’t read this post because this post is all about implementing linear regression in Python. There are several ways in which you can do that, you can do linear regression using numpy, scipy, stats model and sckit learn. But in this post I am going to use scikit learn to perform linear regression.
Scikit-learn is a powerful Python module for machine learning. It contains function for regression, classification, clustering, model selection and dimensionality reduction. Today, I will explore the sklearn.linear_model module which contains “methods intended for regression in which the target value is expected to be a linear combination of the input variables”.
In this post, I will use Boston Housing data set, the data set contains information about the housing values in suburbs of Boston. This dataset was originally taken from the StatLib library which is maintained at Carnegie Mellon University and is now available on the UCI Machine Learning Repository. UCI machine learning repository contains many interesting data sets, I encourage you to go through it.
So come on lets have fun with linear regression,
Exploring Boston Housing Data Set
The first step is to import the required Python libraries into Ipython Notebook.
This data set is available in sklearn Python module, so I will access it using scikitlearn. I am going to import Boston data set into Ipython notebook and store it in a variable called boston.
The object boston is a dictionary, so you can explore the keys of this dictionary.
I am going to print the feature names of boston data set.
I will see the description of this data set to know more about it. In this data set I have 506 instances(rows) and 13 attributes or parameters(columns). The goal of this exercise is to predict the housing prices in boston region using the features given.
I am going to convert boston.data into a pandas data frame.
As you can see the column names are just numbers, so I am going to replace those numbers with the feature names.
boston.target contains the housing prices.
I am going to add these target prices to the bos data frame.
Scikit Learn
In this section I am going to fit a linear regression model and predict the Boston housing prices. I will use the least squares method as the way to estimate the coefficients.
Y = boston housing price(also called “target” data in Python)
and
X = all the other features (or independent variables)
First, I am going to import linear regression from sci-kit learn module. Then I am going to drop the price column as I want only the parameters as my X values. I am going to store linear regression object in a variable called lm.
If you want to look inside the linear regression object, you can do so by typing LinearRegression. and the press <tab> key. This will give a list of functions available inside linear regression object.
Important functions to keep in mind while fitting a linear regression model are:
lm.fit() -> fits a linear model
lm.predict() -> Predict Y using the linear model with estimated coefficients
lm.score() -> Returns the coefficient of determination (R^2). A measure of how well observed outcomes are replicated by the model, as the proportion of total variation of outcomes explained by the model.
You can also explore the functions inside lm object by pressing lm.<tab>
.coef_ gives the coefficients and .intercept_ gives the estimated intercepts.
Fitting a Linear Model
I am going to use all 13 parameters to fit a linear regression model. Two other parameters that you can pass to linear regression object are fit_intercept and normalize.
In [20]: lm.fit(X, bos.PRICE)
Out[20]: LinearRegression(copy_X=True, fit_intercept=True, normalize=False)
I am going to print the intercept and number of coefficients.
I then construct a data frame that contains features and estimated coefficients.
As you can see from the data frame that there is a high correlation between RM and prices. Lets plot a scatter plot between True housing prices and True RM.
As you can see that there is a positive correlation between RM and housing prices.
Predicting Prices
I am going to calculate the predicted prices (Y^i) using lm.predict. Then I display the first 5 housing prices. These are my predicted housing prices.
Then I plot a scatter plot to compare true prices and the predicted prices.
You can notice that there is some error in the prediction as the housing prices increase.
Lets calculate the mean squared error.
The mean squared error has increased. So this shows that a single feature is not a good predictor of housing prices.
Training and validation data sets
In practice you wont implement linear regression on the entire data set, you will have to split the data sets into training and test data sets. So that you train your model on training data and see how well it performed on test data.
How not to do train-test split:
You can create training and test data sets manually, but this is not the right way to do, because you may be training your model on less expensive houses and testing on expensive houses.
How to do train-test split:
You have to divide your data sets randomly. Scikit learn provides a function called train_test_split to do this.
I am going to build a linear regression model using my train-test data sets.
Then I calculate the mean squared error for training and test data.
Input:
print “Fit a model X_train, and calculate MSE with Y_train:”, np.mean((Y_train – lm.predict(X_train)) ** 2)
print “Fit a model X_train, and calculate MSE with X_test, Y_test:”, np.mean((Y_test – lm.predict(X_test)) ** 2)
Output:
Fit a model X_train, and calculate MSE with Y_train: 19.5467584735 Fit a model X_train, and calculate MSE with X_test, Y_test: 28.5413672756
Residual Plots
Residual plots are a good way to visualize the errors in your data. If you have done a good job then your data should be randomly scattered around line zero. If you see structure in your data, that means your model is not capturing some thing. Maye be there is a interaction between 2 variables that you are not considering, or may be you are measuring time dependent data. If you get some structure in your data, you should go back to your model and check whether you are doing a good job with your parameters.
Conclusion
To recap what I have done till now,
- I explored the boston data set and then renamed its column names.
- I explored the boston data set using .DESCR, my goal was to predict the housing prices using the given features.
- I used Scikit learn to fit linear regression to the entire data set and calculated the mean squared error.
- I made a train-test split and calculated the mean squared error for my training data and test data.
- I then plotted the residuals for my training and test datasets.
The post How to run Linear regression in Python scikit-Learn appeared first on Big Data Made Simple - One source. Many perspectives..
How do algorithms influence teaching and bridge the students’ knowledge gap
The essence of education traditionally has involved the transition of accumulated knowledge to younger generations for most of its history. However, as the working routine changes from repetitive work to the knowledge-based activity, the requirements put to the quality of education have changed. So, how are big data and the resulting algorithms influencing teaching and helping students learn?
It can analyze how they’re doing
Because the data sets of student learning are so diverse, very slight nuances about how students are doing can be teased out. In this way, Arizona State is analyzing the keystrokes of the students using their devices to measure how well they are progressing, how they are struggling and what their weak and strong points are.
This, in turn, means that they can step in and help students long before they themselves might even be aware they’re in trouble.
Personalized programs
For the longest time, education assumed that one size fits all. It doesn’t matter how strong or weak a student’s skill is, it is better to put them among people of their own age and let them absorb whatever is being taught in that year.
Of course, this wasn’t just down to convention. It was also related to the difficulties of tracking what every student in a school needed and creating individual programs that best suited their learning styles.
Big data is changing that. As we gather more and more information about students, we don’t only get a better idea of how they’re doing, but can adjust the syllabus to better suit their learning needs. Even better, as this is automatic, this can be done for every student without overtaxing the teachers’ capability.
Evaluation without bias
Another way in which these evaluations differ is that they are no longer witnessed through the prism of a teacher’s likes and dislikes. For the longest time, we’ve known that teachers favor some students over others. For example, teachers tend to give higher grades to more attractive students. This does not happen consciously and instead is the result of how we’re put together, which makes it an incredibly difficult problem to tackle.
Big data offers a way out. After all, a computer does not recognize a student by their race, sex, or visual appeal. Similarly, big data can consider a test in absolute isolation – not giving the benefit of the doubt to students that have done better on previous exams. This creates an equal playing field where we are judged based on how we’re performing instead of all the external factors that surround it but should be irrelevant.
It can boost engagement
By exploring the numbers produced by 100s of thousands of students working on software, it will become far easier to know what is interesting to students and what is not.
Big data will then give a possibility of customization the learning experience to make what students are learning directly relevant to them. Even better, the technology will be able to analyze future lessons and use what it has learned from the student in previous encounters to modify the material as well as predict how hard the student will find it and how much time they will need.
It will even be able to conclude when students should take breaks and when they’re best served to study alone or in a group.
Fitting the right personalities together
Big data will make group projects as productive as possible. Right now, students are often grouped based on where they’re sitting in the class or who are their friends. The thing is, though likability is certainly a useful factor in deciding who to work with, it is certainly not the only way to do so.
A much better idea is to find which students are going to be the most useful to each other and group based on that. This will avoid the popularity contest whereby students that everybody wants to work with have too many choices while less popular students have too few.
Similarly, because students are grouped based on who they will work best with, class engagement will rise and struggling students can be brought back up to speed by who they work with rather than the teacher alone.
Last words
As we pay more attention to engaging all the stages of one’s memorization process, we’re going to see a revolution the learning curriculum is adopted on the fly based on a student’s energy levels, current interests and even how well they are able to focus.
Thus, having a good idea of what each student is capable of in turn will mean they can be advised in one of the most difficult decisions we all have to make, what we will do after school. In this way, big data will not just revolutionize the classroom, but even the choices we will make afterwards. That’s exciting (and perhaps a little bit scary).
The post How do algorithms influence teaching and bridge the students’ knowledge gap appeared first on Big Data Made Simple - One source. Many perspectives..
Source: http://bigdata-madesimple.com/