Databases
StackOverflow2013
Postsdbo
POCan this MySQL Query be optimized?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan this MySQL Query be optimized?
    primarykey
    data
    text
    <p>I currently try to optimize a MySQL query which runs a little slow on tables with 10,000+ rows.</p> <pre><code>CREATE TABLE IF NOT EXISTS `person` ( `_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `_oid` char(8) NOT NULL, `firstname` varchar(255) NOT NULL, `lastname` varchar(255) NOT NULL, PRIMARY KEY (`_id`), KEY `_oid` (`_oid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `person_cars` ( `_id` int(11) NOT NULL AUTO_INCREMENT, `_oid` char(8) NOT NULL, `idx` varchar(255) NOT NULL, `val` blob NOT NULL, PRIMARY KEY (`_id`), KEY `_oid` (`_oid`), KEY `idx` (`idx`), KEY `val` (`val`(64)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; # Insert some 10000+ rows… INSERT INTO `person` (`_oid`,`firstname`,`lastname`) VALUES ('1', 'John', 'Doe'), ('2', 'Jack', 'Black'), ('3', 'Jim', 'Kirk'), ('4', 'Forrest', 'Gump'); INSERT INTO `person_cars` (`_oid`,`idx`,`val`) VALUES ('1', '0', 'BMW'), ('1', '1', 'PORSCHE'), ('2', '0', 'BMW'), ('3', '1', 'MERCEDES'), ('3', '0', 'TOYOTA'), ('3', '1', 'NISSAN'), ('4', '0', 'OLDMOBILE'); SELECT `_person`.`_oid`, `_person`.`firstname`, `_person`.`lastname`, `_person_cars`.`cars[0]`, `_person_cars`.`cars[1]` FROM `person` `_person` LEFT JOIN ( SELECT `_person`.`_oid`, IFNULL(GROUP_CONCAT(IF(`_person_cars`.`idx`=0, `_person_cars`.`val`, NULL)), NULL) AS `cars[0]`, IFNULL(GROUP_CONCAT(IF(`_person_cars`.`idx`=1, `_person_cars`.`val`, NULL)), NULL) AS `cars[1]` FROM `person` `_person` JOIN `person_cars` `_person_cars` ON `_person`.`_oid` = `_person_cars`.`_oid` GROUP BY `_person`.`_oid` ) `_person_cars` ON `_person_cars`.`_oid` = `_person`.`_oid` WHERE `cars[0]` = 'BMW' OR `cars[1]` = 'BMW'; </code></pre> <p>The above SELECT query takes ~170ms on my virtual machine running MySQL 5.1.53. with approx. 10,000 rows in each of the two tables.</p> <p>When I EXPLAIN the above query, results differ depending on how many rows are in each table:</p> <pre><code>+----+-------------+--------------+-------+---------------+------+---------+------+------+---------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+---------------+------+---------+------+------+---------------------------------------------+ | 1 | PRIMARY | &lt;derived2&gt; | ALL | NULL | NULL | NULL | NULL | 4 | Using where | | 1 | PRIMARY | _person | ALL | _oid | NULL | NULL | NULL | 4 | Using where; Using join buffer | | 2 | DERIVED | _person_cars | ALL | _oid | NULL | NULL | NULL | 7 | Using temporary; Using filesort | | 2 | DERIVED | _person | index | _oid | _oid | 24 | NULL | 4 | Using where; Using index; Using join buffer | +----+-------------+--------------+-------+---------------+------+---------+------+------+---------------------------------------------+ </code></pre> <p>Some 10,000 rows give the following result:</p> <pre><code>+----+-------------+--------------+------+---------------+------+---------+------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+------+---------+------------------------+------+---------------------------------+ | 1 | PRIMARY | &lt;derived2&gt; | ALL | NULL | NULL | NULL | NULL | 6613 | Using where | | 1 | PRIMARY | _person | ref | _oid | _oid | 24 | _person_cars._oid | 10 | | | 2 | DERIVED | _person_cars | ALL | _oid | NULL | NULL | NULL | 9913 | Using temporary; Using filesort | | 2 | DERIVED | _person | ref | _oid | _oid | 24 | test._person_cars._oid | 10 | Using index | +----+-------------+--------------+------+---------------+------+---------+------------------------+------+---------------------------------+ </code></pre> <p>Things get worse when I leave out the WHERE clause or when I LEFT JOIN another table similar to <code>person_cars</code>. </p> <p>Does anyone have an idea how to optimize the SELECT query to make things a little faster?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USxlttj
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COTwo quick questions, why are you using a blob data type for storing the make of a car? And also have you considered using InnoDb as your database engine over MyISAM?
      singulars
      1. POCan this MySQL Query be optimized?
      1. USGordyD
    2. COI use blob because I will have to store data of arbitrary lenth in that column. Cars were just an example. And, no, I haven't tried InnoDb yet, because that project doesn't use InnoDb at all. I'll give it a shot, thanks :)
      singulars
      1. POCan this MySQL Query be optimized?
      1. USxlttj
    3. COAlso remember that having BLOB or TEXT fields makes all temporary tables (during joins and sorting) to be actually on-disk tables.
      singulars
      1. POCan this MySQL Query be optimized?
      1. USMarki555
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload