John is here in the channel and we're looking into integrating everything together
since this is involves dealing with large data set, searching and rendering. can i ask a question related to mysql data and querying for reports here?
or wrong place?
no guarantees though
i have a table of about 1million records
each record has the date/time it was inserted and who inserted it
and when it was last updated and who updated it
and, who created might be different from the last person that updated
table has about 30 columns and is currently 716.7Mib
problem now is querying it for information based on the created time, who created, last updated time, who updated last runs into 25seconds
or even more
following? making any sense?
I assume you added the necessary indexes?
let me show you a sample query to get the full picture
but here we go
SELECT
COUNT(CASE WHEN packages.location_id IN (1) AND packages.date_generated >= 1436181080 AND packages.date_generated <= 1438773080 THEN 1 END) AS `ingested`,
COUNT(CASE WHEN packages.source_id IN (1) AND IF(packages.arrival_time IS NULL, 0, packages.arrival_time) >= 1436181080 AND IF(packages.arrival_time IS NULL, 0, packages.arrival_time) <= 1438773080 THEN 1 END) AS `received`,
COUNT(CASE WHEN packages.source_id IN (1) AND packages.last_update_time >= 1436181080 AND packages.last_update_time <= 1438773080 AND currentStatus.type = 'delivery' THEN 1 END) AS `delivered`,
COUNT(CASE WHEN packages.source_id IN (1) AND packages.last_update_time >= 1436181080 AND packages.last_update_time <= 1438773080 AND currentStatus.type = 'return' THEN 1 END) AS `returned`,
SUM(IF(packages.location_id IN (1) AND packages.date_generated >= 1436181080 AND packages.date_generated <= 1438773080 AND packages.ingestion_fee IS NOT NULL, packages.ingestion_fee, 0)) AS `ingestion_earnings`,
SUM(IF(packages.source_id IN (1) AND packages.last_update_time >= 1436181080 AND packages.last_update_time <= 1438773080 AND packages.freight_charge IS NOT NULL, packages.freight_charge, 0)) AS `processed_earnings`
FROM `packages`
LEFT JOIN `package_statuses` `currentStatus` ON `packages`.`package_status_id` = `currentStatus`.`id`
really, it’s a logistics systems. we track packages
location_id is where the waybill/package was generated
source_id is where it is currently
arrival_time is when it got to where it is
of course, we know
last_update_time and date_generated
nope, i cut out some of the aggregations for simplicity
but that should give a picture
yes. scans the entire table apparently
hmm
sums, and counts
only selected those 6 for simplicity
you should probably run that stuff with an explain on and see which indexes get used and which dont
yes. exactly. @entire table