Mysql – User-defined sort order for a query

MySQL

I'm storing user preferences for sort order in a table:

$queryHistory = "SELECT orderby,ascdesc FROM user WHERE UserID= ".$id;

$resultHistory = doQuery($connection, $queryHistory);
$numHistory = mysql_num_rows($resultHistory);
if ($numHistory > 0) {
  while($rowHistory = fetchRow($resultHistory, $queryHistory)) {
    $orderBy = getResult($rowHistory, $resultHistory, 1);
    $sortBy = getResult($rowHistory, $resultHistory, 2);
  }
}

And constructing a query based on that which looks like:

$otherQuery = "SELECT *
               FROM  user
               WHERE user.UserID = $id
               ORDER BY ".$orderBy." ".$sortBy;

Is it possible to do this with a single query, rather than first reading the preferences then running the actual query?

Best Answer

How about using a prepared statement to do it? Something like the below:

SELECT @orderBy := orderby, @ascDesc := ascdesc FROM user WHERE UserId = @id;
PREPARE cmd 
FROM 'SELECT * FROM user WHERE user.UserID = ? ORDER BY ? ?';
EXECUTE cmd USING @id, @orderBy, @ascDesc;
DEALLOCATE PREPARE cmd;

EDIT

Apparently it is not possible to use parameters in the way that I have above (see comment below) in which case you should do the following:

SELECT @orderBy := 'Name', @ascDesc := 'ASC' FROM user WHERE Id = @id;
SET @s := 
CONCAT('SELECT * FROM user WHERE user.UserID = ? ORDER BY ',@orderBy,' ',@ascDesc);
PREPARE cmd FROM @s
EXECUTE cmd USING @id;
DEALLOCATE PREPARE cmd;

I hope this helps you.

Related Solutions

MySQL Slow Query – How to Optimize WHERE Clause Performance

VIEWPOINT #1 : You many need to take a look at column value population

SELECT COUNT(1) rowount,type FROM games GROUP BY type WITH ROLLUP;
SELECT COUNT(1) rowcount,leaguelevel FROM games GROUP BY leaguelevel WITH ROLLUP;

From your question, I gather two things:

The number of rows in games with type='R' must be a low number against the number of rows in the games table.
The number of rows in games with leaguelevel='mlb' must be a high number (greater than 5% of the table) against the number of rows in the games table. (5% is a rule-of-thumb number in the eyes of Query Optimizers)

VIEWPOINT #2 : You may need to refactor this query

Notice that query will perform the WHERE portion after all JOINs are complete. If the WHERE portion could be performed earlier that could help reduce the time. Try reorganizing the query like this:

SELECT * FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN (SELECT * FROM games WHERE leagueLevel = 'mlb') games
ON games.gameName = hits.gameName
LIMIT 50;

VIEWPOINT #3 : Retrieve only the columns you really need

I see you have SELECT * and you have four tables (hits, stadiums, players, games). You will have a lot of duplicate data to drag into the query, particularly when dragging the gameName column from all four tables.

You should reorganize the query to bring only one gameName column:

SELECT hits.gameName,hits.*,players.*,staduims.*,games.* FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN (SELECT * FROM games WHERE leagueLevel = 'mlb') games
ON games.gameName = hits.gameName
LIMIT 50;

Additionally, if you do not need every column from the hits tables, then only include the column you know you will access. The same goes for players, stadiums, and games.

In other words, as an example, if you only need the playerName from the player table, then you do not need player.* in the SELECT. You will need just player.playerName.

VIEWPOINT #4 : You may need to index the leagueLevel column

You will need to do the following to make the needed index:

ALTER TABLE games ADD INDEX (leagueLevel);

Before doing so, run this

SELECT COUNT(1) rowcount,leaguelevel FROM games GROUP BY leaguelevel WITH ROLLUP;

Any value for leagueLevel whose count is greater than 5% of the table will cause the MySQL Query Optimizer not to use the index.

Mysql – Want to order on two columns but not the usual way

SELECT *
FROM Mytable
ORDER BY
   userID, Date

I assume Date is really a date/time type and not varchar...

Edit, after clarification:

Untested

SELECT
    M.*
FROM
    ( --one row for each user
    SELECT MIN(Date) AS FirstUserDate, userID
    FROM MyTable
    GROUP BY userID
    ) foo
    JOIN
    MyTable M ON foo.userID = M.userID
ORDER BY
    foo.FirstUserDate, M.userID, M.Date;

Best Answer

Related Solutions

MySQL Slow Query – How to Optimize WHERE Clause Performance

Mysql – Want to order on two columns but not the usual way

Related Question