MySQL Quite Complex Queries

Table of content

• Longest entry
• Users and categories
• Conclusion

I’ve read an article about database design. It’s very simple but I try to design my databases/tables following that article. But there is one thing that is also important. You need to design your tables and their relations so that you can get almost everything your need with as much queries as possible. This is a part that MySQL-s complex queries are very useful. In two last jobs that I did I needed to optimize script as much as possible. One project is a plugin for WordPress that I’m currently working on. When ever I do something I give my best to do it right and I really try to design relations and queries very good. Here are some examples of queries that I needed.

Longest entryTop

Imagine that you have a table and you need to get an id of entry with longest text. This query will echo id and text sizes from our table.

SELECT `id`, LENGTH(`text`)
	AS `len`
FROM `articles`

When this query is executed we get something like this table.

As you can see, our longest text has size of 7159 characters and an id of 1.  One way of doing it would be by selecting id, ordering by length of text in descending order and limiting it to just one entry so that first one retrieved. Sql for that would look like this.

SELECT `id` , LENGTH(`text`)
	AS `len`
FROM `articles`
ORDER BY LENGTH(`text`) DESC
LIMIT 1

But there is on other way that you can achieve it. You can use sub query. First we select id and length of the text and in WHERE condition we compare it with maximum length of our text using sub query.

SELECT `id`, LENGTH(`text`)
	AS `len`
FROM `articles`
WHERE LENGTH( `text` ) = (
	SELECT MAX(LENGTH(`text`))
	FROM `articles`
)

They both do the same thing and when executing each of them you get result like this.

This is not as elegant as previous query but it show you how to use sub queries and it’s faster then 1st one for about 0,0001 sec. That’s not much but in big projects it’s worth trying.

Users and categoriesTop

This plugin that I’m currently working on has users and categories associated with each user. This are tables and entries in them.

SELECT *
FROM `wp_qe_cats`
LIMIT 0 , 30

SELECT *
FROM `wp_qe_abook`
LIMIT 0 , 30

Let’s say you have to retrieve all categories with users in it. My first thought was just to select all categories and then for each of them select count of users in it. Then I would have two queries and I do not want that because it is n + 1 query executions (where n is number of categories) So for example if we have 4 categories first query will get those 4 and there will be 4 more to get users count. To solve that I’ve used an other sub query in fields list that will be selected

SELECT `name` , `id` , (
	SELECT COUNT(*)
	FROM `wp_qe_abook`
	WHERE `wp_qe_abook`.`category` = `wp_qe_cats`.`id`
) AS `count`
FROM `wp_qe_cats`
WHERE `userid` =1
LIMIT 0 , 10

Since we only have 1 category it will return just one row but imagine that you had 500 categories then you would do 501 query. With this we just do one query and get same resluts.

ConclusionTop

It’s always good to create your relations and queries good because your reduce number of lines of code and execution time. By reducing number of lines of code, you make your code cleaner and easier to maintenance.

You can download table structure and data here.

• 27.07.2010 -- Upload Images With MySQL (0)
  [tinytoc level="1"]Intro[/tinytoc] Ok, let's continue with tutorial requests. This tutorial will ...
• 21.05.2010 -- Multi-Language Site (6)
  It's been a while since I last posted something because I was very busy. So let's do something usefu...
• 11.08.2009 -- Sql Queries Cache (0)
  This will be intermediate tutorial about caching SQL query results. We will use interface to make su...
• 16.07.2010 -- Packt Special Offer (1)
  Packt Publishing has new offer for this hot summer. Have a someones birthday soon and don't know ...
• 07.04.2010 -- Edit XML (15)
  I had an idea about creating tutorial that will cover manipulating XML in PHP. The idea was to creat...
• 02.01.2010 -- Enable E-mail In PHP – Win (0)
  This will be a quick tutorial that will show you how to enable e-mail function in PHP on Windows....
• 23.12.2009 -- Learning Resources (0)
  Reader Satish requested a list of tutorials where he could learn about PHP, MySQL and jQuery. Since ...