How to filter out certain rows in MySQL dynamically to query against them?

Question

I have a PHP - MySQL set up . I have a table devicevalue structure of it is like this

devId | vals | date          | time
xysz  | 23   | 2020.02.17    | 22.06
abcs  | 44   | 2020.02.31    | 22.07

The vals columns hold temperature values . any user loggin in on my webapp have access to only certain devices.

Here are steps

1. On my website "a user" selects from and to dates for which he wants to see data & submit it
2. Then these dates are passed a page "getrecords.php " ,where there are lot select queries ( and many are in loop ) to fetch filtered data in required format

The problem is that this table holds almost 2-3 Million records . and in every where clause I have to add to and from conditions. this causes to search in entire table .

My question is there any way that I can get temporary table at step 1 which will have only certain rows based on given two dates and then all my queries on other page will be against that temporary table ?

Answer 1

Edit: If your date column is a text string, you must convert it to a column of type DATE or TIMESTAMP, or you will never get good performance from this table. A vast amount of optimization code is in the MySQL server to make handling of time/date data types efficient. If you store dates or times as strings, you defeat all that optimization code.

Then, put an index on your date column like this.

CREATE INDEX date_from_to ON devicevalue (`date`, devId, vals, `time` );

It's called a covering index because the entire query can be satisfied using it only.

Then, in your queries use

   WHERE date >= <<<fromdate>>> 
     AND date <  <<<todate>> + INTERVAL 1 DAY

Doing this indexing correctly gets rid of the need to create temp tables.

If your query has something like `WHERE devId = <<>> in it, you need this index instead (or in addition).

CREATE INDEX date_id_from_to ON devicevalue (devId, `date`, vals, `time` );

If you get a chance to change this table's layout, combine the date and time columns into a single column with TIMESTAMP data type. The WHERE clauses I showed you above will still work correctly if you do that. And everything will be just as fast.

SQL is made to solve your kind of problem simply and fast. With a good data choices and proper indexing, a few million records is a modestly-sized table.

Answer 2

Short answer: No. Don't design temp tables that need to live between sessions.

Longer answer:

Build into your app that the date range will be passed from one page to the next, then use those as initial values in the <form> <input type=text...>

Then make sure you have a good composite index for the likely queries. But, to do that, you must get a feel for what might be requested. You will probably need a small number of multi-column indexes.

You can probably build a SELECT from the form entries. I rarely need to use more than one query, but it is mostly "constructed" on the fly based on the form.

It is rarely a good idea to have separate columns for date and time. It makes it very difficult, for example, to say noon one day to noon the next day. Combine into a DATETIME or TIMESTAMP.

O.Jones has said a lot of things that I would normally add here.