MySql Query Takes A Long Time PHP

Question

I have the following PHP code in Laravel is executing a MySql query:

DB::statement(DB::raw("SET @rownum = 0"));
$sql = "@rownum := @rownum+1 AS rank, id, clanid, name, location, level, exp, warslost, warstied, warwinpercent, warswon, playercount, score";
$clan = Clans::orderBy('clanid', 'asc')
    ->selectRaw($sql)
    ->take(7000)
    ->get();

This query is taking a long time to execute.

I have indexes on the columns I am ordering by as well as many of the other ones for other queries.

What should I do?

Update:

Queries executed:

SET @rownum = 0

select @rownum := @rownum+1 AS rank, id, clanid, name, location, level, exp, warslost, warstied, warwinpercent, warswon, playercount, score from `clans` order by `clanid` asc limit 7000

EXPLAIN EXTENDED result:

Follow the instructions at http://stackoverflow.com/questions/14536165/get-the-query-executed-in-laravel-3-4 to produce the actual query that you are sending to the server. Paste it along with the output of EXPLAIN on it. — Sasha Pachev, Nov 16 '15 at 20:09
Does it make a difference if you remove the calculation of the row number? And your second query seems different form the first, the `ORDER BY` clause has changed. — jeroen, Nov 16 '15 at 20:22
@jeroen sorted that problem. It doesn't actually change the speed or anything though. — , Nov 16 '15 at 20:26
On mysql server as root run: ```strace -s 1024 -f -tt -p $(pidof mysqld) -o /tmp/trace.out``` , it will block then change the terminal window run your query from the command line client (rather than Laravel to reduce the noise), then return to the strace window and terminate it with Control-C. Make the contents of `/tmp/trace.out` available somewhere. Examine it first to make sure it does not have any private information you would not want the world to see and edit it out if necessary. — Sasha Pachev, Nov 16 '15 at 20:49
@SashaPachev I'm running the strace command but it is "running" is for a long time. How long should I wait until I can use the command line and use mysql on it? — , Nov 16 '15 at 21:02
You can probably terminate it now, we should have enough evidence it what it has collected so far. — Sasha Pachev, Nov 16 '15 at 21:05
@SashaPachev Hmmm... It actually executed in 4.49 seconds which is weird.... Posting the reuslts now. — , Nov 16 '15 at 21:06
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/95281/discussion-between-sasha-pachev-and-shivam-paw). — Sasha Pachev, Nov 16 '15 at 21:06

Sasha Pachev · Answer 1 · 2015-11-17T16:10:14.890

0

Shivam Paw and I worked on this via chat. It turned out that MySQL query in and of itself was fast after all. However, we determined that

print str_repeat('a', $size_of_mysql_result_set);

was very slow (the size was around 2.4M). So the problem was either in network I/O (possibly the hosting provider throttle), or some issue between PHP and Nginx in serving large amounts of output from PHP.

UPDATE:

Some options at this point:

Re-architect the app so it would not fetch 2+ MB at once. Maybe grab what you need for immediate display initially, then fetch the rest of it in the background - or better yet, only when the user actually needs it
Enable compression in Nginx (https://rtcamp.com/tutorials/nginx/enable-gzip/)
Negotiate a better bandwidth deal with your hosting provider or find a different provider

edited Nov 17 '15 at 16:10

answered Nov 16 '15 at 23:28

Sasha Pachev

5,162
3
20
20

I would say most if not all shared hosted providers has limited the server resources – Maytham Fahmi Nov 17 '15 at 06:09
I am on a VPS though.. @maytham-ɯɐɥıλɐɯ – Nov 17 '15 at 06:41
what can i do to fix it? – Nov 17 '15 at 06:42
From what it looks like you are dealing with too much data. I would do one of two things - think about why you need such a large result set and reduce the result set to what you actually require or update your query to handle the additional processing to return the actual result you intend to get from PHP. You are basically dealing with amount of data vs calculation. You're going to pay the price somewhere anytime you have this large of a result set you are working with. – Ryan Rentfro Nov 17 '15 at 14:12

score -1 · Answer 2 · answered Nov 17 '15 at 05:55

Is there any case to show 7000 rows at once? If you have large datas to display, basically paginate list when user request result.

cause there is no one can see 7000 datas at once ^^;; consider dynamic load data from database.

http://laravel.com/docs/5.1/pagination

MySql Query Takes A Long Time PHP

2 Answers2