Showing entries 1 to 10 of 15
5 Older Entries »
Displaying posts with tag: MySQL Tips (reset)
How to calculate median value in MySQL using a simple SQL query

What is a median value?

The median of an array of numbers is the value of the middle item in the array, assuming the array is sorted. If the array has an even number of items, the median represents the average of the two middle values in the array.

This value is very popular one tries to understand “in which half my value is?”. For example, I got a D (or 80) in my last test at school, am I in the top 50% of my class students or not?

As a practical example, let’s look into the process of retrieving the median value from the following array of school test grades: [55, 80, 95, 100, 99, 70, 60].

  1. First, we’ll sort the array: [55, 80, 95, 100, 99, 70, 60]  ===> [55, 60, 70, 80, 95, 99, 100].
  2. The array contains 7 items, which isn’t an even number, so therefore the median is the (7 / 2 + 1) item, which is the 4th item => 80.

Calculating the median value of …

[Read more]
MySQL is not using my index – Join with a range condition

This post is based on a true story from one of our team members.

Here at EverSQL, we have high appreciation for the MySQL database.
Said that, every product has its pros and cons and even some bugs here and there.

So let’s dive into how we used some “bad practices” to overcome a potential bug in MySQL’s optimizer.
TL;DR: This case study will describe a scenario where the MySQL optimizer will not choose to use an existing and allegedly good index to optimize a search query, which eventually caused a response time of 80 seconds of a simple page in a web application.

The Problem

The scenario from a business perspective – the development team was trying to implement a web page that presents data about the user actions in the last year.
The scenario from a technical database perspective – The team was trying to join two (same issue will occur with more than two) tables and use a range …

[Read more]
Faster Pagination in Mysql – Why Order By With Limit and Offset is Slow?

Queries with LIMITs and OFFSETs are common in application that require pagination and in some cases might work well for a while.
In many cases though, they become slow and painful once the OFFSET has a high value.

Why OFFSET is so slow?

Well, in most cases, low offset queries are not slow. The problem starts with high OFFSET values.
If your query is using the following limit clause: “LIMIT 50000, 20”, it’s actually requesting the database to go through 50,020 rows and throw away the first 50,000. This action can have a high cost an impact response time.

You may ask yourself “who the heck is going to skip to page 50,000 in my application?”.
Let’s list few possible use cases:

  • Your favorite search engine (Google / Bing / Yahoo / DuckDuckGo / whatever) is about to index your ecommerce website. You have about 100,000 pages in that website. How will your application react when the …
[Read more]
MySQL utf8 vs utf8mb4 – What’s the difference between utf8 and utf8mb4?

UTF8 VS UTF8MB4 – What’s the difference?

You’re getting a support call from an IT administrator in a tech company saying some of his critical data can’t be saved in the product you deployed at his servers a week ago. His users are seeing a general error from the application. About 30 of his 500 users are experiencing this issue and can’t save data in the application.

After a short 15 minutes debug session, you can see that the data is transmitted from the client side, received in the backend server and the insertion query is fired to the database. But still, no data in the database.

Hmm.. now it got interesting. You roll up your sleeves, put up a smile on your face, some sunglasses on your eyes and you start investigating (well, maybe just start investigating without all the fancy accessories).

Looking at the logs, it turns out that for specific inputs, MySQL refused to add the data to the database. The …

[Read more]
5 Common Mistakes PHP Developers Make when Writing SQL

Do not use the old mysql api

There are several ways to connect to a MySQL database in PHP. The most common ones are the MySQL API, the MySQLi API and the PDO API (PHP Data Objects). The last two support more features than the old mysql API and are more secure. If you’re using the old “mysql_” functions, you should stop and learn the new PDO API. Those old mysql functions are deprecated and are no longer supported in PHP 7.x.

Bad practice:

<?php  
$con = mysql_connect("localhost", "root", "mypass") or  
    die("Could not connect: " . mysql_error());  
mysql_select_db("tutorials");  
$result = mysql_query("select * from tutorials");  
echo "<h2>Here is a list of the topics:</h2>";  
while ($row = mysql_fetch_array($result)) {  
    echo $row['name']."<br />";  
}  
mysql_close($con);  
?>  

Better practice:

[Read more]
How to find unused indexes in a MySQL database?

Does it matter how many indexes I create?

A general rule of thumb is that the more indexes you have on a table, the slower INSERT, UPDATE, and DELETE operations will be.

Indexes in MySQL (or any database for that matter) are not static. Every time we update the table (for example, using an INSERT query), the relevant indexes are updated by MySQL. Otherwise, they will be useless in the next search query that will need them.

Therefore, adding indexes shouldn’t be taken lightly, as it’s actually a performance trade off which must be balanced properly. The more indexes you add on a set of columns / table, the slower INSERT, UPDATE and DELETE statements will be. On the other hand, search operations will be optimized using those indexes.

This article will not describe the methodologies of choosing the correct indexes, but will teach you how to find and remove redundant indexes from your MySQL database.

How …

[Read more]
Select max, min, last row for each group in SQL without a subquery

In several RDBMS databases, including MySQL, subqueries are often one of the causes for performance issues. Therefore, we have an incentive to avoid them whenever we can and to find alternative ways to implement our requirements.

One of the most popular uses for subselects in SQL is when one needs to fetch the first, last, maximum or minimum row for each group in a table. For example, how would you implement an SQL query that should fetch the employees with the maximum salary for each department from the employees table? Actually, fetching the salary itself is pretty easy, but it becomes more complicated when you want to fetch the employee name (the row data) along with the maximum salary.

Let’s look at the table:

Name Salary Role
David 130,000 …
[Read more]
MySQL Random Data Selection

Some days ago I was working in a vocabulary game and dictionary. The dictionary contains 1,10,000 words and meanings. I developed a vocabulary game where I had to randomly choose 10 words out of 1,10,000 dataset. Here I’m describing the possible solutions for retrieving 10 random words from 1,10,000…  Read Full Article

Obtaining values from UPDATE statements

Today, I would like to post one of my more favorite tricks. This is a useful trick when you want to obtain a value of a row you have updated. For instance, you have a blogs table and you’ve updated its timestamp and want to know the ID of the table you updated. You can emply this:

First, make sure to set the user-defined variable to NULL:

select @v_id = NULL;

UPDATE blogs SET timestampcol = ?, id = (@v_id := id) WHERE owner = ’someone’ AND title = ‘Hejsan Kaj!’;

Now, you can run:

SELECT @v_id;

And @v_id will contain the id of the row updated. If @v_id is NULL, then that means the row was not updated!

Note: This syntax is for MySQL. Other DBs mileage may vary

Oracle to MySQL date type considerations

I’m in the process of migrating remaining functionality of Tripod.com from using Oracle to using MySQL. There were some assumptions I had made about various data types, particularly dates. One thing I discovered while converting one piece of code is the Oracle function to_char(). I have a table:

SQL> select member_name, change_time from access_changes where member_name = 'phptester10' order by change_time desc;

MEMBER_NAME          CHANGE_TI
-------------------- ---------
phptester10          13-APR-09
phptester10          13-APR-09

So, I thought “ok, this is just going to be a ‘date’ type with a different format. For instance, 13-APR-09 will become 2009-04-13. But then I stumbled upon a query in a piece of code that does a sort on dates from this access_changes table:

SQL> select member_name, to_char(change_time, 'YYYY-MM-DD HH24:mi:SS') from access_changes where member_name = …
[Read more]
Showing entries 1 to 10 of 15
5 Older Entries »