KeepingyourdataworkontheserverusingUNION_MySQL

With any technology you use, you have to ask yourself, "What is this tech good at doing?" For me, MySQL has always been excelent at running lots of small queries that use primary, unique, or well defined covering indexes. I guess most databases are good at that. Perhaps that is the bare minimum for any database. MySQL seems to excel at doing this however. We had a query that looked like this:

select category_id, count(*) from some_table
where
article_id in (1,2,3,4,5,6,7,8,9) and
category_id in (11,22,33,44,55,66,77,88,99) and
some_date_time > now() - interval 30 day
group by
category_id

There were more things in the where clause. I am not including them all in these examples. MySQL does not have a lot it can do with that query. Maybe there is a key on the date field it can use. And if the date field limits the possible rows, a scan of those rows will be quick. That was not the case here. We were asking for a lot of data to be scanned. Depending on how many items were in the in clauses, this query could take as much as 800 milliseconds to return. Our goal at DealNews is to have all pages generate in under 300 milliseconds. So, this one query was 2.5x our total page time.

In case you were wondering what this query is used for, it is used to calculate the counts of items in sub categories on our category navigation pages. On this page it's the box on the left hand side labeled "Category". Those numbers next to each category are what we are asking this query to return to us.

Because I know how my data is stored and structured, I can fix this slow query. I happen to know that there are many fewer rows for each item for article_id than there is for category_id. There is also a key on this table on article_id and some_date_time. That means, for a single article_id, MySQL could find the rows it wants very quickly. Without using a union, the only solution would be to query all this data in a loop in code and get all the results back and reassemble them in code. That is a lot of wasted round trip work for the application however. You see this pattern a fair amount in PHP code. It is one of my pet peeves. I have written before about keeping the data on the server . The same idea applies here. I turned the above query into this:

select category_id, sum(count) as count from 
(
	(
	select category_id, count(*) as count from some_table
	where
	article_id=1 and
	category_id in (11,22,33,44,55,66,77,88,99) and
	some_date_time > now() - interval 30 day
	group by
	category_id
	)
	union all
	(
	select category_id, count(*) as count from some_table
	where
	article_id=2 and
	category_id in (11,22,33,44,55,66,77,88,99) and
	some_date_time > now() - interval 30 day
	group by
	category_id
	)
	union all
	(
	select category_id, count(*) as count from some_table
	where
	article_id=3 and
	category_id in (11,22,33,44,55,66,77,88,99) and
	some_date_time > now() - interval 30 day
	group by
	category_id
	)
	union all
	(
	select category_id, count(*) as count from some_table
	where
	article_id=4 and
	category_id in (11,22,33,44,55,66,77,88,99) and
	some_date_time > now() - interval 30 day
	group by
	category_id
	)
	union all
	(
	select category_id, count(*) as count from some_table
	where
	article_id=5 and
	category_id in (11,22,33,44,55,66,77,88,99) and
	some_date_time > now() - interval 30 day
	group by
	category_id
	)
	union all
	(
	select category_id, count(*) as count from some_table
	where
	article_id=6 and
	category_id in (11,22,33,44,55,66,77,88,99) and
	some_date_time > now() - interval 30 day
	group by
	category_id
	)
	union all
	(
	select category_id, count(*) as count from some_table
	where
	article_id=7 and
	category_id in (11,22,33,44,55,66,77,88,99) and
	some_date_time > now() - interval 30 day
	group by
	category_id
	)
	union all
	(
	select category_id, count(*) as count from some_table
	where
	article_id=8 and
	category_id in (11,22,33,44,55,66,77,88,99) and
	some_date_time > now() - interval 30 day
	group by
	category_id
	)
	union all
	(
	select category_id, count(*) as count from some_table
	where
	article_id=9 and
	category_id in (11,22,33,44,55,66,77,88,99) and
	some_date_time > now() - interval 30 day
	group by
	category_id
	)
) derived_table
group by
	category_id

The arrow in the image is when I rolled the change out. Several other graphs show the change in server performance as well.

The UNION is a great way to keep your data on the server until it's ready to come back to your application. Do you think it can be of use to you in your application?

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com