Geek Answers Handbook

Problems with a large MySQL table when working with PHP

Have a huge mysql table with 300,000 entries and want to host the entries in PHP (not here) with the query as follows:

SELECT * FROM `table` LIMIT 250000, 100

In the last part of the records, this can be significantly slower, especially at the end of the table (LIMIT start is very large). I assume MySQL should count everything up to 250,000 before striking out the results to me?

So, how to get around this or any other swap approach, which can be much faster? Thanks!

+6
performance database mysql
source share
4 answers

Make sure you use the index, otherwise it performs a full table scan. You can look at the execution plan to check this out or to get the problem with the ORDER BY (in the indexed column). Here is additional information .

Your table is not so large in 300k rows. However, there are performance problems when approaching the table. The only real solution to this is to fake a limit sentence. Have an auto-increment field, which is listed in lines from 1 to 300,000, and then do:

 SELECT * FROM mytable WHERE field BETWEEN 250000 and 250100

or similar. This may be problematic or impossible if you delete rows often, but I tend to find that older data tends to change less, so you can optimize it a bit by using LIMIT for the first 100,000 rows and a surrogate search call column beyond .

+4
source share

You are right: MySQL must scan 250,000 worthless strings before reading the ones you want. In fact, there is no workaround for this save, splitting the table into several or having hacks, such as:

 SELECT * FROM table WHERE id BETWEEN 250000 AND 250000 + 100 - 1 ;or SELECT * FROM table WHERE id > 250000 ORDER BY id ASC LIMIT 100

But this still does not accurately emulate the function of the LIMIT operator on complex queries. This is speed: functionality.

+1
source share

Is this really the whole query, or do you also have an ORDER BY clause? Because it will greatly slow down requests. If you can get an index on the whole set of things you order, nonetheless.

0
source share

If you have an auto-increment field, you can simply do:

 SELECT * FROM table WHERE ID > @LastID ORDER BY ID LIMIT 100
0
source share

More articles:


All Articles