Menu
Community /Pin to ProfileBookmark
I'm getting Warning: mysql_result(): supplied argument is not a valid MySQL result resource in.
Is my normal select count code not work with date searches?
@NogDogApr 01.2009 — #Assuming
I'm under the impression that using the YEAR() function on a field will force MySQL to bypass using any index(es) on the date[time] field. I could be mistaken.
Of course, if the field isn't indexed, I suppose it doesn't really matter ... @NogDogApr 01.2009 — #
A quick test with EXPLAIN suggests that you may be correct.
The DATE() function is used to extract the date part of a timestamp. As such, you don't need in BETWEEN...AND but you should use to extract the date part from the field
MySQL actually recommends casting the string literal. This makes sense over casting the field itself, since an operation on a field generally requires a table scan:
When I EXPLAINed a set of queries, one which casted the columns and the other which casted the literal, the one which casted the column somehow ended up examining more rows than the table actually contained, whereas the other needed to examine only the matching/returned rows.
Bear in mind, in reference to my first post, if the field you're matching against is actually a datetime, the query should cast the date literal as a datetime including the full datetime range:
Optimization is a little off topic though...
Did you get the [I]mysql_error()[/I], [B]phpnsuff[/B]?
The main point I was trying to make, however, was that using date() to extract a date part from two literals which consisted of only date parts was useless.
In "real life" terms, however, if all I'm insterested in is the year, the fastest and easiest way to go would be:
Sure, casting with date() or datetime() may be unnecessary--at least my version of MySQL autocasts the literal correctly. But, casting the literals makes it the interpretation of the literals [B]explicit[/B] to MySQL from the start of the query [B]and[/B] to other programmers. And, in my humble opinion, at the cost of only a few extra characters, being explicit is [I]always[/I] a good thing.
That actually depends entirely on the format of the date field. If it's a text field, you are absolutely correct. However, my assumption is that the
So, even if your table only contains dates from 2008, you've [slightly] increased running time by using the pattern match. If your table even contains two full years worth of dates, you've more than doubled the running time of that query. And in either case, I'm quite confident that it introduces a relatively lengthy table-wide lock (blocking writes).
So, I think the small amount of effort required to [cast your literals and] use a range comparison is more than made up for by the drastic increase in performance.Forget the theory... Just try it.
You'll see how much faster it is. Above all, it is less error-prone than all this casting and juggling the date.
And, as far as I know, internally, MySQL date fields are stored as text ('YYYY-MM-DD HH:MM:SS'). Perhaps that's why it's faster?
Running EXPLAIN is far from theory. It simulates the query, reports which indexes are used, and reports how many rows the server must examine in order to complete the query. Your version of the query examines more rows than are even in the table, regardless of what range I supply (undoubtedly creating "temporary" rows for the type conversions or something). My version examines [B]only[/B] the rows the query request.
If I request a record from a single date-time, only the row(s) from that date-time are touched. The same for your query, the entire table is scanned.
Older versions of MySQL may store dates in pseudo-textual formats. I'm not entirely sure. However, if you're using the [I]timestamps[/I] type with any remotely recent version (which you most often should be) your dates will be stored as a 32 bit integer representing the number of seconds in UTC since the epoch. And if you're using a datetime instead, they'll be stored in local time as two 64 bit integers: one 32-bit int for the date, and another year, if I recall correctly.
So, no. It's probably faster because you're missing indexes, your query "abuses" the indexes, or your data set is small enough to favor the linear-time select (yours).
In terms of error-prone-ness and type-juggling, I highly disagree. An application is much more secure and robust with explicit typecasting. For instance, my query is less likely to break during MySQL updates because it doesn't rely on the server to auto-cast dates for me. In this particular case, your query is not backwards compatible. Some older versions of MySQL string-casted datetimes and timestamps slightly differently than it currently does.
If you have evidence that your query is faster, show us please. If you're query is indeed faster for some schema or type of operation, it would be good to go into detail so we can learn. If you're just mistaken, posting more detail will allow someone to explain how you can get better performance out of your application.
Either way, if you provide more details about your setup, someone should benefit here.
how do you select just the year from a datestamp?
I wanted to know how you search from a datestamp when you are only looking for a specific year?
i.e. 2008 I want to search my database for the all records in “date” containing 2008 that has the datestamp format YYYY-MM-DD?
Would this same format work for select count and fetch records?
Thanks in advance.
Sign in
to post a comment12 Comments(s) ↴
0
@svidgenApr 01.2009 — #This question probably should have been posted in the SQL section. In any case, if you're using MySQL, you would probably set your query up like this:[code=php]$query = "select * from table where date_field between date('2008-01-01') and date('2008-12-31')";
$result = mysql_query($query);
// ... etc.[/code]reply?
0
@phpnstuffauthorApr 01.2009 — #[CODE]
//connect to database
$dbconn = mysql_connect ( $dbhost, $dbuser, $dbpass );
mysql_select_db( $dbname, $dbconn );
//count records
$tte = mysql_query("SELECT COUNT(*) FROM _cpof_fallen_officers WHERE date between date('2008-01-01') and date('2008-12-31')", $dbconn);
$tte_count = mysql_result($tte, 0);
[/CODE]I'm getting Warning: mysql_result(): supplied argument is not a valid MySQL result resource in.
Is my normal select count code not work with date searches?
reply?
0
@svidgenApr 01.2009 — #Try dumping the result of [I]mysql_error()[/I]:[code=php]$tte = mysql_query("SELECT COUNT(*) FROM _cpof_fallen_officers WHERE date between date('2008-01-01') and date('2008-12-31')", $dbconn);
if ($tte) {
$tte_count = mysql_result($tte, 0);
} else {
print "<pre>mysql_error: " . mysql_error() . "</pre>n";
}[/code]reply?
0
@NogDogApr 01.2009 — #Assuming date<i>
</i>SELECT . . . WHERE YEAR(<span><code>date</code></span>) = 2008
reply?
0
@svidgenApr 01.2009 — #Of course, if the field isn't indexed, I suppose it doesn't really matter ...
reply?
0
@NogDogApr 01.2009 — #I'm under the impression that using the YEAR() function on a field will force MySQL to bypass using any index(es) on the date[time] field. I could be mistaken.
Of course, if the field isn't indexed, I suppose it doesn't really matter ...[/QUOTE]
A quick test with EXPLAIN suggests that you may be correct.
reply?
0
@NightShift58Apr 02.2009 — #[CODE]
$tte = mysql_query("SELECT COUNT(*) FROM _cpof_fallen_officers WHERE date(date) between '2008-01-01' and '2008-12-31' ", $dbconn);
$tte_count = mysql_result($tte, 0);
[/CODE]The DATE() function is used to extract the date part of a timestamp. As such, you don't need in BETWEEN...AND but you should use to extract the date part from the field
datereply?
0
@svidgenApr 02.2009 — #The DATE() function is used to extract the date part of a timestamp. As such, you don't need in BETWEEN...AND but you should use to extract the date part from the field.[/QUOTE] date
MySQL actually recommends casting the string literal. This makes sense over casting the field itself, since an operation on a field generally requires a table scan:
For best results when using BETWEEN with date or time values, you should use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE. (http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between )[/QUOTE]
When I EXPLAINed a set of queries, one which casted the columns and the other which casted the literal, the one which casted the column somehow ended up examining more rows than the table actually contained, whereas the other needed to examine only the matching/returned rows.
Bear in mind, in reference to my first post, if the field you're matching against is actually a datetime, the query should cast the date literal as a datetime including the full datetime range:
[CODE]SELECT COUNT(*) FROM _cpof_fallen_officers WHERE date between timestamp('2008-01-01 00-00-00') and timestamp('2008-12-31 23-59-59')[/CODE]Optimization is a little off topic though...
Did you get the [I]mysql_error()[/I], [B]phpnsuff[/B]?
reply?
0
@NightShift58Apr 02.2009 — #Yes, you could be correct. The main point I was trying to make, however, was that using date() to extract a date part from two literals which consisted of only date parts was useless.
In "real life" terms, however, if all I'm insterested in is the year, the fastest and easiest way to go would be:
[CODE]
$tte = mysql_query("SELECT COUNT(*) FROM _cpof_fallen_officers WHERE date LIKE '2008-%' ", $dbconn);
$tte_count = mysql_result($tte, 0);
[/CODE]reply?
0
@svidgenApr 02.2009 — #using date() to extract a date part from two literals which consisted of only date parts was useless.[/QUOTE]
Sure, casting with date() or datetime() may be unnecessary--at least my version of MySQL autocasts the literal correctly. But, casting the literals makes it the interpretation of the literals [B]explicit[/B] to MySQL from the start of the query [B]and[/B] to other programmers. And, in my humble opinion, at the cost of only a few extra characters, being explicit is [I]always[/I] a good thing.
In "real life" terms, however, if all I'm insterested in is the year, the fastest and easiest way to go would be:
[CODE]$tte = mysql_query("SELECT COUNT(*) FROM[/QUOTE]_cpof_fallen_officersWHEREdateLIKE '2008-%' ", $dbconn);
$tte_count = mysql_result($tte, 0);[/CODE]
That actually depends entirely on the format of the date field. If it's a text field, you are absolutely correct. However, my assumption is that the
datedateSo, even if your table only contains dates from 2008, you've [slightly] increased running time by using the pattern match. If your table even contains two full years worth of dates, you've more than doubled the running time of that query. And in either case, I'm quite confident that it introduces a relatively lengthy table-wide lock (blocking writes).
So, I think the small amount of effort required to [cast your literals and] use a range comparison is more than made up for by the drastic increase in performance.
reply?
0
@NightShift58Apr 05.2009 — #You'll see how much faster it is. Above all, it is less error-prone than all this casting and juggling the date.
And, as far as I know, internally, MySQL date fields are stored as text ('YYYY-MM-DD HH:MM:SS'). Perhaps that's why it's faster?
reply?
0
@svidgenApr 06.2009 — #Forget the theory... Just try it.[/QUOTE]
Running EXPLAIN is far from theory. It simulates the query, reports which indexes are used, and reports how many rows the server must examine in order to complete the query. Your version of the query examines more rows than are even in the table, regardless of what range I supply (undoubtedly creating "temporary" rows for the type conversions or something). My version examines [B]only[/B] the rows the query request.
If I request a record from a single date-time, only the row(s) from that date-time are touched. The same for your query, the entire table is scanned.
And, as far as I know, internally, MySQL date fields are stored as text ('YYYY-MM-DD HH:MM:SS'). Perhaps that's why it's faster? [/QUOTE]
Older versions of MySQL may store dates in pseudo-textual formats. I'm not entirely sure. However, if you're using the [I]timestamps[/I] type with any remotely recent version (which you most often should be) your dates will be stored as a 32 bit integer representing the number of seconds in UTC since the epoch. And if you're using a datetime instead, they'll be stored in local time as two 64 bit integers: one 32-bit int for the date, and another year, if I recall correctly.
So, no. It's probably faster because you're missing indexes, your query "abuses" the indexes, or your data set is small enough to favor the linear-time select (yours).
You'll see how much faster it is. Above all, it is less error-prone than all this casting and juggling the date.It's possible you're query is better, at least for the version of MySQL you're running it on. However, all the data I have suggests there's another reason your query would appear to be outperforming the more theoretically efficient one, especially since the theoretically more efficient one is also more efficient in practice on my installation and schema.
[/QUOTE]
In terms of error-prone-ness and type-juggling, I highly disagree. An application is much more secure and robust with explicit typecasting. For instance, my query is less likely to break during MySQL updates because it doesn't rely on the server to auto-cast dates for me. In this particular case, your query is not backwards compatible. Some older versions of MySQL string-casted datetimes and timestamps slightly differently than it currently does.
If you have evidence that your query is faster, show us please. If you're query is indeed faster for some schema or type of operation, it would be good to go into detail so we can learn. If you're just mistaken, posting more detail will allow someone to explain how you can get better performance out of your application.
Either way, if you provide more details about your setup, someone should benefit here.
reply?