2

I use this library which basically is just a query builder.

The following query:

SELECT * FROM car_search ORDER BY car_model_name ASC, car_id DESC LIMIT 2;

Resulting in:

+------+----------------+---------------------+
| id   | car_model_name |              car_id | 
+------+----------------+---------------------+
|   33 |       Audi     |               17461 |
|   20 |       Bentley  |               17439 |
+------+----------------+---------------------+

Now if I alter the query orders such as,

SELECT * FROM car_search ORDER BY car_model_name ASC, car_id ASC LIMIT 2;

As can be seen, I changed the order from car_id DESC to car_id ASC in hope that the result would be ordered pretty much like how MySQL would. It's like Sphinx is ignoring the orders queries except for the first one.

But the returned result is exactly the same for both queries.

I've read about sorting modes in Sphinx doc, but it seems that it's only can be done if I were to use PHP sphinx client.

Is there anyway to have it written on index configuration instead, perhaps such as min_prefix_len, enable_star, etc?

Or directly from the query?

Samuel Adam
  • 1,327
  • 4
  • 26
  • 45
  • What are you trying to achieve? Your queries are producing exactly the expected results. – Gordon Linoff Dec 13 '15 at 13:19
  • @GordonLinoff The first query result did as expected, but I expect the second query to reorder the result by `car_id` but in ASC mode, but it's not. The second query results is exactly the same, it's like it doesn't matter what goes after the first order. – Samuel Adam Dec 13 '15 at 13:30
  • In both cases, only the first key is used for the sorting -- `car_model_name`. There are no ties so the second key is not used. – Gordon Linoff Dec 13 '15 at 13:59

1 Answers1

1

It seems to be a misunderstanding of how ORDER BY works in SQL/MySQL. What you need is: 

SELECT * FROM car_search ORDER BY car_id ASC, car_model_name ASC LIMIT 2;
Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • I'm really sorry, might just be to tired, it turns out the result set is just way too small for multiple orders. The queries is actually working with larger set of rows (multiple cars with same model is required for the second order to actually work). – Samuel Adam Dec 14 '15 at 02:29