<cut>
Steve - thanks for positive feedback...
>
> We push MySQL to handle a lot of queries per second. Any query that last
> several seconds can be enough to cause a cascade under a real heavy load. We
> have three things that are slow:
>
> 1) select ... order by xyz desc
>
> Workaround: create an extra column called xyz_desc that is set to
> 0x7FFFFFFF - xyz and index that.
We do have a patch to fix it in 3.23, and it is already in 4.0 tree. Tim is
the one who made it, so he can provide more details.
>
> 2) select bigblob where optimized_index_clause order by
> optimized_index_clause limit 6650,50
>
> Problem: I used to think that since the start number is high in the limit,
> that it was slow because the data was not often used and therefore not
> cached (the second try at the query is fast). I think that is part right --
> I think the problem is that MySQL starts using the data file (with the big
> blob) before it needs to. Ideally (and maybe MySQL does this) it should use
> the index file only until the where _and_ limit clauses are satisfied, then
> go get data from the data file. My gut tells be that MySQL is causing lots
> of the blob data to be read from disk even though it is not needed. I have
> the EXPLAIN below.
I think you are right. The first workaround that comes to my head is to
select all the matching values of the primary key into temp table, and then
just perform a join. Monty may have some additional comments.
>
> 3) Someone is doing an ad-hoc query as if it were a data-warehouse.
> Workaround: Replicating to a different server that had a different index
> structure for the tables (or blocking access to those doing such foolish
> things).
In addition, you can put a cap on max_join_size. This will make mysqld refuse
to run queries that it thinks will require examining too many rows.
>
> 4) Repair table.
What is wrong with REPAIR TABLE?
<cut>
>
>
> explain select cache,rootmessageid from forums_threads_6 use
> index(thread_desc_order_reader) where forumid=48806 and
> thread_desc_order_reader > 0 order by forumid, thread_desc_order_reader
> limit 6650,50;
> +------------------+-------+------------------------------------------------
> -------------------------------------------------------------+--------------
> ------------+---------+------+------+------------+
> | table | type | possible_keys
> | key | key_len | ref | rows | Extra |
> +------------------+-------+------------------------------------------------
> -------------------------------------------------------------+--------------
> ------------+---------+------+------+------------+
> | forums_threads_6 | range |
> PRIMARY,thread_desc_order_reader,thread_desc_order_moderator,lastpost_order_
> reader,lastpost_order_moderator | thread_desc_order_reader | 8 | NULL
> | 6590 | where used |
> +------------------+-------+------------------------------------------------
> -------------------------------------------------------------+--------------
> ------------+---------+------+------+------------+
> 1 row in set (0.00 sec)
>
>
> show index from forums_threads_6;
> +------------------+------------+-----------------------------+-------------
> -+-----------------------------+-----------+-------------+----------+-------
> -+---------+
> | Table | Non_unique | Key_name | Seq_in_index
> | Column_name | Collation | Cardinality | Sub_part | Packed
> | Comment |
> +------------------+------------+-----------------------------+-------------
> -+-----------------------------+-----------+-------------+----------+-------
> -+---------+
> | forums_threads_6 | 0 | PRIMARY | 1
> | forumid | A | NULL | NULL | NULL
> | |
> | forums_threads_6 | 0 | PRIMARY | 2
> | rootmessageid | A | 605269 | NULL | NULL
> | |
> | forums_threads_6 | 1 | thread_desc_order_reader | 1
> | forumid | A | 8772 | NULL | NULL
> | |
> | forums_threads_6 | 1 | thread_desc_order_reader | 2
> | thread_desc_order_reader | A | 605269 | NULL | NULL
> | |
> | forums_threads_6 | 1 | thread_desc_order_moderator | 1
> | forumid | A | 8772 | NULL | NULL
> | |
> | forums_threads_6 | 1 | thread_desc_order_moderator | 2
> | thread_desc_order_moderator | A | 605269 | NULL | NULL
> | |
> | forums_threads_6 | 1 | lastpost_order_reader | 1
> | forumid | A | 8772 | NULL | NULL
> | |
> | forums_threads_6 | 1 | lastpost_order_reader | 2
> | lastpost_order_reader | A | 605269 | NULL | NULL
> | |
> | forums_threads_6 | 1 | lastpost_order_moderator | 1
> | forumid | A | 8772 | NULL | NULL
> | |
> | forums_threads_6 | 1 | lastpost_order_moderator | 2
> | lastpost_order_moderator | A | 605269 | NULL | NULL
> | |
> +------------------+------------+-----------------------------+-------------
> -+-----------------------------+-----------+-------------+----------+-------
> -+---------+
>
>
--
MySQL Development Team
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA
<___/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
