sql query too slow

maybe agolia can help...

Usually when a query runs slow try adding an index on the column. If you search for a date it will look in the whole table (full table scan) if you add an index on the used column then it will do an indexed scan which is much faster.

https://laravel.com/docs/5.8/migrations#indexes

You need to index the columns you're going to be querying. If you're using MySQL, I suggest taking some time and learning about "mysql explain" to help identify bottle necks and other things like index order. Another issue that can creep up with larger database is the need to adjust some parameters in the server itself. There's some scripts out there like Tuning Primer that can help identify adjustments to make. As a rule of thumb I keep everything tuned to rather conservative numbers on my dev machine, then get more liberal in production.

Try not to use active record on search with that many records, rather use getPdo().

Index any column you expect to search.

Do not use star if you don't need all fields, use only the fields needed.:

SELECT * from ...   ///   Don't do this

// instead

SELECT `id`, `mydate', (and only fields needed) from ...

In a search text box use more characters, example you are finding someone with the last name of White.

Don't enter just a w.

Enter at least or 4 characters like whit.

If a search can be done via a numeric index, use it instead when possible.

I.e., You need to pull up Joe White in the database. However if he has a customer number, use it.

Numeric is always more efficient.

GetPdo() example: https://laracasts.com/discuss/channels/guides/getpdo-usage

Bind your parameters.

And:

https://laracasts.com/discuss/channels/guides/length-aware-paginator

Also there are special pagination techniques for databases with millions of records, basically you use greater than a certain index and less than. LIMIT in mysql get less efficient as a database grows.

Step one should be getting away from active record (eloquent) and use pdo direct (getPdo()) the pdo instance in laravel.

In fact I used to export data monthly, had an odbc setup on local and did monthly reports in MS Access, much more efficient.

You are at that phase where you are seeing there's a big difference in a small few hundred records verses a huge data set.

Active record (eloquent) is fine provided you don't try and return too much data into memory, so always use take() or paginate()

The mySQL server will do all the work for you.

However, the answer to your question is to use indexing correctly.

In your migration table use index() for following tables date,created_at and id,and you can create index for mysql database tables as manually,

      $table->integer('id')->index();

      $table->integer('created_at')->index();

      $table->integer('date')->index();

Try not to use active record on search with that many records, rather use getPdo().

There is seriously no need to reach for a PDO instance directly in the majority of cases. If you structure you database correctly with the right indexes and also make your queries efficient then Eloquent will do just fine.