PHP Accelerating Queries on MySql

Question

I am using the Laravel PHP Framework.

Say I have several queries like this:

public function order($orderby){ \DB::connection()->disableQueryLog(); if($orderby == "level"){ $clan = Clans::orderBy('level', 'DESC') ->orderBy('exp', 'DESC') ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']); }elseif($orderby == "score"){ $clan = Clans::orderBy('score', 'DESC') ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']); }elseif($orderby == "warwinpercent"){ $clan = Clans::orderBy('warwinpercent', 'DESC') ->where('warswon', '>=', '100') ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']); }else $clan = Clans::paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']); \DB::connection()->enableQueryLog(); return view('clans.index')->with('clan', $clan); } 

It takes about 10-15 seconds.

I have one such:

 public function index(){ $clan = Clans::orderBy('clanid', 'ASC') ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']); return view('clans.index')->with('clan', $clan); } 

Which loads almost instantly.

Why do the first 3 take a lot longer than the last? I added an index for all the variables that I need to search in my table, but it still requires age. Do I need to do anything on the MySql side to rebuild the index? I have already tried optimizing the table, and I restart the MySql service again.

If it's not possible to speed them up, is there an easy way to show the loading animation to the user when the page loads?

Thanks!

UPDATE

Here is the result of an explanation for a quick query:

 explain extended select `id`, `clanid`, `name`, `level`, `exp`, `warwinpercent`, `warswon`, `playercount`, `score` from `clans` order by `clanid` asc limit 100 offset 0 

Here is the result of the explanation for the orderBy level query.

 explain extended select `id`, `clanid`, `name`, `level`, `exp`, `warwinpercent`, `warswon`, `playercount`, `score` from `clans` order by `level` desc, `exp` desc limit 100 offset 0 

Here is the result of SHOW INDEX FROM clans .

UPDATE 2

I also have this code that searches for a row in the name column

 public function clans(){ if((isset($_GET['search'])) && $_GET['search'] != ""){ $search = $_GET['search']; $result = Clans::where('name', 'LIKE', '%'.$search.'%')->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']); $data = array( 'result' => $result, 'search' => $search ); return view('search.clans')->with($data); }else return view('errors.1')->with('message', 'You entered an invalid search term'); } 

This may take a few seconds.

Explain the request:

 explain extended select `id`, `clanid`, `name`, `level`, `exp`, `warwinpercent`, `warswon`, `playercount`, `score` from `clans` where `name` LIKE '%lol%' limit 100 offset 0 

Answer 1

Problem with query 2 (query 1 looks great). But with two, although you have an index at the level, the power is so high that mysql refuses it. Number of lines 170K, power 159k

On the manual page entitled How MySQL Uses Indexes :

Indexes are less important for queries on small tables or large tables where report queries process most or all of the rows. When a query requires access to most rows, reading sequentially is faster than working through an index. Sequential reads minimize search queries, even if not all rows are needed for the query. See Section 8.2.1.16, “Avoiding Full Table Scanning” for more information.

Composite index

A component ( multi-column ) index is an index on 2 or more columns in the form of combos. May be unique or not.

 CREATE UNIQUE INDEX `thing_my_composite` ON thing (position,email,dob); -- forbid dupes hereafter 

Coverage index

A coverage index is one in which a query can be resolved by a quick walk through the b-tree index, without requiring a search on the actual data table. This is the ultimate nirvana for speed. Percona quick article.

Full-text search queries (based on OP comments)

On this page of the manual for Full-Text Search Features , here is an excerpt:

MySQL supports full-text indexing and search:

The full-text index in MySQL is an index of type FULLTEXT.

Full-text indexes can only be used with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can only be created for CHAR, VARCHAR, or TEXT columns.

The FULLTEXT index definition can be specified in the CREATE TABLE statement when a table is created or added later using ALTER TABLE or CREATE INDEX.

For large data sets, it is much faster to load data into a table that does not have a FULLTEXT index and then create an index after that than to load data into a table that has an existing FULLTEXT index.