Query to database very long

Level 63

I have tried to order by id desc and now it works fine, the query is very fast.

Why is it faster for the id than for the created_at field ?

Tray2

Level 73

@vincent15000 Because you have an index (primary key) on the id, and you don't have and index on the created_at column. Just extract the SQL and run explain on it and you will see the difference.

1 like

Level 63

@Tray2 But I have created an index for the created_at column, created directly in phpmyadmin.

Why this difference ?

Level 50

That doesn't seem right. Are you sure the created_at column is indexed correctly?

Can you run show indexes from your_table_name (mysql/maria/postgres) or PRAGMA index_list('your_table_name') (sqlite) and see what the index looks like?

1 like

Level 63

@JussiMannisto Sure it's created correctly. I have created it manually in phpmyadmin to check if it is the solution for the query to be faster.

Level 50

@vincent15000 3-5s seems way too long if the index is used. Does your query have any other conditions outside of order by created_at desc? Can you post the query code?

If the content column held a lot of data, that might explain the slow speed. But it doesn't explain why queries by ID would be so much faster. If ID is the clustered index, it might be a bit faster. But not to this degree.

1 like

Level 63

@JussiMannisto The query is very simple.

$messages = Message::
	orderByDesc('created_at') // or id
	->take(10)
	->get();

Level 50

@vincent15000 created_at is misspelled. Other than that it looks normal.

What DBMS are you using; mysql, sqlite, something else? Did you double-check the index using one of the commands I suggested?

1 like

Tray2

Best Answer

Level 73

@vincent15000 If the index is properly created it should not matter that much, but of course a sequential index is faster than a date time one.

3 likes

Level 50

@Tray2 It's a bit more complicated than that. At least when talking about primary keys in mysql or mariadb.

PKs in those databases are also the clustered index (with innodb). They'll be more spread out on the disk compared to secondary indices. That means many more disk reads when traversing the index. Also the timestamp index size (5 bytes) is smaller than the usual primary key (8 bytes).

You can check this fiddle I made if you're interested.

The COUNT(*) statement will always use the timestamp index instead of the primary index because the optimizer knows it'll be faster. It's not about 5 vs 8 bytes either - it'll always use the smallest non-clustered index.

The SELECT statements are more complicated. It's not just about sorting, it's also about reading full rows. I wouldn't draw any conclusions without benchmarks on a bigger DB with real data.

1 like

jlrdw

Level 75

@vincent15000 if you use id, is it faster?

$messages = Message::
	orderByDesc('id')
	->take(10)
	->get();

2 likes

Tray2

Level 73

@JussiMannisto Of course it's a bit more complicated than that, but keeping it simple is never wrong.

2 likes

Level 63

@jlrdw Yes it's faster.

jlrdw

Level 75

@vincent15000 which should be the case. A numeric index I've always found faster.

2 likes

Level 50

@jlrdw It's all stored as bits. Whether those bits represent numbers or timestamps at the application layer doesn't matter at all. What really matters is the size and cardinality of the index. In mysql, an index on a timestamp column will be smaller than one on a 64 bit integer column.

In any case, at just 600k rows the query shouldn't take several seconds to execute if the index is working.

@vincent15000 Can you run this in phpmyadmin and post the output:

explain select * from messages order by created_at desc limit 10;

2 likes