![]() ![]() There are a lot of tricks up MySQL's sleeve. From the MySQL documentation: To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. rather than physically limiting the rows of the table. It seems you would be better off using a query like: select top 1000 entityID, entityName from TableName. And sometimes there is a simple trick to get rid of one of the sorts. I wouldnt recommend trying to limit the number of rows in a SQL table, unless you had a very good reason to do so. That way, I can explain what EXPLAIN tells you (or does not).ĭid you know that having both a GROUP BY and ORDER BY may cause the use of two sorts? EXPLAIN won't point that out. Be sure to include SHOW CREATE TABLE, the full query, and EXPLAIN SELECT. I can discuss the what and why of each one you throw at me. (The transition is somewhere around 20%, but it that is imprecise.)Ĭonfused? Fine. On the other hand, if there were 9999 rows, then it would use the index. If there are "only" 1000 rows in your table, my example with the ORDER BY x won't use the index because it is faster to simply read through the table, tossing 90% of the rows. A lot of questions on this forum are "Why isn't MySQL using my index?" Back to your query. ![]() ![]() UNLESS you only ask for x.Īnd here's another wrinkle. then it has to reach into the data 100 times to find the other columns that you ask for. Together with INDEX(x) - This may use the index and fetch only 100 rows from the index. 2 This is my current mySQL query: SELECT e., MAX (m.datetime) AS unreadlast, COUNT (m.id) AS unread FROM TABEVENT e LEFT JOIN TABMESSAGE m ON e.idm.eventid WHERE ( m.read IS NULL OR m.read 0) GROUP BY e.id ORDER BY m.datetime DESC, e. And that sort must be done before seeing the LIMIT. GROUP BY and ORDER BY are likely to incur a sort, which is arguably even slower than a full table scan. But, if you change that query in any way, my statement may be wrong. The query you wrote will fetch only 100 rows, not 1000. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |