How select query retrieves data internally?

Question

I have a mysql memory table with contains priorities assign to each row.I am creating this table every 2 minutes and this table is being called almost 100 times/second.

It contains around 800 rows.

Query I am running is like:

select * from table order by priority limit 0,20

I want to optimize this query.Can I put the data in the sorted order while creating the table and can assume data pulled will always be sorted based on priority if I remove the order by clause.

Thanks

What do you use to work with database? Php? – Denis Ermolin Oct 24 '12 at 07:36 — Denis Ermolin, Oct 24 '12 at 07:36
yes..I use php to connect to mysql.. – shashuec Oct 24 '12 at 07:37 — shashuec, Oct 24 '12 at 07:37

Denis Ermolin · Answer 1 · 2012-10-24T08:03:47.373

0

I have 2 suggestions:

If you create this table and do NOT modify after creation it then after creating and filling do this ALTER TABLE table ORDER BY priority. This will sort your table and you can give up your ordering.
Don't use mysql to order your results. Use php for it.

Also dont forget about indexes. If you didn't turn off mysql result caching then all your selects are cached and don't calculates results. So you have to check your config for it. Read more here

edited Oct 24 '12 at 08:03

answered Oct 24 '12 at 07:39

Denis Ermolin

5,530
6
27
44

I guess.we cant reply on insertion order in select query.please check this : http://stackoverflow.com/questions/1949641/mysql-row-order-for-select-from-table-name – shashuec Oct 24 '12 at 08:19
Yes, my suggestion is to alter table after insertion, and assuming that you will not modify it after alter. – Denis Ermolin Oct 24 '12 at 08:21
problem is this doesn't work if I have where conditions.And my data is index on other columns as well.. – shashuec Oct 24 '12 at 10:08
Then query cache to your help or use memcached for caching. – Denis Ermolin Oct 24 '12 at 10:45

score 0 · Answer 2 · answered Oct 24 '12 at 07:47

There are 3 things you can do database-side & 1 on the actual HTML/PHP side to optimise this query:

Use an ISAM table with 'priority' indexed @ http://dev.mysql.com/doc/refman/5.0/en/create-index.html & also make it as small as possible e.g. 'priority' SMALLINT(3) UNSIGNED ZEROFILL . The last point, limiting, you've already got.
Use an opcode cacher to cache the results and deliver them directly to people's browsers if the information is only changing every-so-often, without requerying the database e.g. http://en.wikipedia.org/wiki/List_of_PHP_accelerators

How select query retrieves data internally?

2 Answers2