Mysql why is count so slow

If you need to have the result instantly and you don't care if it's You do not need to do that. You are using prepared statements, which escape the variables automatically. The error is telling you that the socket, called MySQL , cannot be used to make the connection, probably because it does not exist error number 2. On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs.

For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. Your index is sorted desc so when you ask for ascending it needs to do a lot more work to bring it back in that order.

I work on a database that has over million rows in one of its tables. It can take hours to restructure a table, but ordinary queries aren't a problem as long as they're assisted by an index. Your laptop is pretty out of date and underpowered to use as a high-scale database server. It's going to take a long time to do a table restructure. The low amount of memory and typically slow laptop disk is probably constraining you. Collectives on Stack Overflow. Learn more. Asked 12 years, 9 months ago.

Active 1 year, 9 months ago. Viewed 73k times. Improve this question. Ovid Ovid How about if you try something like Add a comment. Active Oldest Votes. Two things I would try: run optimize table. This will ensure that the data pages are physically stored in sorted order.

This could conceivably speed up a range scan on a clustered primary key. This will store a copy of that column in index pages which be much faster to scan.

After creating it, check the explain plan to make sure it's using the new index. Improve this answer. The "optimize table" didn't help much, but the redundant index solved the problem. I'm pretty interested in the details of why this works and the kinds of queries for which it is useful. Do you have any links to further reading on the topic? Any improvement may be because you freshly loaded the entire table into cache. MarkAmery MySQL innodb format stores all row data in the primary index; if you don't have a primary key, one is synthesized for use in the storage index.

This means that rather than it being an index over bigints, it's an index over the whole data tuple, so it has to stride through, so it's not fast to scan. MarkAmery for more details, see dev. Show 1 more comment. Here are a few things I suggest: Change the column from a "bigint" to an "int unsigned". EDIT: Looking more closely at this table, it looks like a logging-style table, where rows are inserted but never modified. Community Bot 1 1 1 silver badge. Given that we have numbers like "", I think that already overflows "int unsigned" it's a high-res timestamp.

Also, we'd lose our FK constraint. Why use a timestamp for your primary key, if you already have a timestamp field?

Also, what happens if two events happen at the same instant? Asked 4 years, 2 months ago. Active 1 year, 4 months ago. Viewed 27k times. Improve this question. Benubird Benubird 1 1 gold badge 3 3 silver badges 8 8 bronze badges.

Add a comment. Active Oldest Votes. Improve this answer. Kevin Bott Kevin Bott 3 3 silver badges 9 9 bronze badges. Although I guess that would be tricky given RickJames ' point below about miscounting due to non-committed operations.

This is faster, but less accurate, than reading the next-to-bottom nodes entirely. MySQL developers made choices and tradeoffs. Rick James Rick James