I have a table advertisers.
id | companyId | packageType | text | adImage
I need to display them in search results where advertisers which have packageType=golden are shown before than packageType=ordinary.
One of solutions is SELECT * FROM advertisers ORDER BY packageType LIMIT $limitMin, $perPage, but the problem is that ORDER BY is very slow. What other options do I have?
Then you don’t the number of rows for which packageType=2, so you’d need one more query
I’m not sure, SQL_COUNT_ROWS is also really fast.
I’m hoping the OP will come back to this thread and post his findings, it would be interesting to see which of the suggested approaches is the fastest (although I think it will be a “battle for microseconds”).
Thank you. But still I think that can be a very wasting solution because I need to display only 10 results per page and user can never go deeper than 1000 results. By using order by it need to go through all 100.000 rows.
I was thinking to make two queries. 1st WHERE packageType=1 and 2nd WHERE packageType=2. Than I would calculate with php mysql LIMIT for pagination purposes.
SELECT * FROM advertisers WHERE packageType=1 LIMIT $limitMin, $perPage
SELECT COUNT(*) FROM advertisers WHERE packageType=1
SELECT COUNT(*) FROM advertisers WHERE packageType=2
You need the last two to be able to figure out the details of pagination
Why don’t you add the index, convert the column to INT and see how it works with ORDER BY and LIMIT?
I created a website once that currently has some 180.000 rows in one table, and also uses an ORDER BY on an INT value and a LIMIT, and it’s still fast.