Selecting first A and than B from the same table

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?

thank you

You could replace

- SELECT * FROM advertisers WHERE packageType=1 LIMIT $limitMin, $perPage
- SELECT COUNT(*) FROM advertisers WHERE packageType=1

with

SELECT SQL_CALC_FOUND_ROWS * FROM advertisers WHERE packageType=1 LIMIT $limitMin, $perPage;
SELECT FOUND_ROWS();

. That’s only on query, sort of anyway, to figure out the pagination details. However your approach would probably be the more efficient one.

Then you don’t the number of rows for which packageType=2, so you’d need one more query :slight_smile:

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”).

Put an index on packagetype?

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.

You would have to use more queries your way

  • 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.

And use integers for the pageType, ordinary=0, golden=1
Integer comparison is way faster than string comparison