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%'"));
Best Answer
FULLTEXT
only applies to string datatypes, such asVARCHAR
andTEXT
. 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 thatALTER
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 andSHOW CREATE TABLE
.More
will check for a specific second
or
will check for any
created
in a specific day.Etc.
Either of these examples may use an
INDEX
(notFULLTEXT
) containingcreated
. I say "may" because there could be other issues. Please provide theSELECT
andSHOW CREATE TABLE
.(The API, such as
mysqli
, is not relevant to the question.)Still More
Looking at this formulation...
I see that
INDEX(symbol)
is likely to be helpful, but nothing having to do withcreated
is useful. This is because of the leading wildcard on theLIKE
, and because an index won't be used with aDATETIME
andLIKE
. The way to get performance via an index is as discussed above (using a range).