When adding the line:
LEFT JOIN core_records_sales as sales ON DATE(appointments.date) = DATE(sales.date_sold)
To my query, it boosts the time for the script to run from about 8 seconds, to 2-3 minutes.
Would there be some data causing this problem or am I not implementing the function correctly?
I need to use DATE() because I need them to be the same day but the date fields are DATETIME
This is almost certainly an issue with the appointments.date field being indexed. Adding the use of the DATE() function makes it so that index cannot be used, forcing a table scan.
I ve had to deal with this before, and the best way I ve found to solve the issue is to have a separate column with just the date part (no time) in it or to store the date and time in two separate columns.
I d love to hear if others have a better way of dealing with that though.
Do you have an index on the date column? Joining without an index requires a full table scan, so that s likely the cause of the performance issue.
Run your query through an EXPLAIN to see what the query optimizer comes up with for the query plan.
It s probably because you re forcing a full table scan on both tables whereas previously you were using an index as part of the query. You need to examine the plan (and provide us with a little more detail too) so run it through EXPLAIN to see what s happening.
I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...
<?php $con=mysql_connect("localhost","mts","mts"); if(!con) { die( unable to connect . mysql_error()); } mysql_select_db("mts",$con); /* date_default_timezone_set ("Asia/Calcutta"); $date = ...
我把我的用心从使用QQL转向MySQL。 它与凯科特合作,现在不工作,因为我已经改变,使用MySQL。 这里的错误信息是:
We have a restaurant table that has lat-long data for each row. We need to write a query that performs a search to find all restaurants within the provided radius e.g. 1 mile, 5 miles etc. We have ...
Is it possible in php to return a specific row of data from a mysql query? None of the fetch statements that I ve found return a 2 dimensional array to access specific rows. I want to be able to ...
Our website was developed with a meta tag set to... <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> This works fine for M-dashes and special quotes, etc. However, I ...
What are some of the strategies being used for pagination of data sets that involve complex queries? count(*) takes ~1.5 sec so we don t want to hit the DB for every page view. Currently there are ~...
My table looks like person_id | car_id | miles ------------------------------ 1 | 1 | 100 1 | 2 | 200 2 | 3 | 1000 2 | 4 | 500 I need to ...