Tag Info

New answers tagged

5

There is a subtle problem hidden in your ORDER BY clause: ORDER BY updated_at DESC Would sort NULL values first. I assume you do not want that. Your column updated_at can be NULL (lacks a NOT NULL constraint). The missing constraint should probably be added. Your query should be fixed either way: SELECT * FROM orders WHERE shop_id = 3828 ORDER BY ...


5

I dont know Postgresql too well, but you're checking across two seperate keys to find the values you're looking for, try creating it as a composite key instead "index_orders_on_shop_id" btree (shop_id) "index_orders_on__updated_at" btree (updated_at) becomes "index_orders_on_shop_id__updated_at" btree (shop_id,updated_at) that could help if there's a ...


1

All you need to do is ORDER BY startDate ASC first, then featured DESC: SELECT a.* --, -- c.id as catid, -- c.cat_name FROM eventlisting a -- LEFT JOIN categories c ON a.cat_num=c.id WHERE a.startDate >= '2015-08-21' AND a.endDate <= '2016-08-21' AND a.status=1 GROUP BY a.id ORDER BY a.startDate ASC, a.featured DESC LIMIT ...


2

Try: select id from tab where id not in (select id from tab where index=0) group by id having max(index)+1!=count(*) order by id; Preventing that the combination of id and index is unique


2

create table index_test(id number, idx number); insert into index_test (id, idx) values (1,0); insert into index_test (id, idx) values (1,1); insert into index_test (id, idx) values (1,2); insert into index_test (id, idx) values (1,3); insert into index_test (id, idx) values (1,4); insert into index_test (id, idx) values (2,0); ...


-1

DECLARE @Line TABLE(Id INT) INSERT INTO @Line (Id) VALUES (1) INSERT INTO @Line (Id) VALUES (2) INSERT INTO @Line (Id) VALUES (3) INSERT INTO @Line (Id) VALUES (4) INSERT INTO @Line (Id) VALUES (5) INSERT INTO @Line (Id) VALUES (7) INSERT INTO @Line (Id) VALUES (9) ;WITH CTE as ( SELECT ROW_NUMBER() OVER (ORDER BY Id) as RowId, Id FROM @Line ) ...


0

Following @oNare's throwing down of the SQL gauntlet, verily I resolved to take up the challenge to my SQL-hood and thus, in this the year of our Lord two thousand and fifteen, I thusly formulated the following palimpsest. I offered incantations to the SQL muses, received inspiration from the SQL daemons, had it carved into silicon and presented it to the ...


1

I was curious about how VĂ©race's query is going to sort different varchar's f1 (his tables) length, so, It didn't work as expected. I decided to make my own sort using LOCATE,LEFT and (the one I hate) REGEXP. New data: mysql> SELECT * FROM billy ORDER BY f1; +-----------+-------+ | f1 | f2 | +-----------+-------+ | on13 | 12 | | on2 ...


1

You can sort this using INET_ATON: Query: SELECT * FROM `ip` WHERE `subnet` LIKE '10.1.2.0/24' ORDER BY INET_ATON(ip); Data: mysql> select * from ip; +------------+-------------+ | ip | subnet | +------------+-------------+ | 10.1.2.1 | 10.1.2.0/24 | | 10.1.2.10 | 10.1.2.0/24 | | 10.1.2.123 | 10.1.2.0/24 | | 10.1.2.3 | 10.1.2.0/24 | ...


5

I created a table called "billy". mysql> SELECT * FROM billy ORDER BY f1; +----------+-------+ | f1 | f2 | +----------+-------+ | soho10 | test5 | | soho12 | test1 | | soho123 | test2 | | soho1234 | test4 | | soho222 | test3 | +----------+-------+ 5 rows in set (0.00 sec) which doesn't give the correct sort order - 1234 is bigger than ...


1

Indicies are used only to select (find) the rows. Once they are put into a final resultset, it is simply a set of rows and columns, regardless of where they came. Order by (sort) is applied as the last operation to this resultset. In your case, you are getting a very large resultset. Obviously, any effort to sort it would add up to the query time.


4

The real answer is that "prefix" indexes are virtually useless. I am referring to KEY `testStoreTitle` (`storeTitle`(182)) Since the index contains only the truncated values, it does not have a completely ordered list of Titles, hence cannot easily be used to do the ORDER BY. InnoDB has a limit of 767 bytes (the max for a utf8 VARCHAR(255)). That can ...


1

For the record SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4); should work as well because you do not have to order the list in the WHERE clause As for how it works, FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists. IF id = 1, then FIELD(id,3,2,1,4) ...


0

Maybe this will be too far from actual code so not low level enough from what you wanted: When MySQL cannot use index to retrieve data in sorted order, it creates a temporary table/resultset with all selected columns and some additional data - one of those is some kind of a column for storing the results of ORDER BY expression value for each row - then it ...


5

David Spillett's answer is correct in all points, except for the "encouraging" suggestion. Here's a way to not only encourage but (in almost all versions) force the optimizer to choose a plan that uses the wanted index to find the 50 rows - and only after that perform the join. It can't always be used but the FOREIGN KEY constraint assures that in this case ...


2

The "why it takes so long" is Using filesort - this means that it is spooling all the results out to sort them without the index. The problem is that the SQL engine can only use one index per table reference. In this case it will be using the index best suited for your join predicate (the one for your primary key). IIRC mySql's query planner still follows ...



Top 50 recent answers are included