Can you use the same query and define two different "ORDER BY"?

Question

I use the following passage for a query to the database

$cs1 = $pdo->query("SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY CLIQUES DESC LIMIT 5")->fetchAll();

I would like to explore this consultation further in order to use as little memory as possible.

Here’s the thing, on home page I’m using two different queries

$cs1 = $pdo->query("SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY CLIQUES DESC LIMIT 5")->fetchAll();

$cs2 = $pdo->query("SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY VOTOS DESC LIMIT 5")->fetchAll();

As you can see, the only thing that changes between queries is the column cited in ORDER BY, where in a cited column of ORDER BY is CLIQUES and the other VOTOS.

In my view this is a waste of resources, since both queries are displayed on the same page. There is then some way to make a single query to determine different columns in the ORDER BY ? Something like that :

<?php 
    $colunas = ID, VOTOS, CLIQUES, CATEGORIA;

    $csu = $pdo->query("SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY $colunas DESC LIMIT 5")->fetchAll();

?>

Ai type, each time you reach the limit of 5, skip to the next term cited in the variable $colunas

Answer 1

I see two possible solutions to this problem:

1. Continue using two separate queries and create indexes in the columns CLIQUES and VOTOS. By using indexes your database manager system will bring both queries faster.

2. Bring all the resultset and sort with PHP. That way you don’t need to create the indexes and can always have the same query.

Column ordering CLIQUES:

usort($resultset, function($a, $b) {
    return $a['CLIQUES'] > $b['CLIQUES'];
});

Column ordering VOTOS:

usort($resultset, function($a, $b) {
    return $a['VOTOS'] > $b['VOTOS'];
});

Answer 2

Well Ivan Veloso, as for the question of resources, to make these queries separately, will not be so expensive, because Mysql works with Query Cache and this way, before the query’s are executed, they will be compared with the results stored in the cache, and if the result already exists, a new query will not be necessary. To verify that this functionality is enabled in your Mysql you can run the command:

SHOW VARIABLES LIKE 'have_query_cache';

As for your problem, if you want to make a return only, for your various queries with different sorts, you can use the UNION ALL. However the return will be grouped and you will have to treat it in PHP. Your query would look something like this:

(SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY CLIQUES DESC LIMIT 5) as postcliques
UNION ALL
(SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY VOTOS DESC LIMIT 5) as postvotos

In this case, if you have more than 5 records in the table post, the return will be 10 records, the first 5 ordered by clicks and the last 5 ordered by votes.