How does one create an index on the date part of DATETIME field in MySql

by

If I remember correctly, that will run a whole table scan because you’re passing the column through a function. MySQL will obediently run the function for each and every column, bypassing the index since the query optimizer can’t really know the results of the function.

What I would do is something like:

SELECT * FROM transactionlist 
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';

That should give you everything that happened on 2008-08-17.

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. MySQL: Enable LOAD DATA LOCAL INFILE
  3. MySQL “WITH” clause
  4. MySQL Query – Records between Today and Last 30 Days
  5. MySQL root access from all hosts
  6. Setting up MySQL and importing dump within Dockerfile
  7. Basics of Foreign Keys in MySQL?
  8. How to import data from text file to mysql database
  9. Select last N rows from MySQL
  10. Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause?
  11. What is the benefit of zerofill in MySQL?
  12. MySQL user DB does not have password columns – Installing MySQL on OSX
  13. How to select date from datetime column?
  14. How do I use properly CASE..WHEN in MySQL
  15. How to detect UTF-8 characters in a Latin1 encoded column – MySQL
  16. MySQL – How to SUM times?
  17. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  18. MYSQL into outfile “access denied” – but my user has “ALL” access.. and the folder is CHMOD 777
  19. SQL unique varchar case sensitivity question
  20. In MySQL, can I defer referential integrity checks until commit
  21. How do I count columns of a table
  22. Mysqldump launched by cron and password security
  23. How to insert Hindi language in Mysql
  24. Converting epoch number to human readable date in mysql
  25. How to make join queries using Sequelize on Node.js
  26. MySQL delete duplicate records but keep latest
  27. MySQL 5.6 – DENSE_RANK like functionality without Order By
  28. MySQL: Can I do a left join and pull only one row from the join table?
  29. Is it possible to count two columns in the same query
  30. mySQL regex in the where clause

Leave a Comment