Combine queries

if the queries are often used together then combining them together can greatly improve performance.

Table 1 and 2 show the two separate tables and Table 3 shows the result you would obtain from combining queries.

Table 1

content

Page name

hello

index.html

Hia

index.html

page2

p2.html

Index

contents.html

Table 2

Message

The site will be down on Tuesday

Table 3

pagename

content

NULL

page

index.html

hello

page

index.html

Hia

page

The site will be down on Tuesday

motd

schema:scott

DROPTABLEpage;DROPTABLEmotd;

CREATETABLEpage(contentTEXT,pagenameTEXT);INSERTINTOpageVALUES('hello','index.html');INSERTINTOpageVALUES('Hia','index.html');INSERTINTOpageVALUES('page2','p2.html');INSERTINTOpageVALUES('Index','contents.html');CREATETABLEmotd(messageVARCHAR(100));INSERTINTOmotdVALUES('The site will be down on Tuesday');

In this example a typical approach to these tables could be:

SELECT pagename, content
FROM page
WHERE pagename = 'index.html'

or:

SELECT message FROM motd

These two queries can be combined using UNION and NULLs where necessary
and therefore a single query can be run allowing a quicker response from the database.