How many ways we can we find the current date using MySQL?

If you ever need to use the current date and time in one of your query, then there is no need to do it in PHP. Just use one of the following functions:

CURDATE() returns the current date as a string of the format YYYY-MM-DD;
CURTIME() returns the current time as a string of the format HH:MM:SS;
NOW() or SYSTIME() returns the current date and time in the format YYYY-MM-DD HH:MM:SS;
Note: NOW() and SYSTIME() does not behave the same way.

NOW() returns the timestamp from the moment, when the SQL query began to be carried out, so that will return one and the same constant during the whole of the request, even if it takes an hour to perform.

SYSTIME(), on the other hand, will always return the current date and time based on the system time (time the system timer server, the internal clock of the computer).

How can we optimize or increase the speed of a mysql select query?

SELECT
Queries, who are more susceptible to optimization, are requests on the sample.
In order to see how it will make a request to the sample, you use the EXPLAIN: With its help we can see, in what order will contact tables and which indexes it will be used.
The main mistake beginners is the lack of indexes on the required fields or create add-on unnecessary fields. If you do a simple sample like:
SELECT * FROM table WHERE field1 = 123
Then you need to insert an index on the field field1, if you're using the sample condition on two fields:
SELECT * FROM table WHERE field1 = 123 & field2 = 234
Then you need to create a composite index on field1, field2.

If we use connect 2 or more tables:
SELECT *
FROM a, b
WHERE a.b_id = b.id

Or in a more General form:
SELECT *
FROM a
[LEFT] JOIN b ON b.id = a.b_id
[LEFT] JOIN the ON с.id = b.c_id
Then you should create indexes on fields, which will join the table. In this case, this field b.id and c.id. However, this statement is true only in the case if the sampling will take place in the order in which they are listed in the request. If, for example, the MySQL optimizer will select records from the tables in the following order: c,b,a, you will need to insert indexes on fields: b.c_id and a.b_id. When linking with a LEFT JOIN table, which is in the request to the left, will always be the first.Sometimes it is such a the situation that we have to make a sample of one and the same part of a very large table, for example, in many requests there is a connection with part of the table:

[LEFT] JOIN b ON b.id = a.b_id AND b.field1 = 123 AND b.field2 = 234.In such cases, it may be reasonable to make this part in a separate temporary table:
CREATE TEMPORARY TABLE tmp_b TYPE=HEAP SELECT * FROM b WHERE b.field1 = 123 AND b.field2 = 234
And the work already with her (about temporary tables you can read in documentationAlso, if several times we count aggregate function to the same data, then for acceleration should make this calculation separately and put the result in a temporary table.