I created is a DATETIME column. I also tried to change all Collation's of that table to the same utf8 type but it wouldn't stick. Is it possible to do a FULLTEXT search on a DATETIME column? (%like% is way too slow).

The error I get is:

1283 - Column 'created' cannot be part of FULLTEXT index

-- edit:

$add_24 = $i + 1440;

$format_second = date("Y-m-d H:i", strtotime('-'.$add_24.' minutes'));

$p_price = mysqli_fetch_array(mysqli_query($con, "SELECT created FROM products WHERE created LIKE '$format_second%'"));

2 Answers 2


FULLTEXT only applies to string datatypes, such as VARCHAR and TEXT. After all, it is looking for "words".

The charset/collation of a table is only the default for newly added columns.

You can ALTER TABLE ... CONVERT TO ... to change all the columns in a table. But before doing so, tell us why you are doing it and whether there is already non-English text anywhere in table. Using that ALTER may just make things worse.

What, in particular, are you searching for in the DATETIME? It may be possible to change to a 'range' query that may run efficiently with an index. Let's see the attempted query and SHOW CREATE TABLE.


WHERE created = '2018-01-11 20:49:01'

will check for a specific second


WHERE created >= '2018-01-11'
  AND created  < '2018-01-11' + INTERVAL 1 DAY

will check for any created in a specific day.


Either of these examples may use an INDEX (not FULLTEXT) containing created. I say "may" because there could be other issues. Please provide the SELECT and SHOW CREATE TABLE.

(The API, such as mysqli, is not relevant to the question.)

Still More

Looking at this formulation...

SELECT  symbol,created
    FROM  products
    WHERE  symbol='".$ccc['symbol']."'
      AND  created LIKE '%".$date_format."%'
    limit  0,48

I see that INDEX(symbol) is likely to be helpful, but nothing having to do with created is useful. This is because of the leading wildcard on the LIKE, and because an index won't be used with a DATETIME and LIKE. The way to get performance via an index is as discussed above (using a range).

  • Hey thanks for the response.. I'm trying to efficiently search for a specific date (ex: 2018-01-11 20:49:01) from a DATETIME column but %LIKE% is too slow. The table has about 8,510,000 rows max at all times. Jan 13, 2018 at 19:25
  • @MarcAlexander - see my additions.
    – Rick James
    Jan 13, 2018 at 19:32
  • $add_24 = $i + 1440; $format_second = date("Y-m-d H:i", strtotime('-'.$add_24.' minutes')); $p_price = mysqli_fetch_array(mysqli_query($con, "SELECT created FROM products WHERE created LIKE '$format_second%'")); Jan 16, 2018 at 0:53
  • I edited the OP. Jan 16, 2018 at 0:55
  • @MarcAlexander - For performance of a 1-minute range, see my example and INDEX (except change DAY to MINUTE). Also, I would do the date arithmetic in SQL, not PHP.
    – Rick James
    Jan 16, 2018 at 1:01

Don't compare DATETIME as a string.

First Rick James, answer is right and should be accepted. You have 3 type categories in MySQL, essentially, you can see them here. (Maybe sometime in the future, you'll have a real JSON type.)

  • Numeric
  • Date/Time
  • String

There are types in each of those. Each of those types has it's own set of operators, and functions. You should always use that types operators and functions. Don't think of a DATETIME, as a string. Think of it as an object that is stringified by default. For your use case,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.